how to make this formula variable

[Kelvin]

Can anyone help with this.

For c = 1 to 4
cells(1,c).formula="=counta(a2:a10)"
next c

this example places the counta formula in a1,b1,c1 and d1.
How do I make the formula variable, so it works on the specific column.
It would be nice if I could use the same variable( c ) as the column
reference in the counta formula . Kind of stuck

Any help would be appreciated

KB

 

[Don Guillett]

Sub doformula()'Just leave the values
For i = 1 To 4
'Cells(1, c).Formula = "=counta(a2:a10)"
Cells(1, i) = Application.CountA(Range(Cells(2, i), Cells(10, i)))
Next i
End Sub

 

[Bob Phillips]

For c = 1 to 4
cells(1,c).formulaR1C1="=COUNTA(R2C:R10C)"
next c


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Another one:

For c = 1 to 4
cells(1,c).formular1c1 = "=counta(r2c:r10c)"
next c

or just plop the formula in all four cells in one shot:

range("a1:d1").formular1c1 = "=counta(r2c:r10c)"

Another one to see how you could use that c as the column in the formula:


For c = 1 To 4
Cells(1, c).Formula = "=counta(" & Cells(2, c).Address(0, 0) _
& ":" & Cells(10, c).Address(0, 0) & ")"
Next c

 

[Kelvin]

Thanks Don,
That worked perfectly
I appreciate the help.

Kelvin

 

[Kelvin]

Thanks all ,



I appreciate all the help