VBA Array Lookup

[gehusi]

I have two cells (say B3 and D3) on Sheet1 which I would like to concatenate. On Sheet3 are columns (say A and B) which hold the values of previous entries of those two cells. I would like a macro which checks if the current concatenated value exists in those columns. If it finds a match, it should prompt the user to confirm an overwrite of the data in the matched row.

I hope that makes sense. Thanks in advance!

 

[JLGWhiz]

I have two cells (say B3 and D3) on Sheet1 which I would like to concatenate. On Sheet3 are columns (say A and B) which hold the values of previous entries of those two cells. I would like a macro which checks if the current concatenated value exists in those columns. If it finds a match, it should prompt the user to confirm an overwrite of the data in the matched row.

I hope that makes sense. Thanks in advance!

Give this a shot:

Code:
Sub checkRes()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh2.Range("A2:A" & lr)
For i = 2 To lr
If sh1.Range("B3").Value & sh1.Range("D3").Value = sh2.Cells(i, 1).Value & sh2.Cells(i, 2).Value Then
MsgBox "CHECK FOR OVERWRITE AT SHEET2, RANGE(" & Range(sh2.Cells(i, 1), sh2.Cells(i, 2)).Address & ")"
End If
Next
End Sub
Code:

 

[gehusi]

Thanks JLG - I was able to accomplish what I needed to with your help.

 

[JLGWhiz]

Thanks JLG - I was able to accomplish what I needed to with your help.

Your're welcome
Regards, JLG