Searching a string from range of values of a sheet using VBA code. My data starts from Column C and number of rows are dynamic

Viewed 10

I have a data set in an excel sheet which is dynamic. I need to search a string from the data range starting from C3 and is extending to more than 700 rows and 16 columns. I have tried following code but facing subscript our of range error.

Private Sub btnSearch_Click()

Dim iSearch As Long, i As Long Dim jSearch As Long, j As Long

With Worksheets("Sheet1").Range("C3").CurrentRegion
    iSearch = .Rows.Count
    jSearch = .Columns.Count
End With

For i = 1 To iSearch

For j = 1 To jSearch

If Trim(Sheet1.Cells(i, j)) = Trim(txtSearch.Text) Then

    txtSrNo.Text = Sheet1.Cells(i, 3)
    txtDepID.Text = Sheet1.Cells(i, 4)
    txtEmpName.Text = Sheet1.Cells(i, 5)
    txtLocation.Text = Sheet1.Cells(i, 6)

    txtMemName.Text = Sheet1.Cells(i, 7)
    txtMemLastname.Text = Sheet1.Cells(i, 8)
    txtGender.Text = Sheet1.Cells(i, 9)
    txtRelation.Text = Sheet1.Cells(i, 10)
    txtDependency.Text = Sheet1.Cells(i, 11)
    txtDoB.Text = Sheet1.Cells(i, 12)
    txtMarital.Text = Sheet1.Cells(i, 13)
    txtHandicap.Text = Sheet1.Cells(i, 14)
    txtEligibleSI.Text = Sheet1.Cells(i, 15)
    txtOptedSI.Text = Sheet1.Cells(i, 16)

Exit For
End If

Next j

Next i

End Sub

0 Answers
Related