Nested Find problem
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 |
Nested Find problem
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 |
All times are GMT +1. The time now is 09:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com