-3

Hi Can I ask for a sample macro code to compare 2 different columns from 2 different sheets.

Here's the columnA in sheet1

enter image description here

Here's the column A in sheet2 enter image description here

Here's what I need to make as an output in sheet1

enter image description here

Then all cells in column A sheet1 without match such as red in the picture above should be cut and copied in column C in sheet1 like the below enter image description here

lastly all cells in column A sheet 2 that has no match should be cut as well and pasted in column D in sheet 1 such as ABC:PINK, ABC:VIOLET and ABC:BLACK as shown below

enter image description here

Thanks for the help in advance.

Here's what I got so far

Sub Button1_Click()
On Error GoTo ErrorHndler:
Dim myRange As Range
Dim sRng As Range

Set myRange = Range("A1:A50")

Start:
     For Each sRng In myRange
       If sRng Like Sheets("Sheet2").Range("A1").Value Then
          MsgBox (Sheets("Sheet2").Range("A1").Value) <----it does not pass here 
          (----I have no Idea what to put here-----)
          'GoTo NextCell
       Else
          'GoTo Start
          MsgBox (Sheets("Sheet2").Range("A1").Value)
          'MsgBox "Doesn't match"  <-----for debugging purposes
       End If
 NextCell:
 Next sRng

 ErrorHandler:
 MsgBox ""
 End Sub
pnuts
  • 58,317
  • 11
  • 87
  • 139
user3465736
  • 51
  • 1
  • 1
  • 8

1 Answers1

4

You can search a range for a value using Range.Find

Range.Find returns Nothing if no match is found or a Range if a match is found.

You can check if two objects refer to the same object using the is operator.

Here is an example:

Sub lookup()
    Dim TotalRows As Long
    Dim rng As Range
    Dim i As Long

    'Copy lookup values from sheet1 to sheet3
    Sheets("Sheet1").Select
    TotalRows = ActiveSheet.UsedRange.Rows.Count
    Range("A1:A" & TotalRows).Copy Destination:=Sheets("Sheet3").Range("A1")

    'Go to the destination sheet
    Sheets("Sheet3").Select

    For i = 1 To TotalRows
        'Search for the value on sheet2
        Set rng = Sheets("Sheet2").UsedRange.Find(Cells(i, 1).Value)
        'If it is found put its value on the destination sheet
        If Not rng Is Nothing Then
            Cells(i, 2).Value = rng.Value
        End If
    Next
End Sub
Ripster
  • 3,545
  • 2
  • 19
  • 28
  • You are a life saver sir. +1 for my compilation of codes. I'm trying to compile codes so that I can study them. I'm on my I think 4th week on learning VBA. Much appreciated, Thank you. – user3465736 May 09 '14 at 20:47
  • Hi Sir Ripter. Now here's my second problem, I cannot post anymore since someone voted down my question because they don't understand it but the fact that you did understand it makes me think they should not be here. :( Anyways back to the question, What if I want to copy whats in cell column A if Cell columns B is empty. I want to copy the cell value if the adjacent cell is empty. Many thanks and Hope to hear from you sir. Thanks much much much more. – user3465736 May 09 '14 at 22:54
  • Hi Sir,How about the ABC:pink,violet and black how will I copy those data to column D since it has no match. – user3465736 May 12 '14 at 16:44
  • Don't ask new questions in comments. If you are stuck on something open a new question – Ripster May 12 '14 at 19:16
  • I can no longer post a new question since my reputation is so low since some of the viewers tend to just vote down the post without even asking for additional info unlike you. – user3465736 May 14 '14 at 21:35
  • @Ripster +1. Basic stuff and much needed! Helped me a lot! – gadi Dec 16 '16 at 09:44