Macro Formula Relative Referencing

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

HansVogelaar

MVP

Jun 13, 2025

Thank you. Unfortunately, that does not provide a clue.

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

And indicate which cell or cells you select before running the macro.

Susan1085
Copper Contributor
Jun 16, 2025
I sent a reply with a link to a small worksheet in google drive.
I do not know what is going on. I have posted with spreadsheet attached 3 times and it has never shown up.
It seems to be taking over 24 hours for my replies to appear.....if they appear at all!

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

Forum Discussion

Resources