Weekday question

[Guest]

Hi there

It seems Im having a problem with weekday function.
I have a column of data values, some of those values are DATE function given
and then there are some others in text format (even some blanks).

Trough a macro i can identify which weekday is it, so to do an action
depending on it. Everything seems to work fine until I reach a cell with no
data (blank), then the action stops and i get a "Type mismatch Error"

How can i avoid this?, im includig the program searching for any kindda help

Thanx

Range("d12").Activate
i = 12
Do While i < 65

If Weekday(Cells(i, 2).Value) = vbMonday Or Weekday(Cells(i, 2).Value) =
vbTuesday Or Weekday(Cells(i, 2).Value) = vbWednesday Or Weekday(Cells(i,
2).Value) = vbThursday Or Weekday(Cells(i, 2).Value) = vbFriday Then

ActiveCell.Value = cxdce
i = i + 1
ActiveCell.Offset(1, 0).Activate

Else

i = i + 1
ActiveCell.Offset(1, 0).Activate
End If
Loop

 

[Rick Rothstein \(MVP - VB\)]

It seems Im having a problem with weekday function.

I have a column of data values, some of those values are DATE function
given
and then there are some others in text format (even some blanks).

Trough a macro i can identify which weekday is it, so to do an action
depending on it. Everything seems to work fine until I reach a cell with
no
data (blank), then the action stops and i get a "Type mismatch Error"

How can i avoid this?, im includig the program searching for any kindda
help

Thanx

Range("d12").Activate
i = 12
Do While i < 65

If Weekday(Cells(i, 2).Value) = vbMonday Or Weekday(Cells(i, 2).Value) =
vbTuesday Or Weekday(Cells(i, 2).Value) = vbWednesday Or Weekday(Cells(i,
2).Value) = vbThursday Or Weekday(Cells(i, 2).Value) = vbFriday Then

ActiveCell.Value = cxdce
i = i + 1
ActiveCell.Offset(1, 0).Activate

Else

i = i + 1
ActiveCell.Offset(1, 0).Activate
End If
Loop

Unless I misread something, the above code can be reduced to this...

Range("d12").Activate
i = 12
Do While i < 65
If Weekday(Cells(i, 2).Value, vbMonday) < 6 Then
ActiveCell.Value = cxdce
End If
i = i + 1
ActiveCell.Offset(1, 0).Activate
Loop

Now, to solve the blank cell problem, this should work...

Range("d12").Activate
i = 12
Do While i < 65
If Trim$(Cells(i,2).Value) <> "" Then
If Weekday(Cells(i, 2).Value, vbMonday) < 6 Then
ActiveCell.Value = cxdce
End If
i = i + 1
ActiveCell.Offset(1, 0).Activate
End If
Loop

Rick

 

[Rick Rothstein \(MVP - VB\)]

Now, to solve the blank cell problem, this should work...

Range("d12").Activate
i = 12
Do While i < 65
If Trim$(Cells(i,2).Value) <> "" Then
If Weekday(Cells(i, 2).Value, vbMonday) < 6 Then
ActiveCell.Value = cxdce
End If
i = i + 1
ActiveCell.Offset(1, 0).Activate
End If
Loop

Actually, in looking at your code more carefully, I think my addition should
look like this instead of how I originally posted it...

Range("d12").Activate
i = 12
Do While i < 65
If Trim$(Cells(i, 2).Value) <> "" Then
If Weekday(Cells(i, 2).Value, vbMonday) < 6 Then
ActiveCell.Value = cxdce
End If
End If
i = i + 1
ActiveCell.Offset(1, 0).Activate
Loop


Rick

 

[Don Guillett]

try this idea
Sub dodatesdon()
For i = 12 To Cells(Rows.Count, 2).End(xlUp).row
On Error Resume Next
Select Case Weekday(Cells(i, 2))
Case 2 To 6: x = "cxdce"
Case Else: x = ""
End Select
If Len(Trim(Cells(i, 2))) > 0 Then
Cells(i, "d") = x
Else
Cells(i, "d") = ""
End If
Next i
End Sub

 

[Guest]

Ok Rick that works ok

Thanx

Actually, in looking at your code more carefully, I think my addition should
look like this instead of how I originally posted it...

Range("d12").Activate
i = 12
Do While i < 65
If Trim$(Cells(i, 2).Value) <> "" Then
If Weekday(Cells(i, 2).Value, vbMonday) < 6 Then
ActiveCell.Value = cxdce
End If
End If
i = i + 1
ActiveCell.Offset(1, 0).Activate
Loop


Rick

 

[Guest]

Thanx Don Actually this worked better

Jepane

try this idea
Sub dodatesdon()
For i = 12 To Cells(Rows.Count, 2).End(xlUp).row
On Error Resume Next
Select Case Weekday(Cells(i, 2))
Case 2 To 6: x = "cxdce"
Case Else: x = ""
End Select
If Len(Trim(Cells(i, 2))) > 0 Then
Cells(i, "d") = x
Else
Cells(i, "d") = ""
End If
Next i
End Sub

 

[Don Guillett]

Glad to help.

 

[Rick Rothstein \(MVP - VB\)]

try this idea

Sub dodatesdon()
For i = 12 To Cells(Rows.Count, 2).End(xlUp).row
On Error Resume Next
Select Case Weekday(Cells(i, 2))
Case 2 To 6: x = "cxdce"
Case Else: x = ""
End Select
If Len(Trim(Cells(i, 2))) > 0 Then
Cells(i, "d") = x
Else
Cells(i, "d") = ""
End If
Next i
End Sub

I believe the following code will work the same as what you posted...

Sub dodatesdon()
For i = 12 To Cells(Rows.Count, 2).End(xlUp).Row
If Len(Trim(Cells(i, 2))) > 0 Then Cells(i, "d") = _
Choose(1 + Weekday(Cells(i, 2), vbMonday) \ 6, "cxdce", "")
Next i
End Sub

Rick

 

[Don Guillett]

It does

 

[Rick Rothstein \(MVP - VB\)]

try this idea

It does

Thanks for the confirmation. Just so we are clear, I didn't post that code
because I thought it was better than your... because, in all probability, it
isn't. The Choose function is not the fastest one in VBA's arsenal and it is
easy to see that your code is more readable. The reason I posted it is I
think it is important to see alternative methods of doing the same thing. I
believe people benefit from such exposure and become aware of techniques
and/or approaches that they would not normally think of.

I did want to make a comment about the code you did post, though. The On
Error Resume Next statement does not have to be "refreshed" on each loop of
your For-Next loop and, so, it can be placed in front of the For statement
rather than after it. Once issued, On Error Resume Next remains "alive"
until either an On Error GoTo 0 command is issued or the procedure of which
it is a part goes out of scope. To see that, here is a short snippet that
forces a Divide-By-Zero error to occur on each even iteration... the one On
Error Resume Next statement takes care of each error generated...

Dim X As Long
Dim Toggle As Long
On Error Resume Next
For X = 1 To 20
Toggle = 1 - Toggle
Debug.Print X / Toggle
Next

Rick