[MikeR[_2_]]

I have 3 worksheets.
One has records of engineering faults. The second a list of fault
numbers and parts used on a repair. The fault number is repeated on as
lines as parts used. The third sheet a detailed list of all
part-descriptions stores location etc.

The below macro (working on double-click on the fault ref. number)
will access the PartsUsed sheet and extract all lines that match a
fault number and assemble in a list the parts used.
So far, so good…. the problem comes when I try to access the third
"Stores Info" sheet "on the fly" to embellish the "list" with
additional info…. ie. Part description, location etc. I have tried
doing another nested "find" but the whole thing falls apart….
Can you do a "nested find" or should I be going about this another
way? Any help appreciated.

Sub DisplayFaultPartsReplaced()
With Sheets("PartsUsed").Range("B3:B" &
Sheets("PartsUsed").Range("B16384").End(xlUp).Row)
Set Itemcode = .Find(ActiveCell.Value,
lookin:=xlValues)
If Not Itemcode Is Nothing Then
firstAddress = Itemcode.Address
Found = -1
Do
Found = Found + 1
Dim StockUsed(15) As Variant
StockUsed(Found) = Itemcode.Offset(0, 2).Value &
"(off) " & Itemcode.Offset(0, 1).Value & " Used"
Parts = Parts & Chr(10) & StockUsed(Found) &
Chr(10)
Set Itemcode = .FindNext(Itemcode)
Loop While Not Itemcode Is Nothing And Itemcode.Address
< firstAddress
End If
End With
Dim Title As String
If Parts = "" Then Parts = "No Parts Used"
Title = "Stock Used on " & ActiveCell.Text
MsgBox Parts, vbInformation, Title

End Sub

[Tom Ogilvy]

You can probably us application.Vlookup to retrieve the other information

set rng1 = Worksheets("Stores Info").Range("A1").CurrentRegion

res = application.vlookup(itemcode,rng1,2,false)
if not iserror(res) then
msgbox "item code: " & itemcode & vbcrlf & _
res
else
msgbox "item code: " & itemcode & " not found"
End if


This assumes the item to be looked up is found in column A and you data
table starts in A1.

Regards,
Tom Ogilvy


MikeR wrote in message
om...
I have 3 worksheets.
One has records of engineering faults. The second a list of fault
numbers and parts used on a repair. The fault number is repeated on as
lines as parts used. The third sheet a detailed list of all
part-descriptions stores location etc.

The below macro (working on double-click on the fault ref. number)
will access the PartsUsed sheet and extract all lines that match a
fault number and assemble in a list the parts used.
So far, so good.. the problem comes when I try to access the third
"Stores Info" sheet "on the fly" to embellish the "list" with
additional info.. ie. Part description, location etc. I have tried
doing another nested "find" but the whole thing falls apart..
Can you do a "nested find" or should I be going about this another
way? Any help appreciated.

Sub DisplayFaultPartsReplaced()
With Sheets("PartsUsed").Range("B3:B" &
Sheets("PartsUsed").Range("B16384").End(xlUp).Row)
Set Itemcode = .Find(ActiveCell.Value,
lookin:=xlValues)
If Not Itemcode Is Nothing Then
firstAddress = Itemcode.Address
Found = -1
Do
Found = Found + 1
Dim StockUsed(15) As Variant
StockUsed(Found) = Itemcode.Offset(0, 2).Value &
"(off) " & Itemcode.Offset(0, 1).Value & " Used"
Parts = Parts & Chr(10) & StockUsed(Found) &
Chr(10)
Set Itemcode = .FindNext(Itemcode)
Loop While Not Itemcode Is Nothing And Itemcode.Address
< firstAddress
End If
End With
Dim Title As String
If Parts = "" Then Parts = "No Parts Used"
Title = "Stock Used on " & ActiveCell.Text
MsgBox Parts, vbInformation, Title

End Sub