MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using a variable in the Range().Select statement?

Posted by Travis on August 02, 2001 11:33 AM

I have a problem. I am wanting to select a certain range based of the result of a formula. I am setting "CellNumber" equal to the formula result by referencing the cell the formula is in. I am then wanting to select a range from C4:E(CellNumber). I just can't get it right.

Sub CellCount()
Dim CellNumber
Dim CellCount
ActiveCell.FormulaR1C1 = "=COUNTIF(PM!RC[2]:R[629]C[2],"">0"")+3"
CellNumber = "=R[1]C[2]"
'This is were I get the error!
Range("C4", "E" & CellNumber).Select
End Sub

Posted by Barrie Davidson on August 02, 2001 11:36 AM

Travis, have a look at 25444.html