loop with array

[John]

The following code pops up my msg box... meaning it doesn't find what I am
looking for. Any ideas why? Thanks for the help.

John


Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Dim DestCell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wks = Sheets("data")
Set rngToSearch = Range("j7:j712")

Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value,
Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues,
lookat:=xlWhole)
If rngFound Is Nothing Then
Range("a6").Select
MsgBox "No new Floaters"
Else
Do
With Worksheets("vlookup")
Set DestCell = Range("a800").End(xlDown).Offset(1, 0)
End With
rngFound.EntireRow.Copy _
Destination:=DestCell
Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Sheets("data").Select
Range("a6").Select
End Sub

 

[John]

If it makes a difference, the values in column D and what I am trying to find
are dates...

 

[John]

I changed the lookat:= to Lookat:= and it began to run... however it stays on
the same row, stuck in a continual loop and ideas on that?

 

[Dave Peterson]

I didn't look too closely at your code, but this line jumped out at me:

Set rngFound = rngToSearch.Find(what:=Array(Range("d1").Value,
Range("d2").Value, Range("d3").Value, Range("d4").Value), LookIn:=xlValues,
lookat:=xlWhole)

I would think you'd want to loop through those ranges and do your individual
finds.

dim myRng as range
dim myCell as range
dim FoundIt as boolean

with activesheet
set myrng = range("d1:d4")
end with

foundit = false
for each mycell in myrng.cells
Set rngFound = rngToSearch.Find(what:=mycell.Value), LookIn:=xlValues,
lookat:=xlWhole)
if rngfound is nothing then
'keep looking
else
foundit = true
exit for
end if
next mycell

if foundit = false then
'....

 

[John]

Dave, thanks for the response. I reposted and I think you can get to the new
post here.
http://www.microsoft.com/office/com...ming&mid=d377c852-97f9-4cd6-b5d5-68a316823a70
Would you mind taking a look at where we are at there? I would appreciate
the help if you have the chance.

Thanks

 

[Dave Peterson]

If Jim's code isn't working, I think you may want to take the time to post what
you want the code to do--in plain old words.

And I'm sure Jim will be able to help once he understands your objective.

 

[John]

I am sure he will be able to as well, I just thought you may catch something
we are missing. Another perspective never hurt... and I believe he knows
what I am trying to do quite well.