Vlookup not working

I have a workbook with multiple worksheets and a user form that has two combo boxes, the first one fills from a named range and when a user selects a value from this combo box it fills another combo box. When a user selects a value from this combo box I need Vlookup to find the value in a named range and column 1 (left) is the same column that fills the combo box so the value that is being searched for has to be identical to the value found but the result always comes back empty?

I'm passing two variables to Vlookup - the search name (from the selected combo box) and an integer for the column offset. The first sub is activated when the user clicks button CmdDetails on the form.

Here is the code

Private Sub CmdDetails_Click()
     
    Dim Name As String
    Dim ColoffSet As Integer
    
       Name = CmbArea.Value
       ColoffSet = 5
           
      Call AreaChange(Name, ColoffSet)
        
   
     
End Sub

[/CODE]

Private Sub AreaChange(Name, ColoffSet)

Dim R As Range
Dim T As Range
Dim Result As Variant
On Error Resume Next
Err.Clear
For Each R In Range("Table")
Result = Application.WorksheetFunction.VLookup(Name, R, ColoffSet, False)
T.AddItem Result

'If Err.Number = 0 Then (I took the error handling out because the vbYesNoCancel button won't cancel and I have to end the program to get out of it using Ctrl Alt Delete, but if I leave it in it says value not found)
'Else
'MsgBox ("result not found"), vbYesNoCancel

'End If
Next R

Call AddRoom(T)

End Sub
[/CODE]

'Forget the call to AddRoom... I don't get that far!

If I toggle a breakpoint on the For each line I can see that the name value is being passed to the function (hovering the mouse over the variables) and when it cycles through the table R (named range) I can see that the value is found but not passed to the result variable which remains empty.

Any ideas?

Please put code tags around your code excerpt.

Can you post a sample workbook?

I'd hazard a guess that this:

For Each R In Range("Table")
Result = Application.WorksheetFunction.VLookup(Name, R, ColoffSet, False)
T.AddItem Result

should be this:

Result = Application.WorksheetFunction.VLookup(Name, Range("Table"), ColoffSet, False)
T.AddItem Result

and lose the Next R loop.


Regards, TMS

Welcome to the forum.

We'd like to help you but first..

Pls take some minutes to read forum rules and specially-in this case- rule#3 and add code tags around your code.
Then anyone will be able to help you.

Please fix the code tags and yes, please upload a workbook

code tags fixed. I've removed all of the excess worksheets and just left in sheet 1 (items) and sheet L9. use the vba editor, click on the form frmMain click show object (right click) and then run the form but select only level 9 from the first combo box otherwise the code will fail looking for worksheets and named ranges that I've just removed.

Regards,

Simon (grateful)

The ColOffset = 5 will reference area outside of named range Table, which is only 4 columns wide.

Not sure why you have for each loop, as that will loop through each individual cell in range Table.
As suggested I think you just need to use the named range.

Set R = Range("Table")
   Result = Application.WorksheetFunction.VLookup(Name, R, ColoffSet, False)

Again not sure what T.additem is show you will have to work out what you want to do with result

VLOOKUP will only return 1 match, so not best suited for looking up multiple matches.

This will loop through each row in the table and display matches. I still think you ColOffset is incorrect as it currently returns the Code rather than Room

Dim Result As Variant
For Each R In Range("Table").Rows
   Result = Application.VLookup(Name, R, ColoffSet, False)
   If Not IsError(Result) Then
        MsgBox Result
    End If
Next R

I don't see how you are defining range T so just went with message box.

In the example select 9, then Area 3. As I said msgbox will display result.

Again I don't see how VLOOKUP will return all matches if Function Area contains multiple "Area 3" items, unless you process a single row at a time.
And T does not make sense. How can you .Additem to a range?