 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / June 2007Weekday 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 > It seems Im having a problem with weekday function. > I have a column of data values, some of those values are DATE function [quoted text clipped - 29 lines] > 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 > Now, to solve the blank cell problem, this should work... > [quoted text clipped - 9 lines] > 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 Ok Rick that works ok Thanx > > Now, to solve the blank cell problem, this should work... > > [quoted text clipped - 26 lines] > > Rick 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  SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Hi there > [quoted text clipped - 31 lines] > End If > Loop Thanx Don Actually this worked better Jepane > try this idea > Sub dodatesdon() [quoted text clipped - 47 lines] > > End If > > Loop Glad to help. SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > Thanx Don Actually this worked better > [quoted text clipped - 54 lines] >> > End If >> > Loop > try this idea > Sub dodatesdon() [quoted text clipped - 11 lines] > 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 It does SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >> try this idea >> Sub dodatesdon() [quoted text clipped - 22 lines] > > Rick >>> try this idea >>> Sub dodatesdon() [quoted text clipped - 22 lines] > > 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.