how to make this formula variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kelvin - 03 Jan 2008 22:23 GMT

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

Signature

KWB

Reply to this Message

Don Guillett - 03 Jan 2008 22:34 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Can anyone help with this.
>
[quoted text clipped - 10 lines]
>
> KB

Reply to this Message

Kelvin - 03 Jan 2008 22:46 GMT

Thanks Don,
That worked perfectly
I appreciate the help.

Kelvin

Signature

KWB

> Sub doformula()'Just leave the values
> For i = 1 To 4
[quoted text clipped - 17 lines]
> >
> > KB

Reply to this Message

Bob Phillips - 03 Jan 2008 22:39 GMT

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

Signature

---
HTH

Bob

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

> Can anyone help with this.
>
[quoted text clipped - 10 lines]
>
> KB

Reply to this Message

Dave Peterson - 03 Jan 2008 22:45 GMT

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

> Can anyone help with this.
>
[quoted text clipped - 13 lines]
> --
> KWB

Signature

Dave Peterson

Reply to this Message

Kelvin - 04 Jan 2008 00:34 GMT

Thanks all ,

I appreciate all the help

Signature

KWB

> Another one:
>
[quoted text clipped - 30 lines]
> > --
> > KWB

Reply to this Message