Searchin for dates

[Reggie]

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


--
Regards,
Tom Ogilvy

"Reggie" <Reggie@discussions.microsoft.com> wrote in message
news:E14D3F1A-2D8B-4113-B118-38A5C3C71431@microsoft.com...
> 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
>

[Reggie]

Thanks Tom, the code worked great!!!!

"Tom Ogilvy" wrote:

> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Reggie" <Reggie@discussions.microsoft.com> wrote in message
> news:E14D3F1A-2D8B-4113-B118-38A5C3C71431@microsoft.com...
> > 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
> >
>
>
>