VLookup macro and variables

[ukdane]

So, my problem today is that I need to use the VLoopup function (or equivalent) to look up a variable in one worksheet, and return the result as another variable.

[VBA]Dim rederinr as string 'this is the variable I want to look up
Dim rederiname as string ' this is the result of the look up
'For the active sheet
rederiname = .Forumla = "=VLookup(Rederinr, B3:C70, 2)"[VBA]

This code doesn't work, what am I doing wrong, what is the correct code?

[Bob Phillips]

rederiname = Application.VLookup(Rederinr, Range("B3:C70"), 2)

[ukdane]

I'm getting a Runtime Error 13: Type Mismatch.

The look-up column is a text field.

The look-up variable is a string.


(Excel 2000 / 2003)

[Bob Phillips]

Works fine for me as I understand your need.

What do your two statements mean?

[ukdane]

Dim Rederinr as string
Dim rederiname as string

'Find rederi
ThisWorkbook.Sheets("Datalist").Visible = True
ThisWorkbook.Sheets("Datalist").Select
rederiname = Application.VLookup(Rederinr, Range("B3:C70"), 2)
ThisWorkbook.Sheets("Datalist").Visible = False


Rederinr is a string taken from the result of a formula on a field on a worksheet. It is defined as a text field.
The range B3:C70 are also text fields.

I've run the code, step by step, and it stops on the line:
rederiname = Application.VLookup(Rederinr, Range("B3:C70"), 2)
and shows the Type Mismatch (Runtime error 13)

Is there another way of looking for the result, (maybe by using a find function, and returning the RC 0,1 result)?

[Bob Phillips]

Don't see the problem. Can you post the workbook?

[ukdane]

I think I've solved it, by doing this:

With ActiveSheet.Range("B3:C70")
Set c = .find(Rederinr, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
rederiname = c.Offset(0, 1).Value
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

[Bob Phillips]

That may work, but is very inefficient. The other way is better, as would Find be.