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

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

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

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

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

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

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

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