Forum Discussion

Copper Contributor

Jun 12, 2025

Solved

Macro Formula Relative Referencing

Hi there I am trying to create a macro to use at the end of every month to convert a report into a format the client requires. The amount of rows in the report will differ every month so using r...

excel

Formulas and Functions

Macros and VBA

HansVogelaar

Jun 17, 2025

That worked. Here is a working macro:

Sub TransformData()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    ' Delete Customer Code and Customer Name
    Range("A1:B1").EntireColumn.Delete
    ' Move Date to column A
    Range("I1").EntireColumn.Cut
    Range("A1").EntireColumn.Insert
    ' Move Transaction Code to column B
    Range("O1").EntireColumn.Cut
    Range("B1").EntireColumn.Insert
    ' Move Docket number to column C
    Range("I1").EntireColumn.Cut
    Range("C1").EntireColumn.Insert
    ' Delete Customer Address1 to Driver Code
    Range("F1:J1").EntireColumn.Delete
    ' Delete Paid Value to Location Description
    Range("G1:K1").EntireColumn.Delete
    ' Create new headers
    Range("G1").Value = "Percy Mills"
    Range("H1").Value = "Other"
    ' Create Formulas in columns G and H
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("G2:G" & LastRow).Formula = "=IF(ISNUMBER(SEARCH($G$1,E2)),F2,"""")"
    Range("H2:H" & LastRow).Formula = "=IF(ISNUMBER(SEARCH($G$1,E2)),"""",F2)"
    ' Totals
    Range("A" & LastRow + 1).Value = "Totals"
    Range("F" & LastRow + 1).Resize(1, 3).Formula = "=SUM(F2:F" & LastRow & ")"
    ' Number format for columns F to H
    Range("F2:H" & LastRow + 1).NumberFormat = "#,##0.00"
    ' Column Widths
    Range("A1").CurrentRegion.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

Susan1085

Copper Contributor

Jun 16, 2025

https://drive.google.com/drive/folders/115o81bgCFJ0gwz1VsOUaE5cLk-o7VMY_

Hi Hans

I have attached the files 3 times now and they don't show up.

Link to google drive above.

I don't select any cells before running the macro.

Rawdata1 - 14 rows long and recorded macro- Macrotest1 - OK

Rawdata2 - 17 rows long and ran same macro - results = Marcotest2 - not OK

Formulas in Columns G & H does not run in rows 15,16 & 17

HansVogelaar

MVP

Jun 17, 2025

That worked. Here is a working macro:

Sub TransformData()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    ' Delete Customer Code and Customer Name
    Range("A1:B1").EntireColumn.Delete
    ' Move Date to column A
    Range("I1").EntireColumn.Cut
    Range("A1").EntireColumn.Insert
    ' Move Transaction Code to column B
    Range("O1").EntireColumn.Cut
    Range("B1").EntireColumn.Insert
    ' Move Docket number to column C
    Range("I1").EntireColumn.Cut
    Range("C1").EntireColumn.Insert
    ' Delete Customer Address1 to Driver Code
    Range("F1:J1").EntireColumn.Delete
    ' Delete Paid Value to Location Description
    Range("G1:K1").EntireColumn.Delete
    ' Create new headers
    Range("G1").Value = "Percy Mills"
    Range("H1").Value = "Other"
    ' Create Formulas in columns G and H
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("G2:G" & LastRow).Formula = "=IF(ISNUMBER(SEARCH($G$1,E2)),F2,"""")"
    Range("H2:H" & LastRow).Formula = "=IF(ISNUMBER(SEARCH($G$1,E2)),"""",F2)"
    ' Totals
    Range("A" & LastRow + 1).Value = "Totals"
    Range("F" & LastRow + 1).Resize(1, 3).Formula = "=SUM(F2:F" & LastRow & ")"
    ' Number format for columns F to H
    Range("F2:H" & LastRow + 1).NumberFormat = "#,##0.00"
    ' Column Widths
    Range("A1").CurrentRegion.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

Susan1085
Copper Contributor
Jun 17, 2025
Many thanks Hans. That is working perfectly.