excel - Import/append cell from one column (newrawdata) to another column (centrallibrary) if not preexisting - date error with Match Function -

the code below uses match function check if new set of values stored in cells of column c exists within pre-existing range stored in column a.

if so, within msgbox prompt, row corresponding value displayed. otherwise, error n/a handled "if iserror" statement, append value in question next empty row within column a

problem:

this works text , numbers, match function doesn't seem correctly return dates. date value gets appended bottom of column a, regardless if pre-existing.

likely error:

formatting or value of date within match function

to note:

below line set comment, creates error dates when uncommented - used display row data found within column a

'msgbox ("data pre-exists in row " & matchans)

happy provide examples.

option explicit  sub appendnewrecords()   'example used - column a, listed number of values including numbers, dates , text, 'column c contains new raw data matching column , not, 'append new raw data not matching column end of column   'declarations      dim neavrow integer     dim neavrecadr string   'declarations      dim importrange long     dim matchlookup variant     dim matcharray variant     dim matchans variant    'use match function see if record exists within range      importrange = 1 worksheets("sheet2").cells(rows.count, "c").end(xlup).row      matchlookup = cells(importrange, 3)     matcharray = activesheet.range("a:a")      msgbox ("lookupvalue " & matchlookup)      matchans = application.match(matchlookup, matcharray, 0)  'msgbox ("data pre-exists in row " & matchans)  'find address of last empty row in column      neavrow = worksheets("sheet2").cells(rows.count, "a").end(xlup).row     neavrecadr = "a" & neavrow + 1 'next available row appending  if iserror(matchans)     range(neavrecadr) = matchlookup  end if      next importrange  end sub 

you need set range , date lookups perform better raw values using worksheet's match function.

matchlookup = cells(importrange, 3).value2   '<~~ use raw date value lookup set matcharray = activesheet.range("a:a")   '<~~ set range  msgbox ("lookupvalue " & matchlookup)  matchans = application.match(matchlookup, matcharray, 0)