asked on

Problem with Vlookup in VBA

Hi,
I am trying to use Vlookup in a macro but can't get it to Work.
I have a sheet called "Pivot" and a sheet called "Data2".
I build a new sheet called "Raabalance" with data from both sheets with this code:

Sub Opdaterdata()
Dim i_rowidx1, i_rowidx2, i_rowidx3 As Integer
Dim rngstart As Range
Dim primo As Currency
Dim k_nr As String
'On Error GoTo Fejl

Set rngstart = Worksheets("Pivot").Range("A1")
i_data1_idx = rngstart.CurrentRegion.Rows.Count

On Error Resume Next

i_vareliste_idx = 2
For i = 2 To i_data1_idx

k_nr = Worksheets("Pivot").Cells(i, 1)
primo = 0
' primo = [VLOOKUP(k_nr, Data2!A1:X200, 2, FALSE)]

' primo = Application.VLookup(k_nr, Tabel_Forespørgsel_fra_EGDTA1, 2, 0)
' primo = Worksheet.VLookup(k_nr, Tabel_Forespørgsel_fra_EGDTA1, 2, 0)
' primo = Application.WorksheetFunction.VLookup(k_nr, Tabel_Forespørgsel_fra_EGDTA1, 2, 0)
' primo = Application.Worksheet.VLookup(k_nr, Tabel_Forespørgsel_fra_EGDTA1, 2, 0)
' primo = Application.Worksheet.VLookup(k_nr, Tabel_Forespørgsel_fra_EGDTA1, 2, 0)

'primo = Application.Worksheet.Function.VLookup(k_nr, Tabel_Forespørgsel_fra_EGDTA1, 2, 0)
primo = Application.WorksheetFunction.VLookup(k_nr, "Data2!a1:x400", 2, 0)

'Kontonr
Worksheets("Raabalance").Cells(i_vareliste_idx, 1).Value = Worksheets("Pivot").Cells(i, 1)

'Saldi januar-december
Worksheets("Raabalance").Cells(i_vareliste_idx, 2).Value = Worksheets("Pivot").Cells(i, 2) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 3).Value = Worksheets("Pivot").Cells(i, 3) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 4).Value = Worksheets("Pivot").Cells(i, 4) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 5).Value = Worksheets("Pivot").Cells(i, 5) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 6).Value = Worksheets("Pivot").Cells(i, 6) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 7).Value = Worksheets("Pivot").Cells(i, 7) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 8).Value = Worksheets("Pivot").Cells(i, 8) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 9).Value = Worksheets("Pivot").Cells(i, 9) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 10).Value = Worksheets("Pivot").Cells(i, 10) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 11).Value = Worksheets("Pivot").Cells(i, 11) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 12).Value = Worksheets("Pivot").Cells(i, 12) + primo
Worksheets("Raabalance").Cells(i_vareliste_idx, 13).Value = Worksheets("Pivot").Cells(i, 13) + primo

i_vareliste_idx = i_vareliste_idx + 1

Next i

End Sub

The Vlookup for the value "Primo" is not working.

Cells from sheet "Pivot":
71112 0 0 -514,66 0 0 -514,66 0 0 -514,66 0 0 0

Cells from sheet "Data2"
71112 -2058,64

Please help...

Regards Bente

Saqib Husain

How about a sample file for testing. There might even be a problem with the data.

Saqib Husain

Try

primo = Application.WorksheetFunction.VLookup(k_nr, range("Data2!a1:x400"), 2, 0)

Amandina

ASKER

Hi Saqib,
I tried your first comment, did not Work.
I have created a sample file with just a little data as it is confidental.
Hope you can help.
R-balance-test.xlsm

Saqib Husain

Ok then try

primo = Application.WorksheetFunction.VLookup(Worksheets("Pivot").Cells(i, 1), Range("Data2!a1:x400"), 2, 0)

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

When you are developing code do not use error handlers until the code actually works. If you do then possible error alerts that may help will be stopped.

Instead of VLOOKUP in Excel look at the .Find method in VBA

Amandina

ASKER

Thanks!

Saqib Husain

Does that mean that the second formula I gave did not work?

Amandina

ASKER

No, it did not Work. The problem was the difference between string and numbers. But thanks