Copy Formula to last active cell in A

[bloodybrit90]

Hi Guys,

Right now this is the VBA I have. Instead of copying the formula down to FG403 all the time, I need to copy it to the last active cell (cell with data) in column A.

Thanks for the help


Range("FG33").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-5]=""Yes"",RC[-2]=""Yes"",RC[-1]=""Yes""),""Yes"",""No"")"
Range("FG33").Select
Selection.AutoFill Destination:=Range("FG33:FG403")
Range("FG33:FG403").Select
Selection.Copy
Range("FG33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[JoeMo]

Try this:

Sub test()
Dim lR As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
With Range("FG33", "FG" & lR)
    .FormulaR1C1 = _
    "=IF(OR(RC[-5]=""Yes"",RC[-2]=""Yes"",RC[-1]=""Yes""),""Yes"",""No"")"
    .Parent.Calculate
    .Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

 

[bloodybrit90]

Joe, When I use your code, I receive a message "Compile error, expected end sub". The following is everything in my VBA window. Sorry I am a noob to VBA.

Sub ForeclosureInfo()
'
' ForeclosureInfo Macro
' Paste formula to determin flreclosures, then copy paste value all foreclosures
'
' Keyboard Shortcut: Ctrl+Shift+F
'
End Sub
Sub Foreclosure()
'
' Foreclosure Macro
' find foreclosure info and paste value.
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Sub test()
Dim lR As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
With Range("FG33", "FG" & lR)
.FormulaR1C1 = _
"=IF(OR(RC[-5]=""Yes"",RC[-2]=""Yes"",RC[-1]=""Yes""),""Yes"",""No"")"
.Parent.Calculate
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub




This then has a button in one of the worksheets.

 

[bloodybrit90]

The error occurred in the second sub forclosure() line.

 

[JoeMo]

Joe, When I use your code, I receive a message "Compile error, expected end sub". The following is everything in my VBA window. Sorry I am a noob to VBA.

Sub ForeclosureInfo()
'
' ForeclosureInfo Macro
' Paste formula to determin flreclosures, then copy paste value all foreclosures
'
' Keyboard Shortcut: Ctrl+Shift+F
'
End Sub
Sub Foreclosure()
'
' Foreclosure Macro
' find foreclosure info and paste value.
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Sub test()
Dim lR As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
With Range("FG33", "FG" & lR)
.FormulaR1C1 = _
"=IF(OR(RC[-5]=""Yes"",RC[-2]=""Yes"",RC[-1]=""Yes""),""Yes"",""No"")"
.Parent.Calculate
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub




This then has a button in one of the worksheets.

Assign the button to Sub test and get rid of the rest of what you posted. You can rename what I posted from test to Foreclosure before assigning the button.

 

[bloodybrit90]

Thanks for the help Joe. It worked.