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

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

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

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