Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Vlookup for VBA question
I have some code that a user has been running which plugs a vlookup formula
into the spreadsheet: 'Do While IsEmpty(Cells(I + 1, 1)) = False ' I = I + 1 ' Cells(I + 1, 11).Select ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],price,4,FALSE)" ' ActiveCell.Offset(1, 0).Select Loop This takes too long to run.....so I want to use code to perform the lookup and plug the result into the active cell. This code is running through several thousand rows...more than 2,000 and less than 10K, but the number of rows varies from week to week. My question: Is there a faster way to preform the lookup and plug the reult into the active cell other than using the application function method: Application.WorksheetFunction.Vlookup() Is this the best method to perform a vlookup? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Vlookup for VBA question
You dont need to select a cell to assign a formula. Try the below two macros
one assigning the formula and the other using worksheetfunction; and compare the time taken.... Sub Macro1() Do While IsEmpty(Cells(I + 1, 1)) = False I = I + 1 Cells(I + 1, 11) = "=VLOOKUP(C" & I & ",price,4,0)" Loop End Sub Sub Macro2() Application.ScreenUpdating = False Do While IsEmpty(Cells(I + 1, 1)) = False I = I + 1 Cells(I + 1, 11) = WorksheetFunction.VLookup _ (Range("C" & I), Range("price"), 4, 0) Loop Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "petedacook" wrote: I have some code that a user has been running which plugs a vlookup formula into the spreadsheet: 'Do While IsEmpty(Cells(I + 1, 1)) = False ' I = I + 1 ' Cells(I + 1, 11).Select ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],price,4,FALSE)" ' ActiveCell.Offset(1, 0).Select Loop This takes too long to run.....so I want to use code to perform the lookup and plug the result into the active cell. This code is running through several thousand rows...more than 2,000 and less than 10K, but the number of rows varies from week to week. My question: Is there a faster way to preform the lookup and plug the reult into the active cell other than using the application function method: Application.WorksheetFunction.Vlookup() Is this the best method to perform a vlookup? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Vlookup for VBA question
Instead of looping through and creating the formual in each cell, try
creating the formula in the first cell and then copy it to the remaining cells. Note the following. I have use I + 1 as your row number but as I said above there is no need to loop and increment I so you can replace I + 1 with the first required row number. A space and underscore at the end of a line is a line break in an otherwise single line of code. Copy and Paste is a one line operation in the following code. The code assumes that it is for the Active worksheet. Dim lngLastRow As Long 'Following line finds last row number with data in 'first column and assigns it to a variable. lngLastRow = Cells(i + 1, 1).End(xlDown).Row 'Enter formula in the first required cell only. Cells(i + 1, 11).FormulaR1C1 = _ "=VLOOKUP(RC[-3],price,4,FALSE)" Cells(i + 1, 11).Copy _ Destination:=Range(Cells(i + 1, 11), _ Cells(lngLastRow, 11)) -- Regards, OssieMac "Jacob Skaria" wrote: You dont need to select a cell to assign a formula. Try the below two macros one assigning the formula and the other using worksheetfunction; and compare the time taken.... Sub Macro1() Do While IsEmpty(Cells(I + 1, 1)) = False I = I + 1 Cells(I + 1, 11) = "=VLOOKUP(C" & I & ",price,4,0)" Loop End Sub Sub Macro2() Application.ScreenUpdating = False Do While IsEmpty(Cells(I + 1, 1)) = False I = I + 1 Cells(I + 1, 11) = WorksheetFunction.VLookup _ (Range("C" & I), Range("price"), 4, 0) Loop Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "petedacook" wrote: I have some code that a user has been running which plugs a vlookup formula into the spreadsheet: 'Do While IsEmpty(Cells(I + 1, 1)) = False ' I = I + 1 ' Cells(I + 1, 11).Select ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],price,4,FALSE)" ' ActiveCell.Offset(1, 0).Select Loop This takes too long to run.....so I want to use code to perform the lookup and plug the result into the active cell. This code is running through several thousand rows...more than 2,000 and less than 10K, but the number of rows varies from week to week. My question: Is there a faster way to preform the lookup and plug the reult into the active cell other than using the application function method: Application.WorksheetFunction.Vlookup() Is this the best method to perform a vlookup? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Vlookup for VBA question
Hi OssieMac
I just tried to work on the OP's code..Actually you dont need to copy. The below will do. lngRow = Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(1, 11), Cells(lngRow, 11)).Formula = "=VLOOKUP(C1,price,4,0)" If this post helps click Yes --------------- Jacob Skaria "OssieMac" wrote: Instead of looping through and creating the formual in each cell, try creating the formula in the first cell and then copy it to the remaining cells. Note the following. I have use I + 1 as your row number but as I said above there is no need to loop and increment I so you can replace I + 1 with the first required row number. A space and underscore at the end of a line is a line break in an otherwise single line of code. Copy and Paste is a one line operation in the following code. The code assumes that it is for the Active worksheet. Dim lngLastRow As Long 'Following line finds last row number with data in 'first column and assigns it to a variable. lngLastRow = Cells(i + 1, 1).End(xlDown).Row 'Enter formula in the first required cell only. Cells(i + 1, 11).FormulaR1C1 = _ "=VLOOKUP(RC[-3],price,4,FALSE)" Cells(i + 1, 11).Copy _ Destination:=Range(Cells(i + 1, 11), _ Cells(lngLastRow, 11)) -- Regards, OssieMac "Jacob Skaria" wrote: You dont need to select a cell to assign a formula. Try the below two macros one assigning the formula and the other using worksheetfunction; and compare the time taken.... Sub Macro1() Do While IsEmpty(Cells(I + 1, 1)) = False I = I + 1 Cells(I + 1, 11) = "=VLOOKUP(C" & I & ",price,4,0)" Loop End Sub Sub Macro2() Application.ScreenUpdating = False Do While IsEmpty(Cells(I + 1, 1)) = False I = I + 1 Cells(I + 1, 11) = WorksheetFunction.VLookup _ (Range("C" & I), Range("price"), 4, 0) Loop Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "petedacook" wrote: I have some code that a user has been running which plugs a vlookup formula into the spreadsheet: 'Do While IsEmpty(Cells(I + 1, 1)) = False ' I = I + 1 ' Cells(I + 1, 11).Select ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],price,4,FALSE)" ' ActiveCell.Offset(1, 0).Select Loop This takes too long to run.....so I want to use code to perform the lookup and plug the result into the active cell. This code is running through several thousand rows...more than 2,000 and less than 10K, but the number of rows varies from week to week. My question: Is there a faster way to preform the lookup and plug the reult into the active cell other than using the application function method: Application.WorksheetFunction.Vlookup() Is this the best method to perform a vlookup? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Vlookup for VBA question
Another question relat4ed to this topic. I need to skip any blank cells. I am using the same method to paste a multiplication formula in another column. This is what I have: Cells(2, 15).FormulaR1C1 = "=RC[-2] * RC[-1]" Cells(2, 15).Copy Destination:=Range(Cells(3, 15), _ Cells(lnglastrow, 15)) How can I make it skip the blank cells? "petedacook" wrote: I have some code that a user has been running which plugs a vlookup formula into the spreadsheet: 'Do While IsEmpty(Cells(I + 1, 1)) = False ' I = I + 1 ' Cells(I + 1, 11).Select ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],price,4,FALSE)" ' ActiveCell.Offset(1, 0).Select Loop This takes too long to run.....so I want to use code to perform the lookup and plug the result into the active cell. This code is running through several thousand rows...more than 2,000 and less than 10K, but the number of rows varies from week to week. My question: Is there a faster way to preform the lookup and plug the reult into the active cell other than using the application function method: Application.WorksheetFunction.Vlookup() Is this the best method to perform a vlookup? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Question | Excel Worksheet Functions | |||
Vlookup Question | Excel Programming | |||
vlookup question | Excel Discussion (Misc queries) | |||
VLOOKUP question | Excel Discussion (Misc queries) | |||
VLookup Question | Excel Worksheet Functions |