Insert Vlookup into auto filter #N/A cells

[excel_2009]

Hi Excel gurus,

I have the following issue and was wondering if someone could help me. I have columns of data but there are some #N/A's in column C and D, if there is an NA in column C then there will always be an NA in column D. I would like to place a vlookup for all NA cells in column D and the value that is looked up to be populated into column C.

I have the following so far but it partially works because the data always changes so it needs to be updated so that it is more dynamic:

   Range("c2").Select   Selection.AutoFilter
   ActiveSheet.Range("C:C").AutoFilter Field:=3, Criteria1:="#N/A"
   Range("C26").Select
   ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],'Categories'!C[-2]:C[-1],2,0)"
   Selection.FillDown
   Range("C2").Select
   Selection.AutoFilter

I hope this makes sense?

Thank you!

 

[excel_2009]

anyone?