Results 1 to 4 of 4

Thread: Assistance needed in amending a macro.

  1. #1
    Board Regular
    Join Date
    Jul 2005
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Assistance needed in amending a macro.

    Hi

    I had this macro that compared data between sheets 1 and 2 and marked "OK" "MISSING" and "DUPLICATE"

    What im trying to amend it to do is to mark "OK" and "DUPLICATE" on sheet 1 still but when data is on sheet 2 but not on sheet 1 I need it marked "MISSING" on the row concerned on sheet 2.

    Any ideas on how I can do this?


    Sub compare_data()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim i As Long, j As Long, lr As Long

    Set sh1 = ActiveWorkbook.Sheets("Sheet1")
    Set sh2 = ActiveWorkbook.Sheets("Sheet2")

    Application.ScreenUpdating = False
    lr = sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row
    For i = 2 To lr
    Application.StatusBar = "Checking row : " & i & " of : " & lr
    j = Application.CountIfs(sh2.Columns("A"), sh1.Cells(i, "A").Value, sh2.Columns("B"), sh1.Cells(i, "B").Value, sh2.Columns("C"), sh1.Cells(i, "C").Value, _
    sh2.Columns("D"), sh1.Cells(i, "D").Value)
    Select Case j
    Case 0
    sh1.Cells(i, "E").Value = "MISSING"
    Case 1
    sh1.Cells(i, "E").Value = "OK"
    Case Is > 1
    sh1.Cells(i, "E").Value = "DUPLICATE"
    End Select
    Next
    MsgBox "Done"
    End Sub

    Thanks

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,888
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Assistance needed in amending a macro.

    Now you need to look for each data of sheet2 on sheet1.

    Try this:

    Code:
    Sub compare_data()
      Dim sh1 As Worksheet, sh2 As Worksheet
      Dim i As Long, j As Long, lr As Long
      
      Set sh1 = ActiveWorkbook.Sheets("Sheet1")
      Set sh2 = ActiveWorkbook.Sheets("Sheet2")
      
      Application.ScreenUpdating = False
      Application.StatusBar = False
      lr = sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row
      'Search 1 in 2
      For i = 2 To lr
        Application.StatusBar = "Checking row : " & i & " of : " & lr
        j = Application.CountIfs(sh2.Columns("A"), sh1.Cells(i, "A").Value, sh2.Columns("B"), sh1.Cells(i, "B").Value, sh2.Columns("C"), sh1.Cells(i, "C").Value, _
        sh2.Columns("D"), sh1.Cells(i, "D").Value)
        Select Case j
          Case 0
          sh1.Cells(i, "E").Value = "MISSING"
          Case 1
          sh1.Cells(i, "E").Value = "OK"
          Case Is > 1
          sh1.Cells(i, "E").Value = "DUPLICATE"
        End Select
      Next
      '
      'Search 2 in 1
      lr = sh2.Range("A" & sh1.Rows.Count).End(xlUp).Row
      For i = 2 To lr
        Application.StatusBar = "Checking row : " & i & " of : " & lr
        j = Application.CountIfs(sh1.Columns("A"), sh2.Cells(i, "A").Value, sh1.Columns("B"), sh2.Cells(i, "B").Value, _
          sh1.Columns("C"), sh2.Cells(i, "C").Value, sh1.Columns("D"), sh2.Cells(i, "D").Value)
        Select Case j
          Case 0
          sh2.Cells(i, "E").Value = "MISSING"
        End Select
      Next
      MsgBox "Done"
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Jul 2005
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance needed in amending a macro.

    Brill

    Many thanks

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,888
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Assistance needed in amending a macro.

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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