Hello,
need some assistance
I keep getting the following Compile Error
"Ambiguous name detected: Worksheet_Change"
here is the code that I run:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, c1 As Range, r As Integer
Dim rng2 As Range, c2 As Range, c As Integer
Dim s1, s2
If Not Application.Intersect(Target, Me.Range("F5,G5")) Is Nothing Then
s1 = Range("F5").Value
s2 = Range("G5").Value
Set rng1 = Range("C9:C12")
Set rng2 = Range("E7:CT7")
On Error GoTo er
For Each c1 In rng1.Cells
If c1 <= s1 And c1.Offset(, 1) >= s1 Then
r = c1.Row
End If
Next c1
For Each c2 In rng2.Cells
If c2 <= s2 And c2.Offset(1) >= s2 Then
c = c2.Column
End If
Next c2
Range("H5").Value = Cells(r, c).Value
End If
Exit Sub
er: MsgBox "Not a valid entry"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, c1 As Range, r As Integer
Dim rng2 As Range, c2 As Range, c As Integer
Dim s1, s2
If Not Application.Intersect(Target, Me.Range("J5,K5")) Is Nothing Then
s1 = Range("J5").Value
s2 = Range("K5").Value
Set rng1 = Range("C13:C16")
Set rng2 = Range("E7:CT7")
On Error GoTo er
For Each c1 In rng1.Cells
If c1 <= s1 And c1.Offset(, 1) >= s1 Then
r = c1.Row
End If
Next c1
For Each c2 In rng2.Cells
If c2 <= s2 And c2.Offset(1) >= s2 Then
c = c2.Column
End If
Next c2
Range("L5").Value = Cells(r, c).Value
End If
Exit Sub
er: MsgBox "Not a valid entry"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, c1 As Range, r As Integer
Dim rng2 As Range, c2 As Range, c As Integer
Dim s1, s2
If Not Application.Intersect(Target, Me.Range("N5,O5")) Is Nothing Then
s1 = Range("N5").Value
s2 = Range("O5").Value
Set rng1 = Range("C17:C20")
Set rng2 = Range("E7:CT7")
On Error GoTo er
For Each c1 In rng1.Cells
If c1 <= s1 And c1.Offset(, 1) >= s1 Then
r = c1.Row
End If
Next c1
For Each c2 In rng2.Cells
If c2 <= s2 And c2.Offset(1) >= s2 Then
c = c2.Column
End If
Next c2
Range("P5").Value = Cells(r, c).Value
End If
Exit Sub
er: MsgBox "Not a valid entry"
End Sub
Bookmarks