Copy another formula in excel to the last row of data 1

[john434]

Hi,

This is probably very easy, but being very new to VBA I've gotten myself a little stuck. I record macro's then try to edit them in order to make them a little bit more dynamic and try to take out any unnessessary code.

I have browsed this forum and found how to Copy a formula down to the last row of data, like this:

Range("AT1").Select
ActiveCell.FormulaR1C1 = "RESCF"
Dim lRow As Long
lRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
With Range("AT2")
.FormulaR1C1 = _
"=RC[-35]"
.AutoFill Destination:=Range("AT2:AT" & lRow)
End With

I then want to do pretty much the same again, but using another formula based on a different cell. Like this:

Range("AU1").Select
ActiveCell.FormulaR1C1 = "RESNEW"
Dim lRow As Long
lRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
With Range("AU2")
.FormulaR1C1 = _
"=IF(RC[-6]=""Block Subsidy"",""SPOT"",""BLOCK"")"
.AutoFill Destination:=Range("AU2:AU" & lRow)
End With

When i try to run this I get the following error:

"Compile Error
Duplicate declaration in current scope"

and VB highlights "lRow As Long", now i assume this is because i cannot use this declaration more than once. Why is this and how can i get round it?

Thanks in advance for your help

 

[SkipVought]

HI,

Once you declare a variable, you cannot declare it again. ONCE is enough.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[john434]

Thanks for that Skip, i believe you've helped me before!!

So for further formula's all i need to do is:

Range("AU2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-6]=""Block Subsidy"",""SPOT"",""BLOCK"")"
.AutoFill Destination:=Range("AU2:AU" & lRow)

Is that correct or have i totally missed the point??

 

[PHV]

You MAY have to calculate lRow but you DON'T have to declare (Dim) it again.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[john434]

Thanks guys,

I ended up using the following for the subsequent formula's and it seemed to work just great:

Range("AU2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-6]=""Block Subsidy"",""SPOT"",""BLOCK"")"
Selection.AutoFill Destination:=Range("AU2:AU" & lRow)

I'm not sure what you mean "PHV" by a may have to calculate it again, it seems to be fine as is.

thank you both for your help!!

Skip have a star!!