Perform a Vlookup using a Form ComboBox as the Lookup Value

I am a poor student at VBA so this should be easy for you pro's out there.

Below is as far as i have got. No errors are thrown up but it doesn't do as i expect it too.

Any help appreciated.

Thanks in advance.

Option Explicit


Private Sub ComboBox1_Change()

    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim LastRow As Long
        Set ws = Worksheets("Tariffs")
        Set ws1 = Worksheets("Billing")

On Error Resume Next

LastRow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row

ws1.Cells("B2") = WorksheetFunction.VLookup(ComboBox1, ws.Range("Tariff2"), 2, False)


End Sub

Okay thanks Buran.

Yes the range Tariff2 is set in the Tariff sheet. In theory should the code I entered work? (am i barking up the right tree)

Originally Posted by buran

it looks right to me. see the attached example

Hi Buran,

Yours works absolutely correct the only thing is you have defined the range and i need a dynamic range as some times there maybe more or less options in the table array on the lookup.

Norie,

The combobox is populated with the below code.

Private Sub UserForm_Initialize()
  Dim T1 As Range
  Dim ws As Worksheet
  
    Set ws = Worksheets("Tariffs")
        
        For Each T1 In ws.Range("Tariff")
            
            Me.ComboBox1.AddItem T1.Value
            
        Next T1
                
End Sub

Hi Norie,

The Tariff range is A2 to A7 the Tariffs sheet.
The Tariff2 range is A2 to D7 on the Tariffs sheet.

Thanks for your time.

Hi Buran,

The Range is A2 to D7 at the moment but i want it to be able to change to D8 if another is added etc.

Thanks for your help.

Hi Buran,

The scope is set as workbook on both Tariff and Tariff2. I will upload the Workbook shortly first i need to get rid of all my embaressing workings out.

I have it working perfectly now thanks guys. One of My Named Ranges was set wrong so i started again and its now doing as it should.

I Set this as the named range TariffName =OFFSET(Tariffs!$A$2,0,0,COUNTA(Tariffs!$A$2:$A$200),1)
and this as the vlookup range TariffRng =OFFSET(Tariffs!$A$2,0,0,COUNTA(Tariffs!$A$2:$A$200),4)

The the following code

Option Explicit
Private Sub CBox_Tariff_Change()

Dim ws As Worksheet
Dim ws1 As Worksheet

Set ws = Sheets("Tariffs")
Set ws1 = Sheets("Billing")

ws1.Range("B1") = WorksheetFunction.VLookup(CBox_Tariff, Range("TariffRng"), 2, False)

End Sub



Private Sub UserForm_Initialize()
  Dim T1 As Range
  Dim ws As Worksheet
  
    Set ws = Worksheets("Tariffs")
        
        For Each T1 In ws.Range("TariffName")
            
            Me.CBox_Tariff.AddItem T1.Value
            
        Next T1
                
End Sub

Thank you both for your help your suggestions helped me greatly to get the finished result.