Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searchin for dates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searchin for dates
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searchin for dates
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) |