Using a variable in a range().select statement??

Posted by Travis on July 31, 2001 6:48 AM

This is the start of my macro. My problem is in the last line of code. Based off the value of "CellNumber", I want to select a certain range. I need help adjusting my macro.

Sub TEST()
Dim CellNumber
Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(PM!RC[2]:R[629]C[2],"">0"")-1"
CellNumber = "=R[1]C[2]"

Range("C4:E[CellNumber]").Select

End Sub

Posted by faster on July 31, 2001 8:05 AM

'*****************************
'try this
Sub TEST()
Dim CellNumber
Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(PM!RC[2]:R[629]C[2],"">0"")-1"

'CellNumber = "=R[1]C[2]"
CellNumber = Selection.Offset(1, 2).Address

'Range("C4:E[CellNumber]").Select
Range("C4", CellNumber).Select

End Sub

Posted by Travis on July 31, 2001 1:15 PM

Re: Didn't work, still need help??

It doesn't work properly. In my previous code, CellNumber = 5 (based off the formula result). I then want to select range (c4:e5). Your changes gave me range (c3:e4). It seems to be close!!

Posted by faster on August 01, 2001 8:35 AM

Re: Didn't work, still need help??

'let me know if this works

Sub TEST()
Dim CellNumber
Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(PM!RC[2]:R[629]C[2],"">0"")-1"

CellNumber = Selection.Offset(0, 2).Column

Range("C4", "E" & CellNumber).Select

End Sub

Posted by Travis on August 01, 2001 1:51 PM

Re: Didn't work, still need help??

I think it is going to work this time. Thanks Alot!