Solved: Lookup in VBA creating Errors

[rrenis]

Hi all - I have the following code which populates a userform (which is in Outlook) from a the data stored in an excel spreadsheet. The sheet numbers for the lookup are assigned by an Integer based upon a user's selection elsewhere on the userform (ComboBox1)...

Dim objXLApp As Excel.Application
Dim objXLwb As Excel.Workbook
Set objXLApp = New Excel.Application

ClearTextBoxes ' This Sub clears Textboxes 1 to 7 using .text = ""

With objXLApp
Set objXLwb = .Workbooks.Open("C:\Support\Database.xls", ReadOnly:=True)
TextBox6.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("B1:B300"))
TextBox1.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("C1:C300"))
TextBox3.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("D1:D300"))
TextBox5.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("E1:E300"))
TextBox4.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("F1:F300"))
TextBox2.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("G1:G300"))
TextBox7.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("H1:H300"))
End With

objXLwb.Close False
objXLApp.Quit

Set objXLwb = Nothing
Set objXLApp = Nothing

The problem is that sometimes this works fine. Other times it errors on the first lookup (B1:B300). Not sure if this should make a difference or not but the rows aren't populated down to row 300 - this is just to save me altering the code each time a new row is added. The database.xls is sorted on exit so the data is always alphabetical on every sheets, so I don't think that's causing the problem.

Can anyone see anything that's wrong with the code (it's strange as sometimes it works fine :think: ) or possibly suggest a better way of doing this.

Cheers,
rrenis.

[Bob Phillips]

Try fully qualifying



TextBox6.Value = objXLApp.Lookup(ComboBox1.Value, objXLwb.Worksheets(SheetNumber).Range("A1:A300"), objXLwb.Worksheets(SheetNumber).Range("B1:B300"))

[rrenis]

Hi xld :hi:

Thanks for the reply. :bow: I'll amend the code now - I'll just give it an hour or so for testing until I mark the thread as solved.

Cheers,
rrenis.

[rrenis]

Thanks again xld - it's been working fine!! :cloud9:

Cheers,
rrenis