|
VB Code
What's wrong with this code?
Private Sub UpdateForm200() If Cells(13, 2) = Today - 3 Then GoTo My_Procedure ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure End If My_Procedu Sheets("Cus Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date ' ' Sheets("House Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date |
VB Code
Hi Chris
you are not saying what to do if the dates aren't 1 day or 3 days before today. Private Sub UpdateForm200() If Cells(13, 2) = Today - 3 Then GoTo My_Procedure ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure Exit Sub End If your code -- Regards Roger Govier "chrisnsmith" wrote in message ... What's wrong with this code? Private Sub UpdateForm200() If Cells(13, 2) = Today - 3 Then GoTo My_Procedure ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure End If My_Procedu Sheets("Cus Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date ' ' Sheets("House Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date |
VB Code
today is for functions. Use DATE as you did in part of the code. also get
rid of selections. If Cells(13, 2) = date - 3 or cells(13,2)=date-1 Then GoTo My_Procedure My_Procedu with Sheets("Cus Futures") . Range(" H9:I50").Copy . Range("D9:E50") . Range("F9:I50").ClearContents . Range("M2") = Date end with with Sheets("House Futures") . Range(" H9:I50").Copy . Range("D9:E50") . Range("F9:I50").ClearContents . Range("M2") = Date end with -- Don Guillett Microsoft MVP Excel SalesAid Software "chrisnsmith" wrote in message ... What's wrong with this code? Private Sub UpdateForm200() If Cells(13, 2) = Today - 3 Then GoTo My_Procedure ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure End If My_Procedu Sheets("Cus Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date ' ' Sheets("House Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date |
VB Code
I realized the code I posted wouldn't do what I wanted anyway. What I want
to do is run My_Procedure only on weekdays not weekends. I'm posting a new code, will it do what I want? Private Sub UpdateForm200() If Weekday(Date) = vbSaturday Or Weekday(Date) = vbSunday Then End If ElseIf Cells(13, 2) = Date - 3 Or Cells(13, 2) = Date - 1 Then GoTo My_Procedure End If My_Procedu With Sheets("Cus Futures") Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date End With ' ' With Sheets("House Futures") Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date End With "Don Guillett" wrote: today is for functions. Use DATE as you did in part of the code. also get rid of selections. If Cells(13, 2) = date - 3 or cells(13,2)=date-1 Then GoTo My_Procedure My_Procedu with Sheets("Cus Futures") . Range(" H9:I50").Copy . Range("D9:E50") . Range("F9:I50").ClearContents . Range("M2") = Date end with with Sheets("House Futures") . Range(" H9:I50").Copy . Range("D9:E50") . Range("F9:I50").ClearContents . Range("M2") = Date end with -- Don Guillett Microsoft MVP Excel SalesAid Software "chrisnsmith" wrote in message ... What's wrong with this code? Private Sub UpdateForm200() If Cells(13, 2) = Today - 3 Then GoTo My_Procedure ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure End If My_Procedu Sheets("Cus Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date ' ' Sheets("House Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date |
VB Code
The code in My_Procedure will be executed no matter what day of the week it
is for the that macro. As written, the code needs an Exit Sub statement immediately in front of the My_Procedu label. However, I probably would just test the day of the week and exit the subroutine immediate if it is a weekday. Something like this... Sub Test() If Weekday(Date, vbMonday) 5 Then Exit Sub ' The My_Procedure code (label no longer needed) goes here MsgBox "H" End Sub -- Rick (MVP - Excel) "Don Guillett" wrote in message ... Try this instead. I noticed that you omitted the dots that are NECESSARY for the with......... Put em back or WITH won't work. Sub ttt() If Weekday(Date) < vbSaturday _ Or Weekday(Date) < vbSunday Then GoTo My_Procedure End If My_Procedu MsgBox "H" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "chrisnsmith" wrote in message ... I realized the code I posted wouldn't do what I wanted anyway. What I want to do is run My_Procedure only on weekdays not weekends. I'm posting a new code, will it do what I want? Private Sub UpdateForm200() If Weekday(Date) = vbSaturday Or Weekday(Date) = vbSunday Then End If ElseIf Cells(13, 2) = Date - 3 Or Cells(13, 2) = Date - 1 Then GoTo My_Procedure End If My_Procedu With Sheets("Cus Futures") Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date End With ' ' With Sheets("House Futures") Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date End With "Don Guillett" wrote: today is for functions. Use DATE as you did in part of the code. also get rid of selections. If Cells(13, 2) = date - 3 or cells(13,2)=date-1 Then GoTo My_Procedure My_Procedu with Sheets("Cus Futures") . Range(" H9:I50").Copy . Range("D9:E50") . Range("F9:I50").ClearContents . Range("M2") = Date end with with Sheets("House Futures") . Range(" H9:I50").Copy . Range("D9:E50") . Range("F9:I50").ClearContents . Range("M2") = Date end with -- Don Guillett Microsoft MVP Excel SalesAid Software "chrisnsmith" wrote in message ... What's wrong with this code? Private Sub UpdateForm200() If Cells(13, 2) = Today - 3 Then GoTo My_Procedure ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure End If My_Procedu Sheets("Cus Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date ' ' Sheets("House Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date |
VB Code
Or, to use OP original style
Sub ttt() If Weekday(Date) = vbSaturday _ Or Weekday(Date) = vbSunday Then Exit Sub End If MsgBox "H" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Rick Rothstein" wrote in message ... The code in My_Procedure will be executed no matter what day of the week it is for the that macro. As written, the code needs an Exit Sub statement immediately in front of the My_Procedu label. However, I probably would just test the day of the week and exit the subroutine immediate if it is a weekday. Something like this... Sub Test() If Weekday(Date, vbMonday) 5 Then Exit Sub ' The My_Procedure code (label no longer needed) goes here MsgBox "H" End Sub -- Rick (MVP - Excel) "Don Guillett" wrote in message ... Try this instead. I noticed that you omitted the dots that are NECESSARY for the with......... Put em back or WITH won't work. Sub ttt() If Weekday(Date) < vbSaturday _ Or Weekday(Date) < vbSunday Then GoTo My_Procedure End If My_Procedu MsgBox "H" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "chrisnsmith" wrote in message ... I realized the code I posted wouldn't do what I wanted anyway. What I want to do is run My_Procedure only on weekdays not weekends. I'm posting a new code, will it do what I want? Private Sub UpdateForm200() If Weekday(Date) = vbSaturday Or Weekday(Date) = vbSunday Then End If ElseIf Cells(13, 2) = Date - 3 Or Cells(13, 2) = Date - 1 Then GoTo My_Procedure End If My_Procedu With Sheets("Cus Futures") Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date End With ' ' With Sheets("House Futures") Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date End With "Don Guillett" wrote: today is for functions. Use DATE as you did in part of the code. also get rid of selections. If Cells(13, 2) = date - 3 or cells(13,2)=date-1 Then GoTo My_Procedure My_Procedu with Sheets("Cus Futures") . Range(" H9:I50").Copy . Range("D9:E50") . Range("F9:I50").ClearContents . Range("M2") = Date end with with Sheets("House Futures") . Range(" H9:I50").Copy . Range("D9:E50") . Range("F9:I50").ClearContents . Range("M2") = Date end with -- Don Guillett Microsoft MVP Excel SalesAid Software "chrisnsmith" wrote in message ... What's wrong with this code? Private Sub UpdateForm200() If Cells(13, 2) = Today - 3 Then GoTo My_Procedure ElseIf Cells(13, 2) = Today - 1 Then GoTo My_Procedure End If My_Procedu Sheets("Cus Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date ' ' Sheets("House Futures").Select Range(" H9:I50").Copy Range("D9:E50") Range("F9:I50").ClearContents Range("M2") = Date |
| All times are GMT +1. The time now is 09:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com