Another Vlookup for VBA question

[petedacook]

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?

 

[Jacob Skaria]

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

 

[OssieMac]

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

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

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?

 

[Jacob Skaria]

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

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

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

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?

 

[petedacook]

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?