Activecell.formula variable column number issue

[gruntingmonkey]

I have code that places a formula into a cell. What I am looking to do is have variable column numbers for E2 & AF2 as these columns will change.

ActiveCell.Formula = "=COUNTIFS(E2,"">=""&Utilities!$F$12,AF2,"">""&Utilities!$H$12)"

I have the code to work out the column numbers but cant work out how to integrate that into the formula

Cells.Find(What:="Test Search", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
TestSearchCol = ActiveCell.Column

 

[Fluff]

I just don't know how to integrate it into the activecell.formula.

And nor do I if you don't answer my question.
It would also help if you posted all the code.

 

[gruntingmonkey]

Lets try this again! Its the last two lines that are the important bits!

''Match First Text to derive first column
    Sheets("Pivot Data").Select
Cells.Find(What:="Test 1", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
Test1Column = ActiveCell.Column

''Match Second Text to derive second column
Cells.Find(What:="Test 2", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
Test2Column = ActiveCell.Column

'''This is the cell that I need the formula to be put into
Range("A2").Select

'''This is the formula that needs to go into the cell - HOWEVER - E2 should be linked in to Test1Column, row 2 & AF2 should be linked in to Test2Column, row 2
    ActiveCell.Formula = "=COUNTIFS(E2,"">=""&Utilities!$F$12,AF2,"">""&Utilities!$H$12)"

Please let me know if this is any clearer.. or not!

 

[Fluff]

Thanks for that, how about

ActiveCell.FormulaR1C1 = "=and(rc" & test1column & ">=Utilities!r12c6,rc" & test2column & ">Utilities!r12c8)+0"

 

[gruntingmonkey]

I'm very sorry for putting that confusingly. Your answer worked a treat.

 

[Fluff]

Glad to help & thanks for the feedback.