Circular reference using SUM

[Arcto]

Hi,

Apologies in advance if there is an answer to this somewhere, I have spent hours searching google and can't find anything that solves my issue!

I have a VBA (below) which I run as an add-on when excel opens. Every time Excel opens it tells me there is a circular reference related to the VBA in Sheet1!$P$2. I am guessing the error is from Range("P" & LastRow + 1).Formula = "=SUM(P2:P" & LastRow & ")" however I got this line of code from a solution someone posted which apparently worked quite well for them. The VBA functions properly but the error message is annoying and I'm thinking (hoping) it is a simple fix?

Thanks in advance


Sub BaxterSum()
Dim ws As Worksheet
Dim LastRow As Long
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Columns(16).NumberFormat = "General"
Columns(18).NumberFormat = "General"
'Delete Blank Rows
On Error Resume Next
Range("O1:O1000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
LastRow = Range("G" & Rows.Count).End(xlUp).Row
Range("O" & LastRow + 1) = "Total"
Range("P" & LastRow + 1).Formula = "=SUM(P2:P" & LastRow & ")"
'Enter formula for fuel surcharge (last number is fuel surcharge as a decimal)
Range("R" & LastRow + 1).Formula = "=(Q" & LastRow + 1 & ")*.046"
'Colour Last Row
Range(Cells(LastRow + 1, "O"), Cells(LastRow + 1, "P")).Interior.ColorIndex = 3
'Freeze top row
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With
Next ws
End Sub

 

[James006]

Hi,

From looking at your code ... nothing seems wrong ...

You could insert following instruction at the top :

Application.Displayalerts = False

and test if the error message disappears or not ...

Hope this will help

 

[Arcto]

Thanks James,

Unfortunately that doesn't fix the issue, the warning comes before I run the macro itself, it appears as soon as I launch Excel...​

 

[DanteAmor]

The problem is when there is no data in the sheet or there is only one record in the sheet, in those cases the formula is in P2 =sum(P1:P2) and there is the circular reference.


Try this:

Sub BaxterSum()
    Dim ws As Worksheet
    Dim LastRow As Long
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Columns(16).NumberFormat = "General"
        Columns(18).NumberFormat = "General"
        'Delete Blank Rows
        On Error Resume Next
        Range("O1:O1000").Select
        Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
[COLOR=#0000ff]        On Error GoTo 0[/COLOR]
        
        LastRow = Range("G" & Rows.Count).End(xlUp).Row
[COLOR=#0000ff]        If LastRow = 1 Then LastRow = 2[/COLOR]
        
        Range("O" & LastRow + 1) = "Total"
        Range("P" & LastRow + 1).Formula = "=SUM(P2:P" & LastRow & ")"
        'Enter formula for fuel surcharge (last number is fuel surcharge as a decimal)
        Range("R" & LastRow + 1).Formula = "=(Q" & LastRow + 1 & ")*.046"
        'Colour Last Row
        Range(Cells(LastRow + 1, "O"), Cells(LastRow + 1, "P")).Interior.ColorIndex = 3
        'Freeze top row
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
            .FreezePanes = True
        End With
    Next ws
End Sub

 

[Arcto]

Hi Dante,

Apologies, I only just saw your reply. Unfortunately this doesn't work either, it happens regardless of what spreadsheet is being opened and the error comes up before a sheet itself opens up as I am running it as an add-on that is loaded each time excel opens.

Thanks Garry