Can Visual Basic get information from a cell and then use that as a
variable?

I have a cell that changes using the function counta. The number in
the Counta formula will change from day to day. I would like to use
that number as a variable in a macro.

Does anyone have any VBA code that shows how this could be done?

SWT

myVar = Range("B2").Value

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SWT" wrote in message
oups.com...
Can Visual Basic get information from a cell and then use that as a
variable?

I have a cell that changes using the function counta. The number in
the Counta formula will change from day to day. I would like to use
that number as a variable in a macro.

Does anyone have any VBA code that shows how this could be done?

SWT

Well, you could use something like
x = worksheetfunction.counta(range("b2:b116"))
and x would be the variable with the changing data.

"SWT" wrote:

Can Visual Basic get information from a cell and then use that as a
variable?

I have a cell that changes using the function counta. The number in
the Counta formula will change from day to day. I would like to use
that number as a variable in a macro.

Does anyone have any VBA code that shows how this could be done?

SWT

Thanks all,

this is the final macro written which works exactly as i hoped.

Sub QB_Format()
'
' QB_Format Macro
' Macro recorded 10/19/2005 by SWT
' Copies rows 4 through 6 and pastes to varying rows

x = Range("Count").Value
sRange = "A7:K" & x
Range("A4:K6").Copy Destination:=Range(sRange)

End Sub

SWT

I revised it and found that this also worked: I eliminated having to
have a counta function in the sheet:

Sub QB_Format()
'
' QB_Format Macro
' Macro recorded 10/19/2005 by SWT
' Copies rows 4 through 6 and pastes to varying rows
R = WorksheetFunction.CountA(Range("M_Amount"))
S = (R * 3) + 3
sRange = "A7:K" & S
Range("A4:K6").Copy Destination:=Range(sRange)