Hi,
I'm still new to marcos and VBA and would appreciate it if someone can lend assistance with my problem. Using Excel 2010.
I recorded a macro to perform a VLookup between 2 different workbooks and then autofill the data across & down in the active workbook. The macro works if I have the same a amount of rows in workbook B everytime I run the macro (in this case, 261 rows of data). However if I run the macro on another workbook with a larger data set than the previous workbook (say 500 rows of data), the autofill function fails after the 261st row has been updated.
Is there a way to edit the autofill function so that it does not require the last row of data to be static? Can it start with as in my example below with cell (C2) and go till the first row in the workbook without data, and if so what would the syntax be?
My marco:
**************************************************************************************************
Sub ComplaintsARCodesMerge3()
'
' ComplaintsARCodesMerge3 Macro
'
Workbooks.Open Filename:= _
"C:\Complaint_Metric_Data\Workbook B.xlsx"
Workbooks.Open Filename:= _
"C:\Complaint_Metric_Data\Wokbook A.xlsx"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(VLOOKUP(RC1,[Workbook B.xlsx]Sheet1!C1:C23,COLUMNS(C1:C[-2]),0)="""","""",VLOOKUP(RC1,[Workbook B.xlsx]Sheet1!C1:C23,COLUMNS(C1:C[-2]),0)),"""")"
Selection.AutoFill Destination:=Range("C2:Y2"), Type:=xlFillDefault
Range("C2:Y2").Select
Good up to here
*************************************************************
This part is where I run into my issue: As you can see I need the data to populate and display to the last row before there is no data without having that last row being hardcoded based on the amount of data that was returned by the VLookUp script (in this case row 261). How do I modify the macro so that the last row is not static and have the autofill populate till the last row with data regardless of how many rows the VlookUp script returns? Do I have to enter VBA code to autofill the selected rows C2:Y2 rather than manually performing the autofill that I recorded? If so, what would the syntax look like?
I just want to be able to populate all the data (fill across & down) that the VLookUp script returned. Obviously the flaw in my current macro is that the data in my workbook will vary depending on depending on how many rows of data are in the file that I’m doing the VLookUp on.
Below is the recorded autofill statement based on the workbook I used having 261 rows of data after the VLookUp script ran. But, what if the next time there is 500 rows of data returned? Currently the macro fails after the 261st row of data is updated.
I can’t seem to figure out what the syntax should be define the end range . The start range will always be C2, but I don't know how to define the end (Y) range since that information will vary based on how many rows of data is returned by the VLookUP.
Selection.AutoFill Destination:=Range("C2:Y261"), Type:=xlFillDefault
Range("C2:Y261").Select
*************************************************************
Than you in advance,
gdoode23
I'm still new to marcos and VBA and would appreciate it if someone can lend assistance with my problem. Using Excel 2010.
I recorded a macro to perform a VLookup between 2 different workbooks and then autofill the data across & down in the active workbook. The macro works if I have the same a amount of rows in workbook B everytime I run the macro (in this case, 261 rows of data). However if I run the macro on another workbook with a larger data set than the previous workbook (say 500 rows of data), the autofill function fails after the 261st row has been updated.
Is there a way to edit the autofill function so that it does not require the last row of data to be static? Can it start with as in my example below with cell (C2) and go till the first row in the workbook without data, and if so what would the syntax be?
My marco:
**************************************************************************************************
Sub ComplaintsARCodesMerge3()
'
' ComplaintsARCodesMerge3 Macro
'
Workbooks.Open Filename:= _
"C:\Complaint_Metric_Data\Workbook B.xlsx"
Workbooks.Open Filename:= _
"C:\Complaint_Metric_Data\Wokbook A.xlsx"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(VLOOKUP(RC1,[Workbook B.xlsx]Sheet1!C1:C23,COLUMNS(C1:C[-2]),0)="""","""",VLOOKUP(RC1,[Workbook B.xlsx]Sheet1!C1:C23,COLUMNS(C1:C[-2]),0)),"""")"
Selection.AutoFill Destination:=Range("C2:Y2"), Type:=xlFillDefault
Range("C2:Y2").Select
Good up to here
*************************************************************
This part is where I run into my issue: As you can see I need the data to populate and display to the last row before there is no data without having that last row being hardcoded based on the amount of data that was returned by the VLookUp script (in this case row 261). How do I modify the macro so that the last row is not static and have the autofill populate till the last row with data regardless of how many rows the VlookUp script returns? Do I have to enter VBA code to autofill the selected rows C2:Y2 rather than manually performing the autofill that I recorded? If so, what would the syntax look like?
I just want to be able to populate all the data (fill across & down) that the VLookUp script returned. Obviously the flaw in my current macro is that the data in my workbook will vary depending on depending on how many rows of data are in the file that I’m doing the VLookUp on.
Below is the recorded autofill statement based on the workbook I used having 261 rows of data after the VLookUp script ran. But, what if the next time there is 500 rows of data returned? Currently the macro fails after the 261st row of data is updated.
I can’t seem to figure out what the syntax should be define the end range . The start range will always be C2, but I don't know how to define the end (Y) range since that information will vary based on how many rows of data is returned by the VLookUP.
Selection.AutoFill Destination:=Range("C2:Y261"), Type:=xlFillDefault
Range("C2:Y261").Select
*************************************************************
Than you in advance,
gdoode23
Last edited: