new product to helper table

[Shay A]

Hello,
in the attached file I have a list of fruits and vegetables and their respective price per Kg. As you can see, there are several products that don't have a price. How can I create a loop to check for every product in column A, if it has matching price in the helper table? If it doesn't, I would like that the code would add them at the bottom of helper table.
For the sake of this practice, have the code generating a random price for all the products that are not in the price list yet.

Many thanks!

 

[AlanSidman]

Try this:

Option Explicit

Sub Veggies()
    Dim lrA As Long, lrG As Long, i As Long
    Dim Res As Variant
    lrA = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To lrA
        lrG = Range("G" & Rows.Count).End(xlUp).Row
        On Error Resume Next
        Err.Clear
        Res = Application.WorksheetFunction.VLookup(Range("A" & i), Range("G2:H" & lrG), 2, False)

        If Err.Number = 0 Then
            Range("B" & i) = Res
        Else
            Range("B" & i) = "xxx"
            Range("G" & lrG + 1) = Range("A" & i)
            Range("H" & lrG + 1) = 9.99
        End If
    Next i

End Sub

 

[Shay A]

Hi,
Thanks for your help but I'm afraid I was unclear.
I need for the loop to go to each cell on column A and to see if it exists on Column F. If it doesn't, so in needs to be added at the bottom of the list in on column F. Could it be made with a nested loop?
See the attached file.

Thanks!

 

[AlanSidman]

I believe that my code does in fact put the product at the bottom of Col G which is what we are searching for in your example spreadsheet. this line of code in my VBA does just that

Range("G" & lrG + 1) = Range("A" & i)

Did you run the code against your sample? I did and it does exactly as you asked in your thread. Does your actual spreadsheet align the same as your example.