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

 

[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([B]E[/B]2,"">=""&Utilities!$F$12,[B]AF[/B]2,"">""&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

Note the code is actually:

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

I mistakenly bolded it... and it has added [ B ] in.

 

[Fluff]

What columns should it look at?

 

[gruntingmonkey]

What columns should it look at?

Currently E (column 5) and AF (column 32) but these are not set and they move around.

 

[Fluff]

How do you determine which columns each time the code is run?

 

[gruntingmonkey]

How do you determine which columns each time the code is run?

Using the below code

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

 

[Fluff]

That's only one column, is it for E2 or AF2 & what is the other meant to be?

 

[gruntingmonkey]

That's only one column, is it for E2 or AF2 & what is the other meant to be?

That's only one column, is it for E2 or AF2 & what is the other meant to be?

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

 

[Fluff]

You have now quoted post#7 twice & your own post, but given no answer.
Please do not quote posts like that it's totally pointless. Just use the "Quick reply window" below the thread.

 

[gruntingmonkey]

Ive just added a 1 on to the end - its not the same. Call one a and one b. a is searching for test and b is searching for test1. I know how to get the columns, I just don't know how to integrate it into the activecell.formula.