| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
| Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
| Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
| Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
| copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
Linear Mode
