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!!!
[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]

[Oorang]

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?

[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
[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]

[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
[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]

[Oorang]

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

[phzietsman]

Thanks!