VLOOKUP Not Updating when Data Repeated

[Ryanchris]

Hello!
I have a quote tool that uses VLOOKUP formulas. I use userforms to gather data and upload it to an excel sheet. I have noticed that the formula works the first time I input a product. However, if I enter the same product again, the formula seems to only recognize the first instance even though the second instance shows up within the VLOOKUP range. Is there an update function or something that is needed to find these second and third instances of the same product?

Thank you so much for your help!!

[Paul_Hossler]

I think that the way VLookup works -- only finds the first

You can try .Find or .Filter if you want them all


Something like this

Option Explicit


Sub drv()
    Dim r As Range


    Set r = FilteredData(ActiveSheet.Cells(1, 1).CurrentRegion, 1, "AAA")
    If Not r Is Nothing Then r.Interior.Color = vbRed


    Set r = FilteredData(ActiveSheet.Cells(1, 1).CurrentRegion, 1, "ddd")
    If Not r Is Nothing Then r.Interior.Color = vbGreen


End Sub


Function FilteredData(r As Range, c As Long, v As Variant) As Range


    On Error GoTo NiceExit


    With r
        If .Parent.FilterMode Then .AutoFilter
        
        .AutoFilter
        .AutoFilter Field:=c, Criteria1:=v
            
        'hide header row
        .Rows(1).EntireRow.Hidden = True
        Set FilteredData = r.SpecialCells(xlCellTypeVisible)
        .Rows(1).EntireRow.Hidden = False
    
        .AutoFilter
    End With


    Exit Function


NiceExit:
    Set FilteredData = Nothing


End Function

[macropod]

For a formula-based solution, see: http://www.techsupportforum.com/foru...ml#post2567119

[Ryanchris]

Thank you for both responses. I was actually able to take something from both and solve a different issues I was having! However, in this case, I need to clarify what I am doing. See example below:

Sheet 1
A B
TLC-03 10
TLC-03 15
TLC-03 20

Sheet 2, Cell A2 needs to calculate the number of TLC-03 parts are ordered. In this case the total is 45. With VLOOKUP, my current cell A2 only calculates 10.

I hope that helps clarify and so sorry that my first question wasn't so clear.

Thank you in advance!!

[Ryanchris]

NOticed the data didn't transfer over well...
Column A is the part, column B is the qty.

[大灰狼1976]

I can only guess without the attachment.
I think it's better for you to use SUMIF here.

--Okami

[Ryanchris]

I will try a few sumif functions and see if I can get that to work. Thank you!

[macropod]

For example:
=SUMIF(Sheet1!A:A,"TLC-03",Sheet1!B:B)

[Ryanchris]

SUMIF was the way to go!

Thanks all!!