Circular References Error- Remove formula and use vba?

[pete4monc]

Hi guys - Sorry if there is two questions in this one post but the first leads onto the second.

I have a spreadsheet which is stored on a network drive, not shared but does have vba. Every time someone opens it up it comes up with the below message and this messes up the spread sheet. Any ideas? Should I remove the formula and get the vba to insert the date into the cell when the button is clicked.

I'm not sure how to get the date inserted into column H of the active row when the data is pasted.
Below is my vba which does not work? Any help would be much appreciated. Thanks.

Application.ScreenUpdating = False
    Dim lCol As Long, sAddr As String, fnd As Range, x As Long, ws As Worksheet
    lCol = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column
    x = ActiveCell.Row
    Set fnd = Rows(x).Find("y", LookIn:=xlValues, LookAt:=xlWhole)
    If Not fnd Is Nothing Then
        sAddr = fnd.Address
        Do
            Set ws = Sheets(Cells(4, fnd.Column).Value)
           
            With ws
   
    Range("A" & x & ":G" & x).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
    [B]Range("H" & (ActiveCell.Row)).Select
    ActiveCell.Value = Date[/B]
   
End With
            Set fnd = Rows(x).FindNext(fnd)
        Loop While fnd.Address <> sAddr
        sAddr = ""
    End If
    Application.ScreenUpdating = True

 

[KRice]

Have you tracked the source of the circular reference error?....Formulas tab, then under Formula Auditing > Error Checking (click drop down) > then select Circular References

 

[pete4monc]

Have you tracked the source of the circular reference error?....Formulas tab, then under Formula Auditing > Error Checking (click drop down) > then select Circular References

Hi KRice
Yes, formula is
=IF((OR(G5="Spin", G5="LW",G5="HJ5",G5="Alphaset")),IF(H5="",NOW(),H5), "")

I believe this IF(H5="",NOW(),H5), "") is causing the issue but I need it so it doesn't over write the date every time the spreadsheet is opened?
Maybe some one could clarify that for me? Thanks

 

[pete4monc]

Just an update in case someone is following.

I have added a line to insert a date into column and then a line to copy that date into the tab/active line when the vba copy/paste those cells.
I'm sure someone out there could find another easier way but due to my inexperience at vba, this is the work around I managed to get to work.

I have inserted my script in case any one wants to use it?

Dim Answer As VbMsgBoxResult

    Answer = MsgBox("Is the curser in the correct place?", vbYesNo, "Run macro")
    If Answer = vbYes Then
           
    
    
    Application.ScreenUpdating = False
    Dim lCol As Long, sAddr As String, fnd As Range, x As Long, ws As Worksheet
    lCol = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column
    x = ActiveCell.Row
    Set fnd = Rows(x).Find("y", LookIn:=xlValues, LookAt:=xlWhole)
    If Not fnd Is Nothing Then
        sAddr = fnd.Address
        Do
        
        [B]Range("I" & (ActiveCell.Row)).Select
        ActiveCell.Value = Date[/B]
        
            Set ws = Sheets(Cells(4, fnd.Column).Value)
            
            With ws
    
    Range("A" & x & ":G" & x).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
    [B]Range("I" & x).Copy .Cells(.Rows.Count, "H").End(xlUp).Offset(1)[/B]
    
End With
            Set fnd = Rows(x).FindNext(fnd)
        Loop While fnd.Address <> sAddr
        sAddr = ""
    End If
    Application.ScreenUpdating = True
   
     
    MsgBox "All matching data has been copied."
    
    End If
        
    Range("M5:M999").Select
    Selection.ClearContents
    Range("A2").Select
    
    ActiveWorkbook.Save
    
    
End Sub

Thanks again to every one.

PS: I will tidy up my code, when I get time.