CODEWITHSUNDEEP
vbaexcel
Kevin
Kevin

Reputation: 3

Using VBA to search a range & return values of row to userform

I use a userform (BoaterChecklist) to enter data into an excel sheet and I am looking to add a feature to search by a unique ID (PermitNumber) in column C to return the row of matching ID to update and edit that data. I am having trouble loading the found row back into the user form. I believe my code is finding the correct ID cell in column C, however it is not making it active. Whatever cell is selected on the sheet is what row data gets loaded into the userform, not the found cell.

I am unsure where to implement the "ActiveCell.Value" to return the correct row values for the found ID. Any help would be greatly appreciated! 

Private Sub SearchForm_Click()
Dim str as String
Dim rgFound as Range

if Permitnumber.text = "" then
msg box "enter a permit number"
exit sub
end if

with worksheets("sheet1")
 str = PermitNumber.Value
 Set rgFound = .Range("c2:c3000").Find(what:=str)
 if rgFound is Nothing then       
 msgbox "permit not found"
else
DateBox.Text = Cells(ActiveCell.row, 1)
TimeBox.Text = Format(cells(ActiveCell.Row, 2), "hh:mm:ss")
PermitNumber.Text = Cells (ActiveCell.Row, 3)
VesselName.Text = Cells (ActiveCell.Row, 4)
 'ect...

end if
end with 
end sub

Thank you!

Upvotes: 0

Views: 2691

Answers (1)

paul bica
paul bica

Reputation: 10715

As suggested, use the found range as reference

Option Explicit

Private Sub SearchForm_Click()
    Dim str As String, found As Range

    If PermitNumber.Text = vbNullString Then
        MsgBox "Enter a permit number"
        Exit Sub
    End If

    With Worksheets("Sheet1")
        str = PermitNumber.Value
        Set found = .UsedRange.Find(What:=str)

        If found Is Nothing Then
            MsgBox "Permit not found"
        Else
            DateBox.Text = .Cells(found.Row, 1).Value
            TimeBox.Text = Format(.Cells(found.Row, 2), "hh:mm:ss")
            PermitNumber.Text = .Cells(found.Row, 3)
            VesselName.Text = .Cells(found.Row, 4)
            'ect...
        End If
    End With
End Sub

.

For more precise control when searching, specify parameters that shouldn't use default values

Range.Find - Method Parameters

Required Parameter --------------------------------------------------------------------

What:= - Value to search for (string or any Excel data type)
       - If What:="" is used, it will return Nothing (no cell found)
       - If What:="*" is used, it will return first non empty cell

Optional Parameters -------------------------------------------------------------------

After:=           - Starts search after (single) cell specified: After:=Cells(1)
                  - It excludes the specified cell, so it starts on Cells(2)
                  - It defaults to upper-left corner of the searched Range
                  - To move to the next found value after the first use:
                    - After:=Cells(1).Offset(1) along with
                    - SearchOrder:=xlByRows or xlByColumns

LookIn:=          - Formulas, Values, Comments. Defaults to Formulas

LookAt:=          - xlWhole or xlPart. If not specified it defaults to xlPart

SearchOrder:=     - xlByRows or xlByColumns. Defaults to xlByRows

SearchDirection:= - xlNext or xlPrevious. Defaults to xlNext

MatchCase:=       - True (search is case sensitive). Default to False

MatchByte:=       - Used only for double-byte language support
                  - True to have double-byte chars match only double-byte chars
                  - False to have double-byte chars match their single-byte equiv

SearchFormat:=    - Searches Font and/or cell formatting. Defaults to False



 * LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method

** Not specifying these parameters, it will use the previous search settings

Upvotes: 2

Related Questions