want to use a dropdown with the days of the week. if i hard code this it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?
--


Gary

Can't be done. VBA is an compiled language (as opposed to an interpreted
language like vbScript), which has the consequence that the code has no
awareness of its own variable and constant names. That is, you can construct
"vbMonday" as a string; but there's no way to use that string to refer to a
variable or constant with that name.

If you need to convert "Monday" to vbMonday, you need to use some kind of
look up structure, case statement, or equivalent.



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
want to use a dropdown with the days of the week. if i hard code this it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?
--


Gary

On Tue, 15 Nov 2005 21:11:04 -0500, "Gary Keramidas" <GKeramidasATmsn.com
wrote:

want to use a dropdown with the days of the week. if i hard code this it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?

vbMonday is a constant equal to 2 (vbSunday=1 and so forth).

Since you have the spelled out day of the week in cell, it's just a matter of
translating it to the appropriate weekday number. So one way:

---------------------------------------------
weekdays = Array("Sunday", "Monday", "Tuesday", "Wednesday", _
"Thursday", "Friday", "Saturday")
WD = Application.WorksheetFunction.Match(cell, weekdays, 0)
---------------------------------------------
Then you can use:

If WD = vbMonday Then

------------------------


--ron

ok thanks, i think i'll resort to the if else thing

--


Gary


"Ron Rosenfeld" wrote in message
...
On Tue, 15 Nov 2005 21:11:04 -0500, "Gary Keramidas" <GKeramidasATmsn.com
wrote:

want to use a dropdown with the days of the week. if i hard code this it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?

vbMonday is a constant equal to 2 (vbSunday=1 and so forth).

Since you have the spelled out day of the week in cell, it's just a matter
of
translating it to the appropriate weekday number. So one way:

---------------------------------------------
weekdays = Array("Sunday", "Monday", "Tuesday", "Wednesday", _
"Thursday", "Friday", "Saturday")
WD = Application.WorksheetFunction.Match(cell, weekdays, 0)
---------------------------------------------
Then you can use:

If WD = vbMonday Then

------------------------


--ron

weekday("Monday") = vbMonday

gives me a type mismatch error. If that isn't what you meant, then
clarify.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
want to use a dropdown with the days of the week. if i hard code this it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?
--


Gary

there is a date in column A. if i use "If weekday(cell.value) = vbMonday
Then" the code works fine. so i have a dropdown in A2 with Monday thru
Saturday listed.
i wanted to use
"If cell.value = "vb" & Range.("A2").value. Then"
or some form of that with a variable but it didn't work.

i just used if/elseif statements.
--


Gary


"Tom Ogilvy" wrote in message
...
weekday("Monday") = vbMonday

gives me a type mismatch error. If that isn't what you meant, then
clarify.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
want to use a dropdown with the days of the week. if i hard code this it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?
--


Gary

vbSunday is 1 and vbSaturday is 7, so you can check against the numbers 1 to
7.

If that doesn't answer it, you would have to be much more specific about
what you are trying to check/how/when/why/what decision you ar e making,
because frankly,
"If cell.value = "vb" & Range.("A2").value. Then"

doesn't make much sense.
--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
there is a date in column A. if i use "If weekday(cell.value) = vbMonday
Then" the code works fine. so i have a dropdown in A2 with Monday thru
Saturday listed.
i wanted to use
"If cell.value = "vb" & Range.("A2").value. Then"
or some form of that with a variable but it didn't work.

i just used if/elseif statements.
--


Gary


"Tom Ogilvy" wrote in message
...
weekday("Monday") = vbMonday

gives me a type mismatch error. If that isn't what you meant, then
clarify.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
want to use a dropdown with the days of the week. if i hard code this
it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?
--


Gary

hopefully this will explain what i was trying to do.
here's the code snippet:

'A2 is the dropdown range
If UCase(Worksheets("report").Range("A2")) = "MONDAY" Then
wday = 2
ElseIf UCase(Worksheets("report").Range("A2")) = "TUESDAY" Then
wday = 3
ElseIf UCase(Worksheets("report").Range("A2")) = "WEDNESDAY" Then
wday = 4
ElseIf UCase(Worksheets("report").Range("A2")) = "THURSDAY" Then
wday = 5
ElseIf UCase(Worksheets("report").Range("A2")) = "FRIDAY" Then
wday = 6
Else
wday = 7
End If


With Worksheets(i) ' there are 12 of them, didn't post this loop
Set rng = .Range("A4:a34")
For Each cell In rng.Cells
If Not IsDate(cell.Value) Then
lastrow = lastrow - 1
End If
If Weekday(cell.Value) = wday Then ' * see below
Worksheets("report").Range("B" & lastrow + 1) = cell.Value
Worksheets("report").Range("C" & lastrow + 1) = cell.Offset(0, 1).Value
Worksheets("report").Range("D" & lastrow + 1) = cell.Offset(0, 4).Value
Worksheets("report").Range("E" & lastrow + 1) = cell.Offset(0, 7).Value
Worksheets("report").Range("F" & lastrow + 1) = cell.Offset(0, 10).Value
Worksheets("report").Range("G" & lastrow + 1) = cell.Offset(0, 13).Value
'Range("H" & lastrow + 1).Formula = "=sum(c" & lastrow + 1 & ":G" & lastrow
+ 1 & ")"
End If

*this is where i was trying to concatenate the "vb" and the value of A2
(vbMonday for example, if A2 contained Monday)
i was trying to use,
If Weekday(cell.Value) = "vb" & Range("A2") Then
but i used the if statements instead
--


Gary


"Tom Ogilvy" wrote in message
...
vbSunday is 1 and vbSaturday is 7, so you can check against the numbers 1
to
7.

If that doesn't answer it, you would have to be much more specific about
what you are trying to check/how/when/why/what decision you ar e making,
because frankly,
"If cell.value = "vb" & Range.("A2").value. Then"

doesn't make much sense.
--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
there is a date in column A. if i use "If weekday(cell.value) = vbMonday
Then" the code works fine. so i have a dropdown in A2 with Monday thru
Saturday listed.
i wanted to use
"If cell.value = "vb" & Range.("A2").value. Then"
or some form of that with a variable but it didn't work.

i just used if/elseif statements.
--


Gary


"Tom Ogilvy" wrote in message
...
weekday("Monday") = vbMonday

gives me a type mismatch error. If that isn't what you meant, then
clarify.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
want to use a dropdown with the days of the week. if i hard code this
it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?
--


Gary

Pick a date you know is Monday (ex: Dec 6, 2004)

Dim dt as Date, s as String
set dt = DateSerial(2004,12,6)
s = Ucase(Worksheets("Report").Range("A2').Text
for i = 1 to 5
if s = Ucase(Format(dt+i-1,"dddd")) then
wday = i + 1
exit for
end if
Next

If your dropdown box was populated from a range of cells that containd
Monday - Friday (assume range is named weekdays)

With Worksheets("Report")
res = Application.Match(.Range("A2").Text,Range("Weekday s"),0)
if not iserror(res) then
wday = res + 1
end if
End with



--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
hopefully this will explain what i was trying to do.
here's the code snippet:

'A2 is the dropdown range
If UCase(Worksheets("report").Range("A2")) = "MONDAY" Then
wday = 2
ElseIf UCase(Worksheets("report").Range("A2")) = "TUESDAY" Then
wday = 3
ElseIf UCase(Worksheets("report").Range("A2")) = "WEDNESDAY" Then
wday = 4
ElseIf UCase(Worksheets("report").Range("A2")) = "THURSDAY" Then
wday = 5
ElseIf UCase(Worksheets("report").Range("A2")) = "FRIDAY" Then
wday = 6
Else
wday = 7
End If


With Worksheets(i) ' there are 12 of them, didn't post this loop
Set rng = .Range("A4:a34")
For Each cell In rng.Cells
If Not IsDate(cell.Value) Then
lastrow = lastrow - 1
End If
If Weekday(cell.Value) = wday Then ' * see below
Worksheets("report").Range("B" & lastrow + 1) = cell.Value
Worksheets("report").Range("C" & lastrow + 1) = cell.Offset(0, 1).Value
Worksheets("report").Range("D" & lastrow + 1) = cell.Offset(0, 4).Value
Worksheets("report").Range("E" & lastrow + 1) = cell.Offset(0, 7).Value
Worksheets("report").Range("F" & lastrow + 1) = cell.Offset(0, 10).Value
Worksheets("report").Range("G" & lastrow + 1) = cell.Offset(0, 13).Value
'Range("H" & lastrow + 1).Formula = "=sum(c" & lastrow + 1 & ":G" &
lastrow
+ 1 & ")"
End If

*this is where i was trying to concatenate the "vb" and the value of A2
(vbMonday for example, if A2 contained Monday)
i was trying to use,
If Weekday(cell.Value) = "vb" & Range("A2") Then
but i used the if statements instead
--


Gary


"Tom Ogilvy" wrote in message
...
vbSunday is 1 and vbSaturday is 7, so you can check against the numbers
1
to
7.

If that doesn't answer it, you would have to be much more specific about
what you are trying to check/how/when/why/what decision you ar e making,
because frankly,
"If cell.value = "vb" & Range.("A2").value. Then"

doesn't make much sense.
--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
there is a date in column A. if i use "If weekday(cell.value) =
vbMonday
Then" the code works fine. so i have a dropdown in A2 with Monday thru
Saturday listed.
i wanted to use
"If cell.value = "vb" & Range.("A2").value. Then"
or some form of that with a variable but it didn't work.

i just used if/elseif statements.
--


Gary


"Tom Ogilvy" wrote in message
...
weekday("Monday") = vbMonday

gives me a type mismatch error. If that isn't what you meant, then
clarify.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
want to use a dropdown with the days of the week. if i hard code
this
it
works

If Weekday(cell.Value) = vbMonday Then

but if i try to concatenate "vb" and the value of the dropdown in A2
(Monday"), it doesn't.

what do i need to do to get it to work?
--


Gary