Defining a Range

[madkinson]

I need to select a range, but it is in the middle of a column. For example, I have a macro that will take a formula in cell F2 and copy it into a range from F3 to the end of the column. I already have defined a variable called LastRow, however I cannot seem to get the syntax right for the Range.Select statement. What I tried was:

Range(F3,"F"&LastRow).Select

I appreciate your patience with this newbie question.

 

[Von Pookie]

Try:

Range("F3:F" & LastRow)

 

[madkinson]

That didn't work. My statement looks like this:

Range("F3:F" & LastRow).Select

I got the Runtine error 1004. I am using Excel XP if that helps. Thanks.

 

[Norie]

Can you post the rest of the code?

 

[Von Pookie]

Is LastRow referring to the row number or the last cell? I was assuming it was a row number.

 

[madkinson]

Here is the relevant piece of the code:

'Create conversion formula
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="" "",IF(RC[-2]="" "",ABS(RC[-1]),"" ""),"" "")"

NOTE: This picks only the rows that meet the relevant criteria (which is copy the formula if both Columns B and D have no data).

'Copy formula into rest of column
Range("F2").Select
Selection.Copy
Range("F3:F" & LastRow).Select
ActiveSheet.Paste

NOTE: This should copy the formula in F2 to the remainder of Column F.

With the particular dataset I'm using for testing, LastRow has a value of 1105. So I "thought" I was copying the formula from cell F2 to cells F3 through F1105.

Where am I messing up?

 

[Norie]

I think we need to see where LastRow is coming from.

By the way that code could be replaced with this one-liner.

 Range("F2:F" & LastRow)..FormulaR1C1 = _
"=IF(RC[-4]="" "",IF(RC[-2]="" "",ABS(RC[-1]),"" ""),"" "")"

If that doesn't work then I really think the problem is with how you've set the value for LastRow.

 

[madkinson]

AARRGGHH I forgot to define it in the subroutine that I broke out of the main macro, so you were absolutely right that LastRow is the problem. Once I tied the value to the main routine, it ran like a charm. Thanks!!