vlookup in vba

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maciej.grzywna@gmail.com - 14 Dec 2007 08:44 GMT

Hi,

I would like to write a macro with vlookup function in vba. Without a
macro (and vba) I do something like that

colA, colB
23, =vlookup(A1, myRange, 2, 0)
45, =vlookup(A2, myRange, 2, 0)
73, =vlookup(A3, myRange, 2 ,0)
54, =vlookup(A4, myRange, 2, 0)

i must add that I have about 10000 rows.
But how to use vlookup in vba? My problem concerns the first argument
of vlookup function, I just don't know how to make it change in every
row.

TIA
Maciek

Reply to this Message

carlo - 14 Dec 2007 09:00 GMT

What exactly do you want to achieve?

do you want to use VBA to enter the returnvalue of the vlookup-
function into column B, or do you want to enter the formula into
column B?
It's not obvious in your post, what your final result should look
like.

To use the function you can do:

application.WorksheetFunction.VLookup

hth

Carlo

On Dec 14, 5:44 pm, maciej.grzy...@gmail.com wrote:

> Hi,
>
[quoted text clipped - 14 lines]
> TIA
> Maciek

Reply to this Message

maciej.grzywna@gmail.com - 14 Dec 2007 09:20 GMT

I want the first option, to use VBA to enter the returnvalue of the
vlookup function into column B, and I know the basic syntax of
application.WorksheetFunction.VLookup. What I don't know is how to do
that in 10000 rows. I hope my explanation would help.

> What exactly do you want to achieve?
>
[quoted text clipped - 34 lines]
>
> - Pokaż cytowany tekst -

Reply to this Message

Bob Phillips - 14 Dec 2007 09:50 GMT

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1
.Cells(i, "B").Value = Application.VLookup( _
.Cells(i, "A").Value, Range("myRange"), 2, False)
Next i
End With

End Sub

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> What exactly do you want to achieve?
>
[quoted text clipped - 34 lines]
>
> - Poka¿ cytowany tekst -

Reply to this Message

maciej.grzywna@gmail.com - 14 Dec 2007 10:17 GMT

Thank you very much Bob!

Maciek

> Public Sub ProcessData()
> Dim i As Long
[quoted text clipped - 68 lines]
>
> - Pokaż cytowany tekst -

Reply to this Message

Dave Neath - 14 Dec 2007 16:47 GMT

Hi Maciek,

I think this code may be better:

Sub Vlookup()
Dim lNumberOfRows As Long
lNumberOfRows = 10000 ' or whatever number you wish
Range("B1").Resize(lNumberOfRows, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],
myRange, 2, 0)"
End Sub

Dave

On Dec 14, 10:17 am, maciej.grzy...@gmail.com wrote:

> Thank you very much Bob!
>
[quoted text clipped - 74 lines]
>
> - Show quoted text -

Reply to this Message