Find in string with Excel and VBA?

Question

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?

Patrick Honorez · Accepted Answer · 2010-11-30 06:41:57Z

2

Depending on what else you need to do, I would make a properly structured Access database for that. You need 3 tables: Products, Materials and BOM (Bill of materials).
BOM will have 3 fields: ProductId, MaterialId, Quantity.
With just that, and a simple Parameter Query, you can solve your problem.

answered Nov 30, 2010 at 6:41

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

N. Lucas Over a year ago

I would love to go the Access route, but sadly, I am limited to only Excel. The system this spreadsheet will be on does not have Access. This was my first idea, but yeah.

Chris Spicer · Accepted Answer · 2010-11-30 10:00:17Z

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.

N. Lucas · Accepted Answer · 2010-12-04 03:36:41Z

0

I gave up trying to figure this out with just Excel and VBA. I made a PHP script that converted all the columns I needed in the Products and Materials worksheets, threw all that into a MySQL database, pulled it all out as CSV and made a new spreadsheet.

answered Dec 4, 2010 at 3:36

N. Lucas

4123 silver badges12 bronze badges

Comments

Patrick · Accepted Answer · 2011-01-04 12:42:00Z

0

Does the products table have a unique ID? Does the Materials table have a foreign key related to the products table?

If not...Why not? Would make things a lot easier and faster than searching with text values.

Check out this Link for using SQL within excel. Should be easy if you have Unique IDs.

answered Jan 4, 2011 at 12:42

Patrick

1461 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

Find in string with Excel and VBA?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related