code to lookup/match and return all values that meet that criteria

[krunk]

Hi, I am new to vba and I am stuck with trying to figure this out.

I have a worksheet which i will attach, that has a "Table Lookup" tab with a pivot table and data. I also have a lookup tab and a "Cost Analysis" tab.

On the Cost Analysis tab, i want to write a code that basically uses the selects in B1,B2, and B3 to populate the table below using the Table Lookup.

I know I can use a formula to do this but i am really trying to understand vba because I think it will perform the function more quickly that way.

It will not let me attach a file or a picture so i've done my best to describe it.
On the cost analysis tab, i have 3 selection drop downs in b1,b2,b3 for
b1 = Product Selection
b2 = Supplier
b3 = Branch

Below, starting in row 6, I've started my table that i want to populate using the criteria listed above as follows:

based on Product Selection and Supplier selection, populate all the branches that match that criteria with the costs.

My table looks like this:
Column A = Branch Column B = Use the Lookup tab to populate the Region based on Column A Column C = Cost per Item.

I've written some but I don't know what to do next. Hoping someone can help.

 

[cerfani]

It is hard to understand what you are trying to do (why i think you have no replies) but i can try to help... do this...

1. Press Print Screen (you can also use the snip tool... search for in start menu)
2. Open MS Paint
3. Paste and save (crop out anything you dont want to show)
4. go to imgur.com
5. upload the file
6. then come back here and click ...

7. then paste the link, then be very specific (use cell addresses, sheet names, etc dont use pronouns like "i want it...") with what you want to do. Then most likely you will receive an answer

 

[krunk]

Hi Cerfani, Thank you for your response. That was helpful.

The code should return all Branches and Cost associated with the branch that match criteria of (Product/ Item Selection in cell b1 and Supplier in cell b2.

I put in a picture of the data as well so you can see how it is displayed and provided an example of what the code should accomplish.


Thank you for your help.

K

It is hard to understand what you are trying to do (why i think you have no replies) but i can try to help... do this...

1. Press Print Screen (you can also use the snip tool... search for in start menu)
2. Open MS Paint
3. Paste and save (crop out anything you dont want to show)
4. go to imgur.com
5. upload the file
6. then come back here and click ...

7. then paste the link, then be very specific (use cell addresses, sheet names, etc dont use pronouns like "i want it...") with what you want to do. Then most likely you will receive an answer

 

[cerfani]

you are trying to upload the webpage that has the image... you need to paste the link to the image itself...

the link you used was... http://imgur.com/E5knkzm (navigate here then right click image and copy the image address)

you should have used... http://i.imgur.com/E5knkzm.png

well i just navigated to the image address and put here...

 

[krunk]

Oh, thank you.

 

[cerfani]

here is some code to go through the Table Lookup and get all data for a specified branch...

Note:If your pivot has a Grand Total Row at the bottom (this would be in column A) then you need to subtract 1 from .Cells(.Rows.Count, 1).End(xlUp).Row

Sub GetBranches()
    Dim branches As New Collection
    With Sheets("Table Lookup")
        For Row = 9 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If Sheets("Cost Analysis").Range("B1").Value2 = .Cells(Row, 2).Value2 Then
                If Sheets("Cost Analysis").Range("B2").Value2 = .Cells(Row, 3).Value2 Then
                    found = False
                    currentBranch = .Cells(Row, 4).Value2


                    For Each b In branches
                        If b = currentBranch Then
                            found = True
                            Exit For
                        End If
                    Next b


                    If Not found Then
                        branches.Add (currentBranch)
                    End If
                End If
            End If
        Next Row
    End With


    'here you have a collection of all branches matching your criteria
    'now you can write a loop that writes it wherever you want
    For Each b In branches
        MsgBox b
    Next b


End Sub

 

[krunk]

Okay, Here is the Code that I have now.

I wrote a question in the below code to try and find the cost that is associated with the branches that match the criteria. If I were to write a formula I would write a concatenation for Product&Supplier&Branch and return the Cost found in column E.


Sub GetBranches()

Dim result As String
Dim Sheet As Wroksheet

Set a = Sheets("Cost Analysis")
Set b = Sheets("Table Lookup")
Set c = Sheets("Lookup")

a.Range("A7:c1000000").Clear

Dim Branches As New Collection
With Sheets("Table Lookup")
For Row = 9 To .Cells(.Rows.Count, 1).End(xlUp).Row
If a.Range("B1").Value2 = .Cells(Row, 2).Value2 Then
If a.Range("B2").Value2 = .Cells(Row, 3).Value2 Then
found = False
currentBranch = .Cells(Row, 4).Value2

For Each b In Branches
If b = currentBranch Then found = True
Exit For
Next b

If Not found Then
Branches.Add (currentBranch)
End If
End If
End If
End If
Next Row
End With
Loop

'How do I find the associated cost for the branches that it found matching this criteria?


'Perform Vlookup to Find the Region Associated with the Branches found in the above function

ax = ax - 1
a.Cells(ay, 2) = Application.WorksheetFunction.VLookup(Sheet.Range("A6"), c.Sheet.Range("D1:D93"), 1, False)
Do Until a.Cells(ay, 1) = ""

















End Sub

here is some code to go through the Table Lookup and get all data for a specified branch...

Note:If your pivot has a Grand Total Row at the bottom (this would be in column A) then you need to subtract 1 from .Cells(.Rows.Count, 1).End(xlUp).Row

Sub GetBranches()
    Dim branches As New Collection
    With Sheets("Table Lookup")
        For Row = 9 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If Sheets("Cost Analysis").Range("B1").Value2 = .Cells(Row, 2).Value2 Then
                If Sheets("Cost Analysis").Range("B2").Value2 = .Cells(Row, 3).Value2 Then
                    found = False
                    currentBranch = .Cells(Row, 4).Value2


                    For Each b In branches
                        If b = currentBranch Then
                            found = True
                            Exit For
                        End If
                    Next b


                    If Not found Then
                        branches.Add (currentBranch)
                    End If
                End If
            End If
        Next Row
    End With


    'here you have a collection of all branches matching your criteria
    'now you can write a loop that writes it wherever you want
    For Each b In branches
        MsgBox b
    Next b


End Sub

 

[cerfani]

you can get the associated cost inside this IF... If a.Range("B2").Value2 = .Cells(Row, 3).Value2 Then

if that is true then... .Cells(Row, 5).Value2 is your cost for the specified branch in .Cells(Row, 4).Value2

Set a breakpoint on the first line of code and step through keeping track of variable values so you know what is happening

For the vlookup you can do in VBA and write the value or just write the formula and let the worksheet calculate it...

a.Cells(ay, 2).Value2 = Application.WorksheetFunction.VLookup(Sheet.Range("A6"), c.Sheet.Range("D1:D93"), 1, False) ... which sheet is the variable 'Sheet' ... you never set its value and you are trying to set a range object with the value... i added .Value2

a.Cells(ay, 2).Value2 = "=VLOOKUP(SheetName!A6,Lookup!D1:D93,1,False)" ... btw this code will just return whatever it is looking up since you have it return column 1 and your lookup range is actually only 1 column

 

[krunk]

Thank you Cerfani.

Can I ask some follow up questions?

The if Statement to return the cost... Should it be written as follows?

If a.Range("b2").Value2 = .Cells(Row, 3).Value2 Then
If .Cells(Row, 5).Value2 Then .Cells(Row, 4).Value2

I am not sure I am interpreting it correctly? I don't understand how it is making sure that both b2 and b3 and the branch then lookup to find the associated cost in column c row 6.

Sorry. I am knew to this.

And for the vlookup, are you giving me two option for the vlookup so either option above would work?

Thank you again.