Solved: Vlookup in VBA - problems

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!!!
[vba]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[/vba] [uvba].[/uvba]

Hello Phzietsman,
Welcome to the board. If you are using Excel 2003 you need to exit your loop at a little sooner.[VBA]Do Until TELLER = 257[/VBA]If 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?

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.

You need to Qualify the the Cells, not the Range
[VBA]
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

[/VBA]

lol I had thought that was intentional:)

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

Instead of
[VBA]
Sheets("PREMIUM RECON").Range(Cells(4, TELLER), Cells(477, 26))
[/VBA]You need
[VBA]
Range(Sheets("PREMIUM RECON").Cells(4, TELLER), Sheets("PREMIUM RECON").Cells(477, 26))
[/VBA]
or using With to avoid repetition

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

To expand on that... Excel uses "implicit" values for the Worksheet and Workbook part of the reference. You can specify the whole reference: [vba]Excel.Workbooks("Book1.xls").Worksheet("Sheet1").Range("A1")[/vba] 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 [vba]Range("A1") = "Foo"[/vba]It's interpreted as:[vba]Excel.ActiveWorkbook.ActiveSheet.Range("A1").Value = "Foo"[/vba] 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

Thanks!