CODEWITHSUNDEEP
excel
Lkaf Temravet
Lkaf Temravet

Reputation: 993

EXCEL : Circular reference issue

I want to check the value of the current cell if it's null then generate a random number else keep it as it is.

=IF(P4<>0,RANDBETWEEN(1,100),P4)

but when I open the sheet I get an issue of circular reference and the value of P4 is changing even though old value is not null

Upvotes: 0

Views: 99

Answers (1)

user4039065
user4039065

Reputation:

Put this code in the worksheet's private code sheet (right-click worksheet tab, View Code).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If not intersect(Target, Range("P4")) Is Nothing Then
        On Error GoTo safe_exit
        Application.EnableEvents = False
        If Range("P4") = vbNullString Then
            Range("P4") = Application.RandBetween(1, 100)
        End If
    End If
safe_exit:
    Application.EnableEvents = True
End Sub

Upvotes: 3

Related Questions