vba to replace Vlookup

[cizzett]

OK Guys so I have been using this formula and it works perfectly, the issue im running into is that the column is a date which can be put in in several ways and I need to see it exactly as it is entered, so I am changing the column to "Text Format" but that means my formula will no longer work

Can someone guide me on the best way to VBA code this to function similarly?

It can be run with another macro so it wont need to be a change event or anything like that.

=IFERROR(VLOOKUP($A3, PD_Table,18,FALSE),"")

Thanks in advance for any help.

 

[cizzett]

I should also mention the two sheets are in the same workbook and both ranges are tables.

Today sheet with TodayTable on it and Prev Day Sheet with PD_Table on it

Both are identical layouts and column a is named Key and has the unique identifier while columns 17 and 18 I want to return the values from PD_Table to TodayTable if Key matches both tables

 

[cizzett]

So this is what ive come up with playing with web finds, I think this is on the right direction but so far it is overwriting my title of the table column which my table begins on row 2 and the databody begins on row 3. I do not want to overwrite my column header and it is also not returning the dates from column 17 but instead the results of column A are being pasted into column 17.

I really hope you can help me find the right direction.

Thanks in advance.

Sub LookDT()


Dim WS As Worksheet: Set WS = Sheets("Today")
Dim Ps As Worksheet: Set Ps = Sheets("Prev Day")
Dim lastrow, lastrow2, i As Long
Dim Searchfor, j, inarr, searcharr, outarr As Variant


lastrow = WS.Cells(Rows.Count, "A").End(xlUp).Row
lastrow2 = Ps.Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))


' load variant array with sercha variables
searcharr = Range(Cells(1, 1), Cells(lastrow, 26))


' define an output aray
outarr = Range(Cells(1, 1), Cells(lastrow, 1))
On Error Resume Next


For i = 3 To lastrow2
 For j = 3 To lastrow
 Searchfor = searcharr(i, 1)
 If inarr(j, 1) = Searchfor Then
  outarr(i, 17) = inarr(j, 17)
  Exit For
 End If
 Next j
Next i
' writeout the output array
Range(Cells(1, 17), Cells(lastrow, 17)) = outarr


End Sub