Hi Susan,
After thinking long and hard i eventually came to a solution without
changing all my codes... i deleted this : Set r =
Columns("A").find(What:=TextBox1.Value)
and changed this line : Set c = .find(r, LookIn:=xlValues)
to : Set c = .find(TextBox1, LookIn:=xlValues)
and it works great!
I will try your code and see what happens maybe it will save me a lot of
space!!
Thanks anyways it is much appreciated!


This is the new code:

Private Sub find()
j = 1

Dim r As Range
ActiveWorkbook.Worksheets(j).Activate

If TextBox1 = "" Then
Else
Do
With Worksheets(j).Range("a:a")
Set c = .find(TextBox1, LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
Do

Set c = .FindNext(c)

TextBox1.Value = c.Offset(, 0)
TextBox2.Value = c.Offset(, 8)
TextBox3.Value = c.Offset(, 3)
TextBox4.Value = c.Offset(, 4)
TextBox5.Value = c.Offset(, 1)
TextBox6.Value = "N/A"
TextBox7.Value = "N/A"
Response = MsgBox("Is this the information you need?",
vbYesNo + vbQuestion)
If Response = vbYes Then
MsgBox "Press ok when finished"
Call clear
Exit Sub
Else
End If

Loop While Not c Is Nothing And c.Address < firstAddress

End If

End With

j = j + 1
Loop Until j = 12
MsgBox "Please note that there are no more entries avaliable"
Call clear
End If

End Sub


This is the troubled code

JustLearning wrote:
I used the following code to search for a value(numbers and letters) in
different sheets(12) in a column. What i have found is that it works only for
a certain number of rows and i need to search all rows! Any suggestions?
Each time im receiving : Run time error5; Invalid procedure call or argument

Private Sub find()
j = 8

Dim r As Range
ActiveWorkbook.Worksheets(j).Activate
If TextBox1 = "" Then
Else
Do
Set r = Columns("A").find(What:=TextBox1.Value)
With Worksheets(j).Range("a1:a5000")
Set c = .find(r, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do

Set c = .FindNext(c)

TextBox1.Value = c.Offset(, 0)
TextBox2.Value = c.Offset(, 8)
TextBox3.Value = c.Offset(, 3)
TextBox4.Value = c.Offset(, 4)
TextBox5.Value = c.Offset(, 1)

Response = MsgBox("Is this the information you need?", vbYesNo +
vbQuestion)
If Response = vbYes Then
MsgBox "Press ok when finished"
Call clear
Exit Sub
Else
End If
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
j = j + 1
Loop Until j = 12
End If

End Sub

thanks