Share via

Problem running Excel macro, Run-Time error 1004. The formula is incomplete. Make sure it has an ending square bracket.

Anonymous
2024-08-26T07:22:33+00:00

Hey,

I'm racking my brains here, we've been running this macro for the past year in an excel sheet. then i went on holiday and suddenly it doesn't work anymore. now i'm not sure if a co-worker changed something but i doubt it.

It's not a macro i wrote myself, my knowledge of macro's is also very limited so any help would be very much appreciated.

Sub Macro1()

'

' Macro1 Macro

'

' 

Dim x As Integer 'row number 

Dim d As Date 'Date 

Range("BE2:BE9000").Select 

Selection.ClearContents 

x = 2 

Do While Sheets("LOAD").Cells(x, 3) <> Empty 

    If Sheets("LOAD").Cells(x, 49) <> Empty Then 

        d = Sheets("LOAD").Cells(x, 49).Value 'manufacturing date 

    Else 

        If Sheets("LOAD").Cells(x, 50) <> Empty Then 

            d = Sheets("LOAD").Cells(x, 50).Value 'Expected ship date 

        Else 

            d = Sheets("LOAD").Cells(x, 26).Value  'Promised ship date 

        End If 

    End If 

    If d - Date < 0 Then 

        Sheets("LOAD").Cells(x, 57).Value = -1 

    Else 

        Sheets("LOAD").Cells(x, 57).Value = d - Date 

    End If 

    x = x + 1 

Loop

'Pivot refresh 

Sheets("Past").Select 

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'-1'", xlDataAndLabel, True 

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 

Sheets("Today").Select 

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'0'", xlDataAndLabel, True 

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 

Sheets("Today+1").Select 

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'0'", xlDataAndLabel, True 

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 

Sheets("Today+2").Select 

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'0'", xlDataAndLabel, True 

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-09-21T21:04:03+00:00

    Hi Berloni

    Please, try the following macro

    Sub Macro1() 

    Dim x As Integer 'row number 

Dim d As Date 'Date 

Range("BE2:BE9000").Value = "" 

x = 2 

Do While Sheets("LOAD").Cells(x, 3) <> Empty 

    If Sheets("LOAD").Cells(x, 49) <> Empty Then 

           d = Sheets("LOAD").Cells(x, 49).Value 'manufacturing date 

    Else 

        If Sheets("LOAD").Cells(x, 50) <> Empty Then 

           d = Sheets("LOAD").Cells(x, 50).Value 'Expected ship date 

        Else 

            d = Sheets("LOAD").Cells(x, 26).Value  'Promised ship date 

        End If 

    End If 

    If d - Date < 0 Then 

        Sheets("LOAD").Cells(x, 57).Value = -1 

    Else 

        Sheets("LOAD").Cells(x, 57).Value = d - Date 

    End If 

    x = x + 1 

Loop

    'Pivot refresh 

    With Sheets("Past").PivotTables("PivotTable1") 

    .PivotSelect "'-1'", xlDataAndLabel, True 

    .PivotCache.Refresh 

End With 

With Sheets("Today").PivotTables("PivotTable1") 

    .PivotSelect "'0'", xlDataAndLabel, True 

    .PivotCache.Refresh 

End With 

With Sheets("Today+1").PivotTables("PivotTable1") 

    .PivotSelect "'0'", xlDataAndLabel, True 

    .PivotCache.Refresh 

End With 

With Sheets("Today+2").PivotTables("PivotTable1") 

    .PivotSelect "'0'", xlDataAndLabel, True 

    .PivotCache.Refresh 

End With

    End Sub

    Was this answer helpful?

    0 comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more