Results 1 to 4 of 4

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

  1. #1

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

    Register for a FREE account, and/
    or Log in to avoid these ads!


    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
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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

  3. #3
    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

  4. #4

    Macro to check if avalue exists in named range and add it if not


    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.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts