PDA

View Full Version : Solved: show term ' not found ' with unmatching terms



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.

Bartek
11-21-2006, 02:05 AM
Hi,


If code unable to find matchign value, i want the cell to show something like ' Not found' . Now, it show blank cell.

It seems that all you need is to replace all lines like:


Range("b9") = Empty

With the code like this:


Range("b9") = "Not found"

You can use VBA editor Find > Replace option (Find What: Empty, Replace With: "Not found")

MNJ
11-21-2006, 02:26 AM
oh thanks. I do not know it was so simple. Thanks!:super: