vlookup in vba

[maciej.grzywna]

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

 

[carlo]

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

 

[maciej.grzywna]

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.

 

[Bob Phillips]

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

--
HTH

Bob

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

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.

 

[maciej.grzywna]

Thank you very much Bob!

Maciek

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

--
HTH

Bob

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


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.






- Pokaż cytowany tekst -

 

[Dave Neath]

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