Solved: Vlookup in VBA - problems

[phzietsman]

I received an set of reference numbers who paid their premiums each month, unfortunately if wasn't in a list by an array. I wrote a function that checks if a certain reference number appears in this array, and everything worked great until I tried the formula in another sheet, I've copied the code below! Please help!!!
Function PREMIUMFINDER(VALUE)
Dim TELLER As Integer
TELLER = 14
Do Until TELLER = 260
PREMIUMFINDER = Application.VLookup(VALUE, _
Sheets("PREMIUM RECON").Range(Cells(4, TELLER), Cells(477, 26)), 1, _
False)
If IsError(PREMIUMFINDER) Then
TELLER = TELLER + 1
Else
Exit Do
End If
Loop
End Function .

[Oorang]

Hello Phzietsman,
Welcome to the board. If you are using Excel 2003 you need to exit your loop at a little sooner.Do Until TELLER = 257If you are using 2007, can you post the error message you are receiving and, if possible, the line of code that the error occurs on?

[phzietsman]

Thank Oorang, but I still have the problem that the function doesn't want to work in other sheets than the one containing the array.

[mdmackillop]

You need to Qualify the the Cells, not the Range

Function PREMIUMFINDER(VALUE)
Dim TELLER As Integer
TELLER = 14
With Sheets("PREMIUM RECON")
Do Until TELLER = 257
PREMIUMFINDER = Application.VLookup(VALUE, _
Range(.Cells(4, TELLER), .Cells(477, 26)), 1, _
False)
If IsError(PREMIUMFINDER) Then
TELLER = TELLER + 1
Else
Exit Do
End If
Loop
End With
End Function

[Oorang]

lol I had thought that was intentional:)

[phzietsman]

Hi you guys,

Thanks a lot, I'd be lost without you!!!

Excuse the stupid questions (I'm new to this game), what did you mean with

"You need to Qualify the the Cells, not the Range"

Cheers
Paul

[mdmackillop]

Instead of

Sheets("PREMIUM RECON").Range(Cells(4, TELLER), Cells(477, 26))
You need

Range(Sheets("PREMIUM RECON").Cells(4, TELLER), Sheets("PREMIUM RECON").Cells(477, 26))

or using With to avoid repetition


With Sheets("PREMIUM RECON")
Range(.Cells(4, TELLER), .Cells(477, 26))
End With

[Oorang]

To expand on that... Excel uses "implicit" values for the Worksheet and Workbook part of the reference. You can specify the whole reference: Excel.Workbooks("Book1.xls").Worksheet("Sheet1").Range("A1") But you don't have to. If you don't the Excel assumes the omitted part is Active Workbook and then ActiveWorksheet in turn. So when you do Range("A1") = "Foo"It's interpreted as:Excel.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Foo" Why the .Value on the end. Value is the Default Method of the Range Class. More on that later:)

But the Range("A1") = "Foo" syntax is just fine, as long as you know what it means, and it means what you want:D

[phzietsman]

Thanks!