HELP!

[Mahirul]

Hello,

I am not sure if I am in the right forum. So bare with me. If you know where
this question should be directed, please let me know.

I am in dire need of some codes. I create a call list everyday for my team.
It is taking roughly an hour per day. I have recorded some macro that can do
the initial data manipulation. However when it comes to VLOOKUP and
autofilter it is failing miserabley. I need to be able to do the following.

I need to do mutiple VLOOKUP to come to a point where I do not have any #N/A
cells. After I do the first lookup, I then auto filter all colums. Now when I
try to do the second VLOOKUP my problem stars. The cell I am go to do it that
varies every day. So I need to be able to say go to the first #N/A cell to
start the VLOOKUP. As an example the code now looks like

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],Sheet2!C1:C3,3,0)"
Selection.AutoFill Destination:=Range("B2:B367")
Range("B2:B367").Select
Selection.AutoFilter Field:=2, Criteria1:="#N/A"
Range("B106").Select

The above B106 cells chanegs I need to say this is the first #N/A cell in
the first row.

Can someone help me with this problem?

 

[Joel]

Sub test()

Set startcell = Range("B2")
Set FillCells = Range("B2:B367")
startcell.FormulaR1C1 = "=VLOOKUP(RC[1],Sheet2!C1:C3,3,0)"
startcell.AutoFill Destination:=FillCells
Set c = Columns("B").Find(what:="#N/A", LookIn:=xlValues)
If Not c Is Nothing Then

Set NAcell = c
End If

End Sub