Searchin for dates

[Guest]

I have an excel sheet with dates column 1 and a number value in column 2. For
example:

6/5/06 1
6/6/06 7
6/5/06 1
6/6/06 7
6/7/06 7
6/5/06 1

basically i want excel to look down column 1 find a specific date, look to
the right for the value and call a different sub according to the value..the
problem im having is getting excel to search the everything on the list.
Right now if it finds one instance of the specified date it stops, i want it
to continue down the list, and find all instances of the specified date.
heres what i have of the code so far.

With Worksheets("Sheet1").Range("A1:A10")
Set C = .Find(What:=Date + 10)
Set found = C

If C Is Nothing Then
x = 1
End If

If x = 1 Then
MsgBox ("dont have to send email")

End If

If x = 0 Then
MsgBox ("have to send email")

If found.Offset(columnoffset:=1).Value = "7" Then
MsgBox (found)
A = 1
Call Group1


If found.Offset(columnoffset:=1).Value = "1" Then
MsgBox ("o")
B = 1
Call Group2
End If
End If

End If
End With

 

[Tom Ogilvy]

This should give you some ideas.

Sub EFG()
Dim rng As Range, rng1 As Range
Dim CritA As Date
Dim sAddr As String
CritA = DateSerial(2006, 1, 17)
With Worksheets("Sheet1")
.Activate
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
Set rng1 = rng.Find(What:=Format(CritA, _
rng(1).NumberFormat), _
After:=rng(rng.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
sAddr = rng1.Address
Do
Select Case rng1.Offset(0, 1)
Case 1
' add code for a value of 1
rng1.Offset(0, 1).Interior.ColorIndex = 3
Case 2
' add code for a value of 2
rng1.Offset(0, 1).Interior.ColorIndex = 5
Case 7
' add code for a value of 7
Case Else
' add code if doesn't match specified values
End Select

Set rng1 = rng.FindNext(rng1)
Loop While rng1.Address <> sAddr
End If
End Sub

 

[Guest]

Thanks Tom, the code worked great!!!!

This should give you some ideas.

Sub EFG()
Dim rng As Range, rng1 As Range
Dim CritA As Date
Dim sAddr As String
CritA = DateSerial(2006, 1, 17)
With Worksheets("Sheet1")
.Activate
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
Set rng1 = rng.Find(What:=Format(CritA, _
rng(1).NumberFormat), _
After:=rng(rng.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
sAddr = rng1.Address
Do
Select Case rng1.Offset(0, 1)
Case 1
' add code for a value of 1
rng1.Offset(0, 1).Interior.ColorIndex = 3
Case 2
' add code for a value of 2
rng1.Offset(0, 1).Interior.ColorIndex = 5
Case 7
' add code for a value of 7
Case Else
' add code if doesn't match specified values
End Select

Set rng1 = rng.FindNext(rng1)
Loop While rng1.Address <> sAddr
End If
End Sub