Find and Findnext Problems

[lickrob]

ok so im having some problems with my Workbook search. i have attached it to this thread as describing it would just be confusing. (I was having problems uploading so its Zipped)

ok so here is an example. I have had to replace all the names for data protection hence the strange names.

I need to search for "AName007"

this value currently populates the following cells:

Sheet2:A10 valid=No
Sheet2:A12 valid=No
Sheet3:A8 valid=No
Sheet4:A15 valid=Yes

the scrip should then lookup if this is "valid" by panning accross to the right, as you can see it is not valid for Sheet2:A10, Sheet2:A12, Sheet3:A8 however it is valid for Sheet4:A15. so in theory it should return the value "EEE003" in the consultant bok on sheet one.

this all works fine.

where i have a problem is if the it is valid on the same sheet as one that is not valid it will not find it. if you where to step through the first example you would see taht it never finds Sheet2:A12 but ill show you another example anyway:

we search for AName008

this value currently populates the following cells:

Sheet2:A11 valid=No
Sheet2:A36 valid=Yes

as Sheet2:A36 is valid it should return the value QQQ023 in the consultante box on sheet 1, however it does not, the script will not find the same value on the same sheet. And I cant figure out why.

anyway, this is the first VBA script i have written, so if you do change anything can you please explain why.

[Bob Phillips]

[VBA]Sub Search()
Dim ws As Worksheet
Dim customer As Range
Dim valid As Range
Dim firstaddress As String
Dim found As Boolean
Dim headiings As Variant
Dim idx As Long

Application.ScreenUpdating = False

Sheet1.Cells(15, 4).Value = "NO"
Sheet1.Cells(13, 4).Value = ""
Sheet1.Cells(17, 4).Value = ""
Sheet1.Cells(19, 4).Value = ""

For Each ws In Worksheets(Array("Sheet2", "Sheet3", "Sheet3", "Sheet4"))

With ws.Columns(1)

Set customer = .Find(Sheet1.tbSearch.Text, After:=ws.Range("A1"))
If Not customer Is Nothing Then

firstaddress = customer.Address
Do

For idx = 2 To 254 Step 4

If ws.Cells(customer.Row, idx).Value = "Yes" Then

Sheet1.Cells(15, 4).Value = "YES"
Sheet1.Cells(13, 4).Value = ws.Cells(2, idx).Value
Sheet1.Cells(17, 4).Value = ws.Cells(customer.Row, idx + 1).Value
Sheet1.Cells(19, 4).Value = ws.Cells(customer.Row, idx + 2).Value

found = True
Exit For 'idx
End If
Next idx

If Not found Then Set customer = .FindNext(After:=customer)
Loop Until customer Is Nothing Or customer.Address = firstaddress Or found
End If 'customer Is Nothing
End With

If found Then Exit For 'ws
Next ws

Application.ScreenUpdating = True
End Sub
[/VBA]

[lickrob]

Wow thats alot simpler, thanks alot

[lickrob]

is it possable to seacrch for the next item. so say i search for "AName" it will just cycle through all the values with AName in the content?

would this be done with .findnext?

[Bob Phillips]

What is different in this from just chhanging the name and doing a new search?

[lickrob]

im not sure what you are asking. ill try expain better.

in the workbook it could be possable for AName007 to have 2 values that come up as yes lets say

Sheet3:A8 valid=Yes
Sheet4:A15 valid=Yes


currently when you search you will only return the value for Sheet3:A8 and if you where to search again the same thing would happen, as this one appears first. you will never see that Sheet4:A15 is also valid. so how would i get it so that when i click search for the first time i get Sheet3:A8 but the second time i click search i get Sheet4:A15

[Bob Phillips]

As far as I can see Sheet3!A8 is a No, so it will find Sheet4!A15, and only Sheet4!A15.

[lickrob]

currently Sheet3!A8 is "no" but if you where to change it to "yes" then what i said above applies and it would still only find Sheet4!A15

[Bob Phillips]

[VBA]Sub Search()
Static prevCustomer As String
Static prevCell As String
Static prevWS As String
Dim ws As Worksheet
Dim arySheets As Variant
Dim afterCell As String
Dim selected As String
Dim customer As Range
Dim valid As Range
Dim firstaddress As String
Dim found As Boolean
Dim headiings As Variant
Dim idx As Long

Application.ScreenUpdating = False

With sheet1

.Cells(15, 4).Value = "NO"
.Cells(13, 4).Value = ""
.Cells(17, 4).Value = ""
.Cells(19, 4).Value = ""
End With

selected = sheet1.tbSearch.Text
If selected = prevCustomer Then

Select Case prevWS

Case "Sheet2": arySheets = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
Case "Sheet3": arySheets = Array("Sheet3", "Sheet4", "Sheet5")
Case "Sheet4": arySheets = Array("Sheet4", "Sheet5")
Case "Sheet5": arySheets = Array("Sheet5")
End Select

afterCell = prevCell
firstaddress = prevCell
Else

afterCell = "A1"
arySheets = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
End If

For Each ws In Worksheets(arySheets)

With ws.Columns(1)

Set customer = .Find(selected, After:=ws.Range(afterCell))
If Not customer Is Nothing Then

Do While Not customer Is Nothing And customer.Address <> firstaddress And Not found

For idx = 2 To 254 Step 4

' Debug.Assert idx <> 90
If ws.Cells(customer.Row, idx).Value = "Yes" Then

sheet1.Cells(15, 4).Value = "YES"
sheet1.Cells(13, 4).Value = ws.Cells(2, idx).Value
sheet1.Cells(17, 4).Value = ws.Cells(customer.Row, idx + 1).Value
sheet1.Cells(19, 4).Value = ws.Cells(customer.Row, idx + 2).Value

found = True
Exit For 'idx
ElseIf ws.Cells(customer.Row, idx).Value = "No" Then

Exit For 'idx
End If
Next idx

If firstaddress = "" Then firstaddress = customer.Address

If Not found Then Set customer = .FindNext(After:=customer)
Loop
End If 'customer Is Nothing
End With

If found Then Exit For 'ws

afterCell = "A1"
firstaddress = ""
Next ws

If found Then

prevWS = ws.Name
prevCell = customer.Address
prevCustomer = selected
Else

prevWS = ""
prevCell = ""
prevCustomer = ""
End If

Application.ScreenUpdating = True
End Sub
[/VBA]

[lickrob]

yup thats it, working perfectly. thanks so much for your help.