macro to check if a value exist in sheet and add it +other parts of row if it doesnt

retrogun

New member
Joined
Aug 30, 2011
Messages
1
Reaction score
0
Points
0
Hi,

I need help from you experts on this problem I'm having:

I need a macro to go through the values in column A in the "reference" tab and check if the value already exist in column B in "master" sheet. If it does, then copy respective value from column C and paste it to column F in the "master" sheet. If it doesn't exist then add column A and B values to the end of the table in "master" sheet and add column C value to "master" column F. Go through all the values in column A in the "reference" tab.

Thanks in advance for your help. I've attached another sample workbook for this.

Please help :confused2:
 

Attachments

  • Sample.xlsm
    13.1 KB · Views: 524

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Code:
Public Sub CheckData()
Dim Lastrow As Long
Dim Newrow As Long
Dim i As Long
Dim sh As Worksheet

    Application.ScreenUpdating = False
    
    Set sh = Worksheets("Master")
    
    With Worksheets("Reference")
    
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 4 To Lastrow
        
            If IsError(Application.Match(.Cells(i, "A").Value, sh.Columns("B"), 0)) Then
            
                Newrow = sh.Range("B3").End(xlDown).Row + 1
                .Cells(i, "A").Copy sh.Cells(Newrow, "B")
                .Cells(i, "B").Copy sh.Cells(Newrow, "C")
                .Cells(i, "C").Copy sh.Cells(Newrow, "E")
            End If
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub
 

Josling

New member
Joined
Nov 18, 2013
Messages
1
Reaction score
0
Points
0
Hi
I have used this macro in a spreadsheet but it creates an additional row between the existing data and the new data. How do I stop this please?
thank you
 

carlliebenberg

New member
Joined
Jun 22, 2014
Messages
1
Reaction score
0
Points
0
Macro to check if avalue exists in named range and add it if not

Hi

Bob Phillips provided vba code which compared two tabs in workbook where when compared the items not on the Reference tab were added to the Master tab. I am using ranges in the attached example and cannot modify Bob's code to incorporate the named ranges as well - In the attachment PO tab is equivalent to Reference tab and Master tab equivalent to Items tab. If and item is entered onto the PO (Purchase Order) which is not in the Items tab add it and all other data for that row into Items tab.
 

Attachments

  • Bob Phillips code with named ranges.xlsm
    33.8 KB · Views: 768
Top