Syntax of formula with variable within macro.

Hi Guys,

I have a macro that is inserting a rows then populating them with data.

In the macro a variable (rw) has been set with the row number of the newly inserted row.

What I would like to do:-

Get the existing macro to insert a formula to count how many cells in row rw from column F to DD are populated.


So, when the macro is played this time it would read:-

.Cells(rw, 4).Value = COUNTIF(F108:DD108,">0").Value

And next time

.Cells(rw, 4).Value = COUNTIF(F109:DD109,">0").Value

So, what I am after is the correct syntax for:-

.Cells(rw, 4).Value = COUNTIF(Frw:DDrw,">0").Value

Any help would be greatly appreciated,

Keith

Thanks for your reply Sixthsense, unfortunately I don’t understand Excel or VBA well enough to be able to do this.

All that I have got has been built using modified snippets from other users, then trying to learn from these snippets and modifying them for use elsewhere.

Could you please elaborate using the above example?

TIA
Keith

Ok, I have now created another variable called RowRange

RowRange = "F" & rw & ":" & "DD" & rw this evaluates to F108:DD108 which is the correct range.

I have then tried all sorts of combinations of:-

.Cells(rw, 4).Value = "=" & CountIf(RowRange > 0)
.Cells(rw, 4).Value = "=" & Evaluate(CountIf(RowRange > 0))
.Cells(rw, 4).Value = "=" & Evaluate("CountIf(RowRange > 0"))
.Cells(rw, 4).Value = "=" & Evaluate CountIf(RowRange > 0

but I am still up against a brick wall.

Could someone please advise on the correct syntax, or I am approaching this the correct way even.

TIA
Keith