CODEWITHSUNDEEP
excelvba
LievenSi
LievenSi

Reputation: 15

How to search a column (multiple rows) for only the first 6 characters of every row?

There is a sheet containing information in multiple columns. The code below searches a single column and prints the rest of the data in my Userform.

First question: the column I want to search (column 1) contains 8 characters, but only the first 6 need to be searched. I tried using .Characters(1, 6).

Sheet1 contains around 5500 rows of data.

Second question: I would like to print the data of the row in a few TextBoxes as shown in the code. TextBox2 should contain the result of the searched cell in my first question (column 1), but again only the first 6 characters.

Public Sub Search_Owner()

    Dim PrsNr As String
    Dim Found As Range

    PrsNr = TextBox1.value

    Worksheets("Sheet1").Activate

    Set Found = Worksheets("Sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp)).Characters(1, 6).Find(PrsNr, Lookat:=xlWhole)

    If Found Is Nothing Then
        MsgBox "Nothing found", vbCritical
    Else

        TextBox2 = Cells(Found.Row, 1).value
        TextBox3 = Cells(Found.Row, 5).value
        TextBox4 = Cells(Found.Row, 8).value

    End If

End Sub

Upvotes: 0

Views: 56

Answers (1)

Abdallah El-Yaddak
Abdallah El-Yaddak

Reputation: 466

  1. Yes, you can by using "*" wildcard within your .Find method. More about it
  2. Yes, by using Left() function. More about it

This is how:

Public Sub Search_Owner()
    Dim PrsNr As String
    Dim Found As Range
    PrsNr = TextBox1.Value
    Set Found = Worksheets("Sheet1").Range("A2", Range("A" & Rows.Count).End(xlUp)).Find(PrsNr & "*", Lookat:=xlWhole)
    If Found Is Nothing Then
        MsgBox "Nothing found", vbCritical
    Else
        TextBox2 = Left$(Cells(Found.Row, 1).Value, 6)
        TextBox3 = Left$(Cells(Found.Row, 5).Value, 6)
        TextBox4 = Left$(Cells(Found.Row, 8).Value, 6)
    End If
End Sub

Upvotes: 1

Related Questions