HLookup & Match driven by UserForm inputs

[crburke92]

Hello,

Hoping I'm pretty close and there's a simple solution. I have a Userform that has 3 inputs. A textbox (DevID), and 2 Comboboxes (DevSel & LocSel). The idea is to name a new sheet using DevID, The sheet to copy is a hidden worksheet selected via DevSel. This portion works fine.

The part I'm struggling with is using a HLookup & Match to find the first blank cell underneath the matched value in LocSel and make the value DevID. What I currently have is:

Private Sub Close_Btn_Click()
UFInfo.Hide
End Sub

Private Sub Execute_Btn_Click()
Dim DevID As String

Select Case DevSel
    Case "HVBKR"
       Sheets("HVCIRCUITBREAKER_TEMP").Visible = True
            Sheets("HVCIRCUITBREAKER_TEMP").Copy After:=Sheets("Master")
                Sheets("HVCIRCUITBREAKER_TEMP").Visible = False
                    ActiveSheet.Name = Me.DevID.Text
WorksheetFunction.HLookup(LocSel, Range("Headers"), WorksheetFunction.Match(LocSel, Range("Headers").End(xlUp).Offset(1), False), False) = DevID
End Select

Unload Me
Sheets("Master").Activate

End Sub

Private Sub UserForm_Initialize()
Dim Cell As Range
For Each Cell In Range("Headers")
    UFInfo.LocSel.AddItem (Cell.Value)
Next Cell

For Each Cell In Range("DevList")
    UFInfo.DevSel.AddItem (Cell.Value)
Next Cell
End Sub

Combo boxes fill from named ranges which also works fine. Currently I'm getting Run time error '1004' Unable to get the Match property of the WorksheetFunction class. I'm assuming I'm not referencing the Combobox/Textbox selections properly/need to dim them properly?

Thanks in advance!

 

[crburke92]

Anyone?