Searching an entire column for all the values existing in a nother column

I have been looking and browsing the web all day now for a macro that would really help me out. But first some background.

I have 2 worksheets in the same workbook.
The first workbook (ProductDatabase) is like a database, with the product names in column A. The second worksheet (tmpWeb) is imported from the web though a macro and then put into the second worksheet. Also in this worksheet the product names are in column A.

What I need to do is that I want to create a macro that does the following thing:

starts in the second sheet (tmpWeb), in cell A1 and then see if the same value exist in the other worksheet, ProductDatabase, somewhere in the entire column A, not just in the corresponding cell A1. If so, I then want the following value "Registered" to be put in column H in the same row. If the same value does not exist anywhere in column A, then no value needs to be added into column H.

After the macro have checked if cell A1 in tmpWeb exists somewhere in ProductDatabase-sheet in column A, I want the macro to check if cell A2 exist, and then A3, A4 and so on. Until the whole column A in tmpWeb has been checked, i guess the whole range then. (there will not be any rows between any products since the product name always must exist).

If someone could point me in the right direction how I can do this, I would be really happy!

try this, i think i understood what you wanted, did you want the "registered" in the product database set?

any way hope this helps, let me know if it need modifying.

Sub Product_Reg()
Dim Total_Reg, Total_UnReg As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Lastcell As Range
Dim i As Integer
Dim Lookup_value As String


'//these to processes increase speed
Application.ScreenUpdating = False
Application.EnableEvents = False

'//Set Worksheets to required sheet names - rename as required
Set ws1 = Sheets("ProductDatabase")
Set ws2 = Sheets("tmpWeb")

'//finds the last cell so loop is minimal
Set Lastcell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

    For i = 1 To Lastcell.Row
        Lookup_value = ws2.Cells(i, "A")
        '//Starts looking in productdata sheet for part
        If Len(Lookup_value) <> 0 Then
        For x = 1 To ws1.Cells.SpecialCells(xlCellTypeLastCell).Row
        
            If ws1.Cells(x, "A") = Lookup_value Then
            
               ws1.Cells(x, "H") = "Registered"
               
                '// add to total so user can see products have been registered
                Total_Reg = Total_Reg + 1
            End If
        Next
        End If
Next

MsgBox ("Total Products Registered: " & Total_Reg & "")

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

regards,

Jon

Jonn

It works Great! And right of the box too, I did not even have to change one single line!

I would very much like to thank you for the time and effort you put into writing all this, thank you so much.

No worries mate, i like problems like this

If you need a hand with anymore code give us a shout.

Cheers,

Jon

What if the counter that counts the number of "Registered" to put into column H also checks if the product allready have been "Registered".

So that those product with "Registered" in column H do not count when displaying the msgbox. I tried to change the code a bit but not succsessfully.

try this

Sub Product_Reg()
Dim Total_Reg, Total_UnReg As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Lastcell As Range
Dim i As Integer
Dim Lookup_value As String

'//these to processes increase speed
Application.ScreenUpdating = False
Application.EnableEvents = False

'//Set Worksheets to required sheet names - rename as required
Set ws1 = Sheets("ProductDatabase")
Set ws2 = Sheets("tmpWeb")

'//finds the last cell so loop is minimal
Set Lastcell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

    For i = 1 To Lastcell.Row
        Lookup_value = ws2.Cells(i, "A")
        '//Starts looking in productdata sheet for part
        If Len(Lookup_value) <> 0 Then
        For x = 1 To ws1.Cells.SpecialCells(xlCellTypeLastCell).Row
        
            If ws1.Cells(x, "A") = Lookup_value Then
                If Not ws1.Cells(x, "H") = "Registered" Then
                    ws1.Cells(x, "H") = "Registered"
               
                    '// add to total so user can see products have been registered
                    Total_Reg = Total_Reg + 1
                End If
            End If
        Next
        End If
Next

MsgBox ("Total Products Registered: " & Total_Reg & "")
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Jon

That extra code did the trick, now everything is working just as I want it to be.

Thank you Jonn!