Excel Vba 2007: use reference inside countifs

Question

Hi there,

I have this:

ActiveCell.Formula = "=COUNTIFS(G$4:G$" & (i - 1) & _
                     ", Sheets(ActiveSheet.Name).Cells(3, 25).Value ,F$4:F$" & (i - 1) & _
                     ",J4)"

I want to use this: Sheets(ActiveSheet.Name).Cells(3, 25)

so that I can make in loop to scan through column. as this reference cell must be fix. but I had error, it doesnt work.

Anyone help me please. Thanks o lot

Answer 1

What stops you from using the actual address, $Y$3?

---

EDIT

Ah. You said you want this value fixed so I thought...

Then, you don't you just do the exactly same thing you're already doing in the rest parts of your formula?

ActiveCell.Formula = "=COUNTIFS(G$4:G$" & (i - 1) & _ 
                     ", " & _
                     ActiveSheet.Cells(3, 25).Address(True, True, xlA1) & _
                     ", F$4:F$" & (i - 1) & _ 
                     ", J4)" 

Answer 2

You can use =CHAR() and =CODE() for the letters. "A" can be represented by =CHAR(65), so any letter is 64 + (letter of the alphabet), so Y =CHAR(89).

If you want to go one column over from "Y", you can go CHAR(CODE("Y")+1) = "Z".

Also, CHAR(cells.column) will give you the column letter (assuming you are using the whole sheet).

Hope that makes sense. :)