View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ralph Heidecke[_2_] Ralph Heidecke[_2_] is offline
external usenet poster
 
Posts: 7
Default Problem with find method

I have the following code that looks for dates from an array that may
already exist in a worksheet. When I test this code with a date in the array
already put inthe worksheet the find results in nothing;

what needs to be corrected?

'load the dates in row B into the array

Dim dtArray() As Date ' capture the selected dates
Dim icol as Integer
Dim ColCount as Integer
Dim Colstep as Integer
Dim i As Integer
Dim c as Range

ReDim dtArray(ColCount)
For iCol = 1 To ColCount Step 1
dtArray(iCol) = Cells(2, Colstep).Value
Colstep = Colstep + 1
Next iCol

' check if these dates were previously processed
Worksheets("datecontrol").Activate
With ActiveSheet.Usedrange ' the dates should be in Col A & it doesn't
matter if I use Range("A:A") or put the date in A2 and say .Range("A2")
For i = LBound(dtArray) To UBound(dtArray)
Set c = .Find(what:=dtArray(i), Lookat:=xlWhole)
If Not c Is Nothing Then
' c always ends up being "Nothing"
MsgBox "This date may have been processed; " & c.Value & "
terminating program"
Application.ScreenUpdating = True
Exit Sub
End If

Next i
End With