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