please help me toreduce my codes
Hi Experts,
i have only basic knowledge in VBA
please find below codes i have done.
Range("d23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("d2"))
Range("e23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("e2"))
Range("f23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("f2"))
Range("g23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("g2"))
Range("h23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("h2"))
Range("i23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("i2"))
Range("j23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("j2"))
Range("k23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("k2"))
Range("l23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("l2"))
Range("m23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("m2"))
Range("n23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("n2"))
Range("o23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("o2"))
Range("p23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("p2"))
Range("q23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("q2"))
Range("r23") =
Application.WorksheetFunction.CountIfs(Sheet3.Range("xfd:xfd"),
Sheet3.Range("f12"), Sheet2.Range("e:e"), Sheet1.Range("r2"))
Range("s23") = Application.WorksheetFunction.Sum(Range("d23:r23"))
like this i have thousands of lines.
please anyone help me to reduce this codes with advanced scrips.
i am getting an error message Procedure too long.
Shorter code...
Range("D23:R23").Formula = "=CountIfs(Sheet3!$XFD:$XFD, $F$12, Sheet2!$E:$E, Sheet1!D$2)"
Range("D23:R23").Value = Range("D23:R23").Value
a small editing is needed
The code is not working because
For the cell d23 it is ok
But for the next cell e23 the code should be
Range("D23:R23").Formula = "=CountIfs(Sheet3!$XFD:$XFD, $F$12, Sheet2!$E:$E, Sheet1!e$2)"
Here at the end of the code Sheet1!D$2 should be changed to Sheet1!e$2
And it will change accordingly in the rest of the cells
It will increment automatically...
As there is no dollar sign before the 'D' of the last portion it will automatically become E,F,G etc as it is entered by the VBA into the cells.
If you run the code without the .value = .value line you can look at the formula in the cells and will see that the last portion of the formula will have incremented.