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
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
 
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
 
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: 770
Back
Top