variable question

[Gary Keramidas]

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?

 

[Jezebel]

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.

 

[Ron Rosenfeld]

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:

 

[Gary Keramidas]

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

 

[Tom Ogilvy]

weekday("Monday") = vbMonday

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

 

[Gary Keramidas]

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.

 

[Tom Ogilvy]

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.

 

[Gary Keramidas]

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

 

[Tom Ogilvy]

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("Weekdays"),0)
if not iserror(res) then
wday = res + 1
end if
End with