Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Sunday with min value
I am looking in a range of dates to find the minimum date. Once it is found,
I will find what weekday it is. If it isn't a Sunday, I go to the smallest Sunday larger than the Minimum date to set the starting point for my code. I have everything working, except I can't seem to transfer the cell with the minimum date to a range variable. This was my latest attempt: Set rng = Range(fst.Address & ":A" & Cells(Rows.Count, "A").End(xlUp).Row) Set dmin = rng.Find(Application.WorksheetFunction.Min(rng)) dmin is always set to Nothing. What do I need to do to set dmin as the cell containing the minimum date? Also, since it is possible that "fst" may not be in column A, is there a more flexible way to set my "rng" variable? Thanks, -- tj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Sunday with min value
This works for me
iCol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, iCol).End(xlUp)) val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & fst.Address & "))," & fst.Address & "0)") Set DMin = fst(val) -- HTH Bob Phillips "tjtjjtjt" wrote in message ... I am looking in a range of dates to find the minimum date. Once it is found, I will find what weekday it is. If it isn't a Sunday, I go to the smallest Sunday larger than the Minimum date to set the starting point for my code. I have everything working, except I can't seem to transfer the cell with the minimum date to a range variable. This was my latest attempt: Set rng = Range(fst.Address & ":A" & Cells(Rows.Count, "A").End(xlUp).Row) Set dmin = rng.Find(Application.WorksheetFunction.Min(rng)) dmin is always set to Nothing. What do I need to do to set dmin as the cell containing the minimum date? Also, since it is possible that "fst" may not be in column A, is there a more flexible way to set my "rng" variable? Thanks, -- tj |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Sunday with min value
Thanks, Bob. I'm getting a Compile Error when I test this code. It tells me
that a function call on the left side of an equation must return a variant or an object. -- tj "Bob Phillips" wrote: This works for me iCol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, iCol).End(xlUp)) val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & fst.Address & "))," & fst.Address & "0)") Set DMin = fst(val) -- HTH Bob Phillips "tjtjjtjt" wrote in message ... I am looking in a range of dates to find the minimum date. Once it is found, I will find what weekday it is. If it isn't a Sunday, I go to the smallest Sunday larger than the Minimum date to set the starting point for my code. I have everything working, except I can't seem to transfer the cell with the minimum date to a range variable. This was my latest attempt: Set rng = Range(fst.Address & ":A" & Cells(Rows.Count, "A").End(xlUp).Row) Set dmin = rng.Find(Application.WorksheetFunction.Min(rng)) dmin is always set to Nothing. What do I need to do to set dmin as the cell containing the minimum date? Also, since it is possible that "fst" may not be in column A, is there a more flexible way to set my "rng" variable? Thanks, -- tj |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Sunday with min value
Is that ob the val line or the Set rng line?
Have you declared these variables at all, if so to what? -- HTH Bob Phillips "tjtjjtjt" wrote in message ... Thanks, Bob. I'm getting a Compile Error when I test this code. It tells me that a function call on the left side of an equation must return a variant or an object. -- tj "Bob Phillips" wrote: This works for me iCol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, iCol).End(xlUp)) val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & fst.Address & "))," & fst.Address & "0)") Set DMin = fst(val) -- HTH Bob Phillips "tjtjjtjt" wrote in message ... I am looking in a range of dates to find the minimum date. Once it is found, I will find what weekday it is. If it isn't a Sunday, I go to the smallest Sunday larger than the Minimum date to set the starting point for my code. I have everything working, except I can't seem to transfer the cell with the minimum date to a range variable. This was my latest attempt: Set rng = Range(fst.Address & ":A" & Cells(Rows.Count, "A").End(xlUp).Row) Set dmin = rng.Find(Application.WorksheetFunction.Min(rng)) dmin is always set to Nothing. What do I need to do to set dmin as the cell containing the minimum date? Also, since it is possible that "fst" may not be in column A, is there a more flexible way to set my "rng" variable? Thanks, -- tj |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Sunday with min value
Below is what I have been using to test with since your initial response. I
get the error message with the line that starts "val =." This is the error: "function call on the left side of an equation must return a variant or an object." Thank you for your continuing assistance. Sub test() Dim icol As Integer Dim fst As Range, rng As Range, dmin As Range Set fst = Cells.Find(What:="Date").Offset(1, 0) icol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, icol).End(xlUp)) Val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & _ fst.Address & "))," & fst.Address & "0)") Set dmin = fst(Val) MsgBox dmin End Sub -- tj "Bob Phillips" wrote: Is that ob the val line or the Set rng line? Have you declared these variables at all, if so to what? -- HTH Bob Phillips "tjtjjtjt" wrote in message ... Thanks, Bob. I'm getting a Compile Error when I test this code. It tells me that a function call on the left side of an equation must return a variant or an object. -- tj "Bob Phillips" wrote: This works for me iCol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, iCol).End(xlUp)) val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & fst.Address & "))," & fst.Address & "0)") Set DMin = fst(val) -- HTH Bob Phillips "tjtjjtjt" wrote in message ... I am looking in a range of dates to find the minimum date. Once it is found, I will find what weekday it is. If it isn't a Sunday, I go to the smallest Sunday larger than the Minimum date to set the starting point for my code. I have everything working, except I can't seem to transfer the cell with the minimum date to a range variable. This was my latest attempt: Set rng = Range(fst.Address & ":A" & Cells(Rows.Count, "A").End(xlUp).Row) Set dmin = rng.Find(Application.WorksheetFunction.Min(rng)) dmin is always set to Nothing. What do I need to do to set dmin as the cell containing the minimum date? Also, since it is possible that "fst" may not be in column A, is there a more flexible way to set my "rng" variable? Thanks, -- tj |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Sunday with min value
Hi Tjtjjtjt
Try this slight modification: Sub test() Dim icol As Integer Dim fst As Range, rng As Range, dmin As Range Dim myVal As Long Set fst = Cells.Find(What:="Date").Offset(1, 0) icol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, icol).End(xlUp)) myVal = Evaluate("MATCH(MIN(IF(WEEKDAY(" & _ rng.Address & ")=1," & rng.Address & "))," _ & rng.Address & "0)") Set dmin = rng(myVal) MsgBox dmin.Value End Sub --- Regards, Norman "tjtjjtjt" wrote in message ... Below is what I have been using to test with since your initial response. I get the error message with the line that starts "val =." This is the error: "function call on the left side of an equation must return a variant or an object." Thank you for your continuing assistance. Sub test() Dim icol As Integer Dim fst As Range, rng As Range, dmin As Range Set fst = Cells.Find(What:="Date").Offset(1, 0) icol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, icol).End(xlUp)) Val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & _ fst.Address & "))," & fst.Address & "0)") Set dmin = fst(Val) MsgBox dmin End Sub -- tj "Bob Phillips" wrote: Is that ob the val line or the Set rng line? Have you declared these variables at all, if so to what? -- HTH Bob Phillips "tjtjjtjt" wrote in message ... Thanks, Bob. I'm getting a Compile Error when I test this code. It tells me that a function call on the left side of an equation must return a variant or an object. -- tj "Bob Phillips" wrote: This works for me iCol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, iCol).End(xlUp)) val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & fst.Address & "))," & fst.Address & "0)") Set DMin = fst(val) -- HTH Bob Phillips "tjtjjtjt" wrote in message ... I am looking in a range of dates to find the minimum date. Once it is found, I will find what weekday it is. If it isn't a Sunday, I go to the smallest Sunday larger than the Minimum date to set the starting point for my code. I have everything working, except I can't seem to transfer the cell with the minimum date to a range variable. This was my latest attempt: Set rng = Range(fst.Address & ":A" & Cells(Rows.Count, "A").End(xlUp).Row) Set dmin = rng.Find(Application.WorksheetFunction.Min(rng)) dmin is always set to Nothing. What do I need to do to set dmin as the cell containing the minimum date? Also, since it is possible that "fst" may not be in column A, is there a more flexible way to set my "rng" variable? Thanks, -- tj |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Sunday with min value
Norman,
Thanks for that alteration; it did the trick. -- tj "Norman Jones" wrote: Hi Tjtjjtjt Try this slight modification: Sub test() Dim icol As Integer Dim fst As Range, rng As Range, dmin As Range Dim myVal As Long Set fst = Cells.Find(What:="Date").Offset(1, 0) icol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, icol).End(xlUp)) myVal = Evaluate("MATCH(MIN(IF(WEEKDAY(" & _ rng.Address & ")=1," & rng.Address & "))," _ & rng.Address & "0)") Set dmin = rng(myVal) MsgBox dmin.Value End Sub --- Regards, Norman "tjtjjtjt" wrote in message ... Below is what I have been using to test with since your initial response. I get the error message with the line that starts "val =." This is the error: "function call on the left side of an equation must return a variant or an object." Thank you for your continuing assistance. Sub test() Dim icol As Integer Dim fst As Range, rng As Range, dmin As Range Set fst = Cells.Find(What:="Date").Offset(1, 0) icol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, icol).End(xlUp)) Val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & _ fst.Address & "))," & fst.Address & "0)") Set dmin = fst(Val) MsgBox dmin End Sub -- tj "Bob Phillips" wrote: Is that ob the val line or the Set rng line? Have you declared these variables at all, if so to what? -- HTH Bob Phillips "tjtjjtjt" wrote in message ... Thanks, Bob. I'm getting a Compile Error when I test this code. It tells me that a function call on the left side of an equation must return a variant or an object. -- tj "Bob Phillips" wrote: This works for me iCol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, iCol).End(xlUp)) val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & fst.Address & "))," & fst.Address & "0)") Set DMin = fst(val) -- HTH Bob Phillips "tjtjjtjt" wrote in message ... I am looking in a range of dates to find the minimum date. Once it is found, I will find what weekday it is. If it isn't a Sunday, I go to the smallest Sunday larger than the Minimum date to set the starting point for my code. I have everything working, except I can't seem to transfer the cell with the minimum date to a range variable. This was my latest attempt: Set rng = Range(fst.Address & ":A" & Cells(Rows.Count, "A").End(xlUp).Row) Set dmin = rng.Find(Application.WorksheetFunction.Min(rng)) dmin is always set to Nothing. What do I need to do to set dmin as the cell containing the minimum date? Also, since it is possible that "fst" may not be in column A, is there a more flexible way to set my "rng" variable? Thanks, -- tj |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the Sunday with min value
Bob,
Thanks for all the help. I got the help I needed for this job and to help me build solutions for a couple of other applications I need to start developing. -- tj "Bob Phillips" wrote: Is that ob the val line or the Set rng line? Have you declared these variables at all, if so to what? -- HTH Bob Phillips "tjtjjtjt" wrote in message ... Thanks, Bob. I'm getting a Compile Error when I test this code. It tells me that a function call on the left side of an equation must return a variant or an object. -- tj "Bob Phillips" wrote: This works for me iCol = fst.Column Set rng = Range(fst.Address, Cells(Rows.Count, iCol).End(xlUp)) val = Evaluate("MATCH(MIN(IF(WEEKDAY(" & fst.Address & ")=1," & fst.Address & "))," & fst.Address & "0)") Set DMin = fst(val) -- HTH Bob Phillips "tjtjjtjt" wrote in message ... I am looking in a range of dates to find the minimum date. Once it is found, I will find what weekday it is. If it isn't a Sunday, I go to the smallest Sunday larger than the Minimum date to set the starting point for my code. I have everything working, except I can't seem to transfer the cell with the minimum date to a range variable. This was my latest attempt: Set rng = Range(fst.Address & ":A" & Cells(Rows.Count, "A").End(xlUp).Row) Set dmin = rng.Find(Application.WorksheetFunction.Min(rng)) dmin is always set to Nothing. What do I need to do to set dmin as the cell containing the minimum date? Also, since it is possible that "fst" may not be in column A, is there a more flexible way to set my "rng" variable? Thanks, -- tj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last Sunday | Excel Worksheet Functions | |||
Sunday | Excel Worksheet Functions | |||
Sunday Function | Excel Worksheet Functions | |||
Help with looking the nearest Sunday | Excel Worksheet Functions | |||
Find the first Sunday for a given year? | Excel Worksheet Functions |