| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weekday question
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weekday question
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weekday question
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weekday question
Ok Rick that works ok
Thanx "Rick Rothstein (MVP - VB)" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weekday question
Thanx Don Actually this worked better
Jepane "Don Guillett" wrote: 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 SalesAid Software "Jepane" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weekday question
Glad to help. -- Don Guillett SalesAid Software "Jepane" wrote in message ... Thanx Don Actually this worked better Jepane "Don Guillett" wrote: 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 SalesAid Software "Jepane" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weekday question
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weekday question
|
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Weekday question
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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
| Weekday | Excel Discussion (Misc queries) | |||
| WEEKDAY question on year | Excel Discussion (Misc queries) | |||
| Weekday | Excel Worksheet Functions | |||
| WEEKDAY using IF | Excel Discussion (Misc queries) | |||
Linear Mode
