VBA Macro to apply formulas to all worksheets in a workbook

[stabilos]

Hi All,

Kind of new to VBA, and have been studying previous posts
I have 100s of worksheets with different ranges, but all need the same formulas applied to column A and B (if blank, preferably so that i can add and update the worksheets later).
I can only get A2 to populate, any chance someone can assist.

Many thanks

Sub Formula()

Dim lastRow As Long
Dim rs As Worksheet
lastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each rs In ThisWorkbook.Worksheets
Range("A2").Formula = "=VLOOKUP(B2,'D:\test\[test.xlsx]sheet1'!$AG:$AG,1,FALSE)"
Range("B2").Formula ="=C2&":"&D2"
End
Next rs


End Sub

 

[Micron]

You don't say for how many rows. If that is based on the last cell of some column having data (e.g. F in the example), that could be like

Dim lrow As Long

lrow = Cells(Rows.Count, "F").End(xlUp).Row
Range("A2:A" & lrow).Formula = "=C2 & D2"

That would create a relative reference formula from A2 to last row value; i.e. C2 & D2, C3 & D3, C4 & D4 ...
You can also use the Find method to locate the last used cell.
Please enclose code in code tags (use vba button on posting toolbar) to maintain indentation and readability.

 

[DanteAmor]

Hi @stabilos.

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Considering the following:

but all need the same formulas applied to column A and B

1. You want to apply the formulas in column A and B, but we must consider columns C and D as a reference, since the second formula, the one that goes in column B, concatenates C and D.

2. If the sheet is empty, then it does not put the formulas.

3. The macro must loop through all sheets, but you must reference each sheet in each range, see how I put sh before the range, this way you are ensuring that the formulas are put on each sheet.

Sub Put_Formulas()
  Dim sh As Worksheet
  Dim f As Range
  Dim lastRow As Long
  
  For Each sh In ThisWorkbook.Sheets
    Set f = sh.Range("C:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
    If Not f Is Nothing Then
      lastRow = f.Row
      If lastRow > 1 Then
        With sh.Range("A2:A" & lastRow)
          .Formula = "=VLOOKUP(B2,'D:\test\[test.xlsx]sheet1'!$AG:$AG,1,FALSE)"
          .Offset(0, 1).Formula = "=C2&D2"
        End With
      End If
    End If
  Next
End Sub

4. You must have your reasons, but the only thing that the formula in column A does is if the data exists in the test book, it puts the same data that you already have in column B.
But maybe it's an example and you need to know how to cycle through the sheets and put formulas in all the cells of A and B.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[stabilos]

Sub Put_Formulas() Dim sh As Worksheet Dim f As Range Dim lastRow As Long For Each sh In ThisWorkbook.Sheets Set f = sh.Range("C:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious) If Not f Is Nothing Then lastRow = f.Row If lastRow > 1 Then With sh.Range("A2:A" & lastRow) .Formula = "=VLOOKUP(B2,'D:\test\[test.xlsx]sheet1'!$AG:$AG,1,FALSE)" .Offset(0, 1).Formula = "=C2&D2" End With End If End If Next End Sub

Dante
Many thanks for the warm welcome, and yes this works perfectly, this really saves me so much time as was copy and pasting for 800+ worksheets
Great job, wish you a wonderful day
Stabilos