VBA- A macro which Applies Formula down a column until last row

[DesperateKid]

Hi
I need a macro for VBA which applies a formula From cell I4 to the last I value for which Column A has data filled in.
The formula in the worksheet would be equivalent to "= $j3+1" In Cell I4 which is then dragged down(autofilled) until the last row in column I.

Thanks so much!!

 

[Darren Bartrup]

Something like this:

Public Sub Fill_Formula()

    Dim rLastCell As Range
    Dim wrkSht As Worksheet
    
    Set wrkSht = ThisWorkbook.Worksheets("Sheet1")
    Set rLastCell = LastCell(wrkSht, 1)


    With wrkSht
        .Cells(4, 9).FormulaR1C1 = "=R[-1]C10+1"
        .Cells(4, 9).AutoFill Destination:=.Range(.Cells(4, 9), .Cells(rLastCell.Row, 9)), Type:=xlFillDefault
    End With


End Sub


Public Function LastCell(wrkSht As Worksheet, Optional Col As Long = 0) As Range


    Dim lLastCol As Long, lLastRow As Long
    
    On Error Resume Next
    
    With wrkSht
        If Col = 0 Then
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        Else
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row
        End If
        
        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1
        
        Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0
    
End Function

 

[JoeMo]

Sub FillFormula()
Range("I4").Formula = "=$J3+1"
Range("I4", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

 

[Darren Bartrup]

Much tidier than mine.
Will FillDown stop when column H is empty rather than column A though?

 

[JoeMo]

Much tidier than mine.
Will FillDown stop when column H is empty rather than column A though?

The fill in col I will stop at the row containing the last filled cell in col A.

 

[ajandrs]

Sub FillFormula()
Range("I4").Formula = "=$J3+1"
Range("I4", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

I registered just to say thank you for this post. I've been teaching myself VBA over the last couple weeks and have already added a ton of automation to my workbooks ... probably relatively simple stuff but having added it, it has been a massive time saver throughout my days. This however was the one piece I just couldn't get after days of reading and googling trying to figure it out. Modified this block of code to fit my needs and boom, it works. Can't thank you enough for this!!

 

[JoeMo]

I registered just to say thank you for this post. I've been teaching myself VBA over the last couple weeks and have already added a ton of automation to my workbooks ... probably relatively simple stuff but having added it, it has been a massive time saver throughout my days. This however was the one piece I just couldn't get after days of reading and googling trying to figure it out. Modified this block of code to fit my needs and boom, it works. Can't thank you enough for this!!

Thanks for registering and for taking the time to let me know you found this useful.

 

[whitehead90]

Sub FillFormula()
Range("I4").Formula = "=$J3+1"
Range("I4", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

Hi Joe,

I wa looking to do something similar only with a different formula. I repalced my formula and cells with the VBA macro you provided but I get a compile error window and it highlights the following line red:


Sub FillFormula()
Range("B2").Formula = "=INDIRECT(A2&"!S63")"
Range("B2", "B" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

Can you help me figure out why it's won't work?

In my Summary sheet, I have sku numbers listed in column A that represent each tab in the workbook and I'm trying to retrieve the information listed in S63 in each tab sheet for each sku. It works fine everytime I enter it manually, but it would be nice to create a macro that runs it each time for every workbook.

Please advise, thank you,

 

[JoeMo]

Hi Joe,

I wa looking to do something similar only with a different formula. I repalced my formula and cells with the VBA macro you provided but I get a compile error window and it highlights the following line red:


Sub FillFormula()
Range("B2").Formula = "=INDIRECT(A2&"!S63")"
Range("B2", "B" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

Can you help me figure out why it's won't work?

In my Summary sheet, I have sku numbers listed in column A that represent each tab in the workbook and I'm trying to retrieve the information listed in S63 in each tab sheet for each sku. It works fine everytime I enter it manually, but it would be nice to create a macro that runs it each time for every workbook.

Please advise, thank you,

Your syntax is not right - try:

Range("B2").Formula = "=INDIRECT(" '" & A2&"'!S63")"

 

[whitehead90]

Your syntax is not right - try:

Range("B2").Formula = "=INDIRECT(" '" & A2&"'!S63")"

I get a syntax error with the provided formula you gave me. Originally, the formula I used when entered manually in my summary sheet is =Indirect(A2&"!S63") - it looks up the sku number in A2 and locates the tabsheet and returns the value that is located in cell S63. Does that make sense? I am a beginner at VBA , so I'm not sure if the same formula would work in a Macro.