0

How can I create a VBA code that will match 2 reference numbers in different columns and return data in third column.

The reference numbers are located in column A (Sheet1) and column A (Sheet2) If a match is found, then the Dept. in Sheet 1, Column C, will be copied into an empty column in Sheet 2 Column B.

The code that I have written so far matches data in both columns but only for specific words.

Sheet 1

Sheet 2

Private Sub CommandButton1_Click()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If Worksheets("Sheet1").Cells(i, 3).Value = "North" Then

Worksheets("Sheet1").Rows(i).Copy
Worksheets("sheet2").Activate
b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("sheet2").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet1").Activate

End If
Next
 Application.CutCopyMode = False
 ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

 End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Abdi
  • 1
  • 1

1 Answers1

1

You don't need VBA for this: Juse use a VLOOKUP

See formula used in Column H

enter image description here

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • If you absolutely need VBA you can just recreate the `VLOOKUP` formula there (which is well documented on this site) or use `Range.Find` with `Offset` to get the value (which is also well documented on this site) – urdearboy Jun 24 '20 at 22:06
  • Thanks Undearboy, could you direct me to the VBA codes that are on here which document this problem as I need to use VBA codes? – Abdi Jun 24 '20 at 22:52
  • https://stackoverflow.com/questions/5567513/writing-a-vlookup-function-in-vba – urdearboy Jun 24 '20 at 23:31