| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup working in one instance, but not another
This code works until I get to the last line and then it fails with
this error message "AutoFill method of Range class Failed", does anyone have a clue why? Dim lastRow9 As Long lastRow9 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row Range("a2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl, 3,FALSE)),"""",VLOOKUP(RC[1],tbl,3,FALSE))" Range("A2").AutoFill Destination:=Range("A2:A" & lastRow9) ' Insert Column for Type Sheets("Working Sheet").Select Columns("C:C").Select Selection.Insert Shift:=xlToRight Selection.NumberFormat = "General" 'Populate Column D with Type Dim lastRow8 As Long lastRow8 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row Range("C2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl, 4,FALSE)),"""",VLOOKUP(RC[1],tbl,4,FALSE))" Range("C2").AutoFill Destination:=Range("C1:C" & lastRow8) <-THIS ROW FAILS IN MACRO |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup working in one instance, but not another
Your trying to autofill C2 into a range of C1 - CLastrow8 and you cant
do that. Pick a range to autofill that does overwrite your source range. Mike On Jan 22, 3:04*pm, S Himmelrich wrote: This code works until I get to the last line and then it fails with this error message "AutoFill method of Range class Failed", does anyone have a clue why? Dim lastRow9 As Long * * lastRow9 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row * * Range("a2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl, 3,FALSE)),"""",VLOOKUP(RC[1],tbl,3,FALSE))" * * Range("A2").AutoFill Destination:=Range("A2:A" & lastRow9) ' Insert Column for Type * * Sheets("Working Sheet").Select * * Columns("C:C").Select * * Selection.Insert Shift:=xlToRight * * Selection.NumberFormat = "General" 'Populate Column D with Type * * Dim lastRow8 As Long * * lastRow8 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row * * Range("C2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl, 4,FALSE)),"""",VLOOKUP(RC[1],tbl,4,FALSE))" * * Range("C2").AutoFill Destination:=Range("C1:C" & lastRow8) <-THIS ROW FAILS IN MACRO |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup working in one instance, but not another
Sorry, Clarification, pick an autofill range that doesnt have your
source data in the middle of the range being autofilled. Check out the difference between your 2 autofill statements Mike On Jan 22, 3:04*pm, S Himmelrich wrote: This code works until I get to the last line and then it fails with this error message "AutoFill method of Range class Failed", does anyone have a clue why? Dim lastRow9 As Long * * lastRow9 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row * * Range("a2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl, 3,FALSE)),"""",VLOOKUP(RC[1],tbl,3,FALSE))" * * Range("A2").AutoFill Destination:=Range("A2:A" & lastRow9) ' Insert Column for Type * * Sheets("Working Sheet").Select * * Columns("C:C").Select * * Selection.Insert Shift:=xlToRight * * Selection.NumberFormat = "General" 'Populate Column D with Type * * Dim lastRow8 As Long * * lastRow8 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row * * Range("C2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl, 4,FALSE)),"""",VLOOKUP(RC[1],tbl,4,FALSE))" * * Range("C2").AutoFill Destination:=Range("C1:C" & lastRow8) <-THIS ROW FAILS IN MACRO |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
vlookup working in one instance, but not another
I have the same problem with him. your answer is not clear for me. Could you
please aswer it in more details? -- Thanks "Mike H" wrote: Sorry, Clarification, pick an autofill range that doesnt have your source data in the middle of the range being autofilled. Check out the difference between your 2 autofill statements Mike On Jan 22, 3:04 pm, S Himmelrich wrote: This code works until I get to the last line and then it fails with this error message "AutoFill method of Range class Failed", does anyone have a clue why? Dim lastRow9 As Long lastRow9 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row Range("a2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl, 3,FALSE)),"""",VLOOKUP(RC[1],tbl,3,FALSE))" Range("A2").AutoFill Destination:=Range("A2:A" & lastRow9) ' Insert Column for Type Sheets("Working Sheet").Select Columns("C:C").Select Selection.Insert Shift:=xlToRight Selection.NumberFormat = "General" 'Populate Column D with Type Dim lastRow8 As Long lastRow8 = Worksheets("Working Sheet").Cells(Rows.Count, "B").End(xlUp).Row Range("C2").Formula = "=IF(ISNA(VLOOKUP(RC[1],tbl, 4,FALSE)),"""",VLOOKUP(RC[1],tbl,4,FALSE))" Range("C2").AutoFill Destination:=Range("C1:C" & lastRow8) <-THIS ROW FAILS IN MACRO |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup, Index, Match every instance. | Excel Discussion (Misc queries) | |||
| VLOOKUP more than one instance | Excel Discussion (Misc queries) | |||
| How do I get one instance of Excel to communicate with another instance? | Excel Programming | |||
| Vlookup adding more than one instance | Excel Discussion (Misc queries) | |||
| Vlookup to find Second INstance | Excel Worksheet Functions | |||
Hybrid Mode
