How I can find string in excel with vba?

Find in string with Excel and VBA?

• I have a spreadsheet with a few worksheets in it, one being Product Inventory and one being Material Inventory... I am trying to figure out how I can have another worksheet to where I can look up a product code, pull the description out of Product Inventory, and extract materials needed from Material Inventory. I know how to do this in other languages, but I'm extremely limited to just Excel and I don't know how to go about it. Say I have Product Lookup:$B$2 = "ps26k417", which I already have pulling info from Product Inventory which pulls the item description, Garden Gate Indigo/Linen Natural 26x26 KE Fiber Pillow* But now I need to figure out how to pull from Material Inventory.. From that product description, I need to pull two rows out of the Material Inventory sheet.. I need a way to extract info based on the description, in this example I need the row for 417 Garden Gate Indigo/Linen and the row for 554 Linen Natural/S Backed Is it even worth going this in-depth in an Excel macro?

• Answer:

  If each result from the ProductInventory sheet translates into exactly two entries on the MaterialInventory sheet, the simplest option would be to use two VLOOKUPS formulae. If you'd like to go down the VBA route, you'll probably need code like the following: Public Sub SearchForMaterial(materials() As String) Dim N As Long Dim materialsList As Range Set materialsList = ' Set to the relevant range Dim cell As Range Dim output(UBound(materials) - LBound(materials)) As String Dim outputCount As Long For N = LBound(materials) To UBound(materials) For Each cell In materialsList.Cells If cell.Value = materialsList(N) Then output(outputCount) = cell.Offset(ColumnOffset:= ??) End If Next cell Next N End Sub This code isn't particularly efficient. You say that you have experience in other languages, so hopefully this should be enough to get you started.