CODEWITHSUNDEEP
excelvba
DryBSMT
DryBSMT

Reputation: 65

Why does my Excel User Form VBA search work with one table column and not another?

I have a User Form that searches a table column and returns all the values in the row as editable fields on the form. It works fantastic! But I wanted to add another column to the search. I would like to use the last 4 numbers of an 11 digit number so I created another column with a formula that returns the last 4 digits.

I set the variable with:

RecordRow = Application.Match(CLng(TextBoxSearch.Value), Range("JobSheet[W/O]"), 0).
and it works fine. The column is filled with 6 digit numbers populated by this reference: =IFERROR(JobSheetData[@[W/O]],"").

However, when I change it to this:

RecordRow = Application.Match(CLng(TextBoxSearch.Value), Range("JobSheet[Ticket Search]"), 0) it will not find the row with the search value.

I have a column in the table that uses this reference =IFERROR(JobSheetData[@[ON1Call Ticket '#]],"") and then I have the column Ticket Search that contains the last 4 digits as mentioned above.

The W/O column that is searchable has every line filled with data but 40% of the Ticket Search column is blank. I tried removing values from the W/O column to see if that was the issue but it still worked.

Here is all the code:

Private Sub CommandButton1_Click()

    Dim RecordRow As Long
    Dim RecordRange As Range
    Dim sChkBoxResult As String
 
    ' Turn off default error handling so Excel does not display
    ' an error if the record number is not found
    On Error Resume Next
        
    'Find the row in the table that the record is in

**This one works:**
     RecordRow = Application.Match(CLng(TextBoxSearch.Value), Range("JobSheet[W/O]"), 0)
        
**This one doesn't:**
     RecordRow = Application.Match(CLng(TextBoxSearch.Value), Range("JobSheet[Ticket Search]"), 0)
            
    ' Set RecordRange to the first cell in the found record
        Set RecordRange = Range("JobSheet").Cells(1, 1).Offset(RecordRow - 1, 0)

    ' If an erro has occured i.e the record number was not found
        If Err.Number <> 0 Then
    
            ErrorLabel.Visible = True
            On Error GoTo 0
            Exit Sub
        
        End If
        
    ' Turn default error handling back on (Let Excel handle errors from now on)
       On Error GoTo 0
    
    ' If the code gets to here the record number was found
    ' Hide the error message 'Not Found'
       ErrorLabel.Visible = False
    
   
    ' and populate the form fields with the record's data
        TextBoxNameAddress.Value = RecordRange(1, 1).Offset(0, 3).Value & " - " & RecordRange(1, 1).Offset(0, 2).Value & " " & RecordRange(1, 1).Value
        TextBoxHold.Value = RecordRange(1, 1).Offset(0, 5).Value
        TextBoxDays.Value = RecordRange(1, 1).Offset(0, 7).Value
        CheckBoxLocate.Value = RecordRange(1, 1).Offset(0, 9).Value
        TextBoxCount.Value = RecordRange(1, 1).Offset(0, 11).Value
        TextBoxFirst.Value = RecordRange(1, 1).Offset(0, 13).Value
        TextBoxOveride.Value = RecordRange(1, 1).Offset(0, 14).Value
        CheckBoxBell.Value = RecordRange(1, 1).Offset(0, 15).Value
        CheckBoxGas.Value = RecordRange(1, 1).Offset(0, 16).Value
        CheckBoxHydro.Value = RecordRange(1, 1).Offset(0, 17).Value
        CheckBoxWater.Value = RecordRange(1, 1).Offset(0, 18).Value
        CheckBoxCable.Value = RecordRange(1, 1).Offset(0, 19).Value
        CheckBoxOther1.Value = RecordRange(1, 1).Offset(0, 20).Value
        CheckBoxOther2.Value = RecordRange(1, 1).Offset(0, 21).Value
        CheckBoxOther3.Value = RecordRange(1, 1).Offset(0, 22).Value
       
End Sub

UPDATE:

Here is a screenshot of some sample data: Sample Data The data starts in column A

My ultimate goal was to have an if statement that would run either the 6 digit search on the W/O column or the 4 digit search on the ON1Call Ticket # column based on the length of the string in TextBoxSearch Since they are either 4 digit or 6 digit, I thought I would base it on if the value was >9999 but the `ON1Call Ticket #' column is a text column and not numeric and the search fails.

When the first utility locate arrives the 10 or 11 digit ticket number is automatically added to the Job Sheet. As the emails arrive from the various utilities, the ticket number is always used for identification. I have an automation that extracts the Ticket number and saves the incoming locates as PDF files using the ticket number and some random characters characters as the file name. I have it set up to split the filename like this: 123456 7890 - jkes.pdf. A person now renames the file to indicate what utilities are included in that file and and uses the middle set of 4 numbers in the User Form: enter image description here to find the correct record and check the checkbox of the corresponding utility. I don't want the user to have to type all 11 digits and I was trying to avoid a helper column but I could not figure out how to make the 4 digit search look only at the last 4 digits of the ticket number.

At other times we need to search by the Work Order # which is 6 digits.

Upvotes: 0

Views: 186

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

I would maybe do something like this:

Private Sub CommandButton1_Click()

    Dim RecordRow As Variant '<<< not Long, or throws an error when no match
    Dim vSearch As Long, col, lo As ListObject
    
    Set lo = ThisWorkbook.Worksheets("Data").ListObjects("JobSheet") 'adjust sheet name
    vSearch = CLng(TextBoxSearch.Value)
    
    For Each col In Array("W/O", "Ticket Search") 'loop over columns to search in
        'no need for On Error Resume Next - test the return value from Match instead
        RecordRow = Application.Match(vSearch, lo.ListColumns(col).DataBodyRange, 0)
        If Not IsError(RecordRow) Then Exit For 'got a hit - stop searching
    Next col
    
    ErrorLabel.Visible = IsError(RecordRow) 'hide/show error label
    If Not IsError(RecordRow) Then LoadRecord lo.ListRows(RecordRow).Range
    
End Sub

EDIT: after clarification - different search methods depending on length of input

Private Sub CommandButton1_Click()

    Dim RecordRow As Variant '<<< not Long, or throws an error when no match
    Dim vSearch, col, lo As ListObject
    
    Set lo = ThisWorkbook.Worksheets("Data").ListObjects("JobSheet") 'adjust sheet name
    
    vSearch = TextBoxSearch.Value
    If Not IsNumeric(vSearch) Then
        MsgBox "Search value must be numeric!"
    End If
    'decide how to search based on length of search input
    Select Case Len(vSearch)
        Case 4
            'call custom function instead of Match
            RecordRow = EndsWithMatch(vSearch, lo.ListColumns("ON1Call Ticket #").DataBodyRange)
        Case 6
            'cast search value to Long before using Match
            RecordRow = Application.Match(CLng(vSearch), lo.ListColumns("W/O").DataBodyRange, 0)
        Case Else
            MsgBox "Search value must either 4 or 6 digits!"
    End Select
   
    ErrorLabel.Visible = IsError(RecordRow) 'hide/show error label
    If Not IsError(RecordRow) Then LoadRecord lo.ListRows(RecordRow).Range
    
End Sub
'search a single-column range of data for an "ends with" match to `vSearch`
Function EndsWithMatch(vSearch, rngSrch As Range)
    Dim i As Long, arr
    arr = rngSrch.Value
    For i = 1 To UBound(arr, 1)
        If arr(i, 1) Like "*" & vSearch Then
            EndsWithMatch = i
            Exit Function 'done searching
        End If
    Next i
    EndsWithMatch = CVErr(xlErrNA) 'no match: return error value as in Match()
End Function

Common to both answers (edit - added some suggestions for saving the edited record):

Dim editedRow as Range 'holds a reference to the row loaded for editing

'Better as a stand-alone method which you can call from other places...
Sub LoadRecord(sourceRow As Range)
    With sourceRow
        TextBoxNameAddress.Value = .Cells(4).Value & " - " & _
                        .Cells(3).Value & " - " & .Cells(1).Value
        TextBoxHold.Value = .Cells(6).Value
        'etc for other fields
    End With
    Set editedRow = sourceRow 'set a global for the row being edited
    'also enable the "Save" button... 
End Sub

Sub SaveRecord()
    If Not editedRow Is Nothing Then
        With editedRow
            .Cells(6).Value = TextBoxHold.Value
            'etc for the other fields
        End With
    Else
        MsgBox "No row is being edited!"
    End If
End Sub

It's easier/safer to test the return value from Match() than to turn off errors.

Upvotes: 2

Related Questions