Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
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? -- Chomotkar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP!
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 "Mahirul" wrote: 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? -- Chomotkar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|