Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search engin
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search engin
JustLearning wrote:
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search engin
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" wrote: JustLearning wrote: 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search engin
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 wrote: 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" wrote: JustLearning wrote: 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search engin
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search engin
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 JustLearning wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two search category & two search terms | Excel Discussion (Misc queries) | |||
Functions (search within search result) reply to this please | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
I cant do a search on this forum. Everytime I search, it comes up with zero results | Excel Programming | |||
Create a search Field within a worksheet to search command buttons | Excel Programming |