Search engin

[JustLearning]

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

 

[Susan]

With Worksheets(j).Range("a1:a5000")

you have it set to only search through 5000 rows....... do you need
more than that, or is it stopping well before then?
if you want it to search the whole column, you could do

With Worksheets(j).Range("a:a")

this would have no limit on rows.
susan

 

[JustLearning]

Hi Susan
I only need about 500 lines per sheet.
Your method works but i still get the same problem?
The error is in this line :
Set c = .find(r, LookIn:=xlValues)
and if my cursor is on this line it shows :
xlValues= -4163

Thanks

 

[Susan]

ok.......... sometimes when you've pared down code, you might have
pared it down too much..... i recorded a "find" & this is what i got:

Cells.Find(What:="simple", After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

i don't know if you can set a variable to be a function..........
you have "c" trying to be a function, not a thing.
why don't you try

With Worksheets(j).Range("a:a")
Cells.Find(What:="TextBox1.Value", After:=ActiveCell,
LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

'you might have to eliminate the quotes in the What: field, or perhaps
add
'Me. to the statement.
'and later you can pare down what you don't need out of the find
'statement

If Not c Is Nothing Then
'i assume "c" stands for "cell", but you didn't declare it...........
firstAddress = c.Address
etc.

if i'm waaaaay off base, then be forewarned that i'm no guru, & maybe
this is beyond what i am understanding.
susan

 

[JustLearning]

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

 

[Susan]

well, if it works for you, that's great, plus you taught me something.
i didn't know you could assign a variable to a FUNCTION - i thought it
had to be a "thing".
so you were right after all!

but thanks for making me think!
susan