PDA

View Full Version : VLookup macro and variables



ukdane
01-07-2009, 03:00 AM
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
01-07-2009, 03:04 AM
rederiname = Application.VLookup(Rederinr, Range("B3:C70"), 2)

ukdane
01-07-2009, 03:58 AM
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
01-07-2009, 04:00 AM
Works fine for me as I understand your need.

What do your two statements mean?

ukdane
01-07-2009, 04:10 AM
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
01-07-2009, 04:19 AM
Don't see the problem. Can you post the workbook?

ukdane
01-07-2009, 04:27 AM
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
01-07-2009, 05:07 AM
That may work, but is very inefficient. The other way is better, as would Find be.