Macro to Fill Down to last row of data

[whitey1987]

Hi everyone,

Hoping someone can help me with this. I've created a Macro to input an IF formula into my spreadsheet. The number of rows in the spreadsheet changes on a regular basis. So I would like to adapt my macro so that the formula fills to the last row of data each time it is run.

Can anyone please adapt the code I have below?


Sub Macro1()
'
' Macro1 Macro
' Inserts If formula.
'

'
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-2]=""FGH"",RC[1]=""123""),RC[-2]&RC[1],IF(AND(RC[-2]=""FGH"",RC[1]<>""123""),RC[-2],IF(AND(RC[1]=""123"",OR(RC[-2]=""IJK"",RC[-2]=""ABC"",RC[-2]=""CDE"")),RC[-2]&RC[1],RC[-2]&RC[-1])))"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D880")
Range("D2:D880").Select
Range("A1").Select
End Sub

Thank you in advance for any help you can give.

 

[VoG]

Maybe this

Sub Macro1()
'
' Macro1 Macro
' Inserts If formula.
'

'
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Columns("D").Insert
Range("D2:D" & LR).FormulaR1C1 = _
"=IF(AND(RC[-2]=""FGH"",RC[1]=""123""),RC[-2]&RC[1],IF(AND(RC[-2]=""FGH"",RC[1]<>""123""),RC[-2],IF(AND(RC[1]=""123"",OR(RC[-2]=""IJK"",RC[-2]=""ABC"",RC[-2]=""CDE"")),RC[-2]&RC[1],RC[-2]&RC[-1])))"
End Sub

 

[whitey1987]

Hi,

Thanks for the reply. That works brilliantly. Could you talk me through how this code works? It will help me progress with my macro making .

Thanks again, much appreciated.

 

[VoG]

Basically LR gets the last filled row on the sheet then the formula is entered in D2:D & LR

 

[whitey1987]

Thanks VoG. This will come in handy on some other macros.

Another quick question whist I'm here....

Can you force a macro to only run within a particular worksheet? If so, how would you adapt the code above to achieve this...

Am I right in thinking it would be something like;

If isheet.Name = "SheetExample" Then ..... code here?

 

[VoG]

Yes, like this

If Sheets(isheet).name="SheetExample" Then

 

[whitey1987]

Thanks again, you've been a great help!

 

[gopal.k.adhikari]

Basically LR gets the last filled row on the sheet then the formula is entered in D2:D & LR

Hi..Thank you for your code. However, my "D1" field gets displaced. Could you please guide me on this??

My coding:

Sub CHK()
'
' CHK Macro
'


'
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Columns("D").Insert
Range("D2:D" & LR).FormulaR1C1 = _
"=(RC[-2]/RC[-1])*100"

End Sub