MNJ
11-21-2006, 12:47 AM
Dear experts :hi: ,
I got code from a forum ( can't rmb). It worked but I think if i can change the code a bit to fit me.
sorry for poor english.
Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1, 1)
If Not Intersect(.Cells, Range("b3:b8")) Is Nothing Then
Range("b9") = Empty
For i = 3 To 8: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m3", Cells(3, Columns.Count).End(xlToLeft))
For i = 0 To 5: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b9").Value = r.Offset(6).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b16:b22")) Is Nothing Then
Range("b23") = Empty
For i = 16 To 22: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m16", Cells(16, Columns.Count).End(xlToLeft))
For i = 0 To 6: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b23").Value = r.Offset(7).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b30:b33")) Is Nothing Then
Range("b34") = Empty
For i = 30 To 33: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m30", Cells(30, Columns.Count).End(xlToLeft))
For i = 0 To 3: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b34").Value = r.Offset(4).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b41:b44")) Is Nothing Then
Range("b45") = Empty
For i = 41 To 44: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m41", Cells(41, Columns.Count).End(xlToLeft))
For i = 0 To 3: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b45").Value = r.Offset(4).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b53:b57")) Is Nothing Then
Range("b58") = Empty
For i = 53 To 57: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m53", Cells(53, Columns.Count).End(xlToLeft))
For i = 0 To 4: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b58").Value = r.Offset(5).Value
Exit For
End If
txt2 = ""
Next
End If
End With
Application.EnableEvents = True
End Sub
If code unable to find matchign value, i want the cell to show something like ' Not found' . Now, it show blank cell.
thanks all experts.
I got code from a forum ( can't rmb). It worked but I think if i can change the code a bit to fit me.
sorry for poor english.
Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1, 1)
If Not Intersect(.Cells, Range("b3:b8")) Is Nothing Then
Range("b9") = Empty
For i = 3 To 8: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m3", Cells(3, Columns.Count).End(xlToLeft))
For i = 0 To 5: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b9").Value = r.Offset(6).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b16:b22")) Is Nothing Then
Range("b23") = Empty
For i = 16 To 22: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m16", Cells(16, Columns.Count).End(xlToLeft))
For i = 0 To 6: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b23").Value = r.Offset(7).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b30:b33")) Is Nothing Then
Range("b34") = Empty
For i = 30 To 33: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m30", Cells(30, Columns.Count).End(xlToLeft))
For i = 0 To 3: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b34").Value = r.Offset(4).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b41:b44")) Is Nothing Then
Range("b45") = Empty
For i = 41 To 44: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m41", Cells(41, Columns.Count).End(xlToLeft))
For i = 0 To 3: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b45").Value = r.Offset(4).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b53:b57")) Is Nothing Then
Range("b58") = Empty
For i = 53 To 57: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m53", Cells(53, Columns.Count).End(xlToLeft))
For i = 0 To 4: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b58").Value = r.Offset(5).Value
Exit For
End If
txt2 = ""
Next
End If
End With
Application.EnableEvents = True
End Sub
If code unable to find matchign value, i want the cell to show something like ' Not found' . Now, it show blank cell.
thanks all experts.