1

I currently have a userform with 3 inputs (DevID, DevSel and LocSel). The Idea is to copy a template sheet based off combobox DevSel, and rename the sheet based on the DevID text box. The issue I have is I have combobox LocSel poplated with the defined range "Headers" (A1:F1) and want to use V&HLooup, or HLookup & Match to return first blank cell under the matched LocSel header, and make it equal DevID.

Private Sub Execute_Btn_Click()
Dim Headers As Range
Dim LocID As Range
Select Case DevSel
Case "HVBKR"
'Make a copy of the selected sheet
Sheets("HVCIRCUITBREAKER_TEMP").Visible = True
Sheets("HVCIRCUITBREAKER_TEMP").Copy After:=Sheets("Master")
Sheets("HVCIRCUITBREAKER_TEMP").Visible = False
ActiveSheet.Name = Me.DevID.Text
'Autopopulate site list
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

In the Debugger, LocSel and DevID are populating with the correct information. based on my box selections, but I keep getting Runtime Error1004 - Unable to get the Match property of the worksheetfunction class. Any help would be appreciated!

crburke92
  • 11
  • 2
  • Maybe it would help to use `Debug.Print Range("Headers").End(xlUp).Offset(1).Address` and inspect the output in the Immediate Window. Hint: it's going to be `$A$2`. Is that what you want? – BigBen Dec 03 '21 at 20:21
  • Made a mistake and fixed in initial post. Heads is A1:F1. The idea is to find a matching result in "headers" with what you select in LocSel (LocSel combobox autopopulates based on "Headers" so it will always find the match). I then need to find the first blank under the matching header and make it = DevID. When I hovered over End(xlUP) in the debugger it gave me -4162. – crburke92 Dec 03 '21 at 20:30
  • 1
    If headers is A1:F1, `Range("Headers").End(xlUp).Offset(1)` is `A2`. You shouldn't be using `Offset` in the match it seems. Side note: hovering over `End(xlUp)` is not the same as actually using `Debug.Print`. It would be helpful to learn how to use `Debug.Print`. – BigBen Dec 03 '21 at 20:31
  • I wouldn't use `HLookup` here at all either. Use `Match` to find the appropriate column, then [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) in that column. Then `Offset`. – BigBen Dec 03 '21 at 20:35
  • `headerColumn = Rows(1).Find(searchTerm).Column` then you can search in the `headerColumn`. Is there a lower bound to your table, meaning that you can loop a fixed range (think i = 2 to lastRow), or is this a `do until` scenario? – Cyril Dec 03 '21 at 20:36
  • Not really a lower bound but should not exceed ~10 rows. Ie I could set a lower bound I know I'll never hit. – crburke92 Dec 03 '21 at 21:17
  • Is the combobox items in the same order as the headers, if so the column would be Listindex + 1 – CDP1802 Dec 04 '21 at 22:58
  • They are the same order as the box populates with `For Each Cell In Range("Headers") UFInfo.LocSel.AddItem (Cell.Value) Next Cell` – crburke92 Dec 06 '21 at 17:44
  • Without the loop `UFInfo.LocSel.List = Application.Transpose(Range("Headers"))` – CDP1802 Dec 06 '21 at 18:00
  • Welcome to SO! It will be a good idea to edit your question and add your input with a few screenshots, if not, any user trying to help you is completely blind. – David García Bodego Dec 10 '21 at 05:32

0 Answers0