Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
Can someone explain to me why my Excel 2003 is not recognizing ( If )?
I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
Previous post was not complete...
I tested the following macro after entering 2/13/2009 in B13 and it showed the message "If is OK" Try it Sub Test() If Weekday(Date, vbMonday) = 5 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub My_Procedu Msgbox "IF is OK" 'With Sheets("Cus Futures") ' .Range(" H9:I50").Copy Range("D9:E50") ' .Range("F9:I50").ClearContents Range("M2") = Date 'End With End Sub "Sheeloo" wrote: Use call My_Procedure instead of Go to My_Procedure "chrisnsmith" wrote: Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
Use call My_Procedure instead of Go to My_Procedure
"chrisnsmith" wrote: Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
Previous post was not complete...
I meant put the statements after My_Procedure in a SUB and then call that SUB. I tested the following macro after entering 2/13/2009 in B13 and it showed the message "If is OK" Try it Sub Test() If Weekday(Date, vbMonday) = 5 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub My_Procedu Msgbox "IF is OK" 'With Sheets("Cus Futures") ' .Range(" H9:I50").Copy Range("D9:E50") ' .Range("F9:I50").ClearContents Range("M2") = Date 'End With End Sub "Sheeloo" wrote: Use call My_Procedure instead of Go to My_Procedure "chrisnsmith" wrote: Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
I would use Call and I'd qualify all those unqualified ranges:
Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 3 Then Call My_Procedure ElseIf Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 1 Then Call My_Procedure End If End Sub My_Procedu With Sheets("Cus Futures") .Range("H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ' ' With Sheets("House Futures") .Range(" H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9:E50") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ====== Change all those sheet names to what they need to be. chrisnsmith wrote: Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
Hopefully this will be my last question regarding this project.
When I run this code I get the message Sub or Function not defined. I'm a Visual Basic dummy and trying to work thru this thing. What is the process for defining My_Procedure? "Dave Peterson" wrote: I would use Call and I'd qualify all those unqualified ranges: Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 3 Then Call My_Procedure ElseIf Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 1 Then Call My_Procedure End If End Sub My_Procedu With Sheets("Cus Futures") .Range("H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ' ' With Sheets("House Futures") .Range(" H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9:E50") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ====== Change all those sheet names to what they need to be. chrisnsmith wrote: Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
Sub My_Procedure()
'do stuff with your code. End Sub chrisnsmith wrote: Hopefully this will be my last question regarding this project. When I run this code I get the message Sub or Function not defined. I'm a Visual Basic dummy and trying to work thru this thing. What is the process for defining My_Procedure? "Dave Peterson" wrote: I would use Call and I'd qualify all those unqualified ranges: Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 3 Then Call My_Procedure ElseIf Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 1 Then Call My_Procedure End If End Sub My_Procedu With Sheets("Cus Futures") .Range("H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ' ' With Sheets("House Futures") .Range(" H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9:E50") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ====== Change all those sheet names to what they need to be. chrisnsmith wrote: Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
I assumed you meant to place my_procedure in a separate module.
When I do that I get the message Else without an If. "Dave Peterson" wrote: Sub My_Procedure() 'do stuff with your code. End Sub chrisnsmith wrote: Hopefully this will be my last question regarding this project. When I run this code I get the message Sub or Function not defined. I'm a Visual Basic dummy and trying to work thru this thing. What is the process for defining My_Procedure? "Dave Peterson" wrote: I would use Call and I'd qualify all those unqualified ranges: Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 3 Then Call My_Procedure ElseIf Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 1 Then Call My_Procedure End If End Sub My_Procedu With Sheets("Cus Futures") .Range("H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ' ' With Sheets("House Futures") .Range(" H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9:E50") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ====== Change all those sheet names to what they need to be. chrisnsmith wrote: Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
chrisnsmith wrote:
Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub F1 on IF. If you code If a Then [statements] everything must appear on one line, and no EndIf is used. Use the "block" syntax" instead: If a Then b ElseIf c Then d Else e EndIf |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Posting---If function problem again
I didn't see anything that would cause that error in My_Procedure from what
you've posted. And the code I suggested for the workbook_open won't cause that error. Maybe it's time to share your code again. And indicate where the error occurs. And since this is related to the workbook_Open event (and won't be called by any other routine????), you could keep it in the ThisWorkbook module. chrisnsmith wrote: I assumed you meant to place my_procedure in a separate module. When I do that I get the message Else without an If. "Dave Peterson" wrote: Sub My_Procedure() 'do stuff with your code. End Sub chrisnsmith wrote: Hopefully this will be my last question regarding this project. When I run this code I get the message Sub or Function not defined. I'm a Visual Basic dummy and trying to work thru this thing. What is the process for defining My_Procedure? "Dave Peterson" wrote: I would use Call and I'd qualify all those unqualified ranges: Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 3 Then Call My_Procedure ElseIf Weekday(Date, vbMonday) = 2 _ And worksheets("Somesheetnamehere").Cells(1, 1).value = Date - 1 Then Call My_Procedure End If End Sub My_Procedu With Sheets("Cus Futures") .Range("H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ' ' With Sheets("House Futures") .Range(" H9:I50").Copy _ destination:=worksheets("Someotherenamehere").Rang e("D9:E50") .Range("F9:I50").ClearContents worksheets("Someotherenamehere").Range("M2").value = Date End With ====== Change all those sheet names to what they need to be. chrisnsmith wrote: Can someone explain to me why my Excel 2003 is not recognizing ( If )? I've tried several codes with the If function and my code won't run. The responses I received from my previous post asked that I post my code. This is the If...EndIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(13, 2) = Date - 3 Then GoTo My_Procedure End If End Sub 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 This is the If...ElseIf code. Private Sub Workbook_Open() If Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 3 Then GoTo My_Procedure ElseIf Weekday(Date, vbMonday) = 2 And Cells(1, 1) = Date - 1 Then GoTo My_Procedure End If End Sub 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Posting---If function problem again | Excel Discussion (Misc queries) | |||
Problem Posting Replies | Excel Discussion (Misc queries) | |||
Posting problem? | Excel Discussion (Misc queries) | |||
the owner of posting should be able to delete the posting | Excel Discussion (Misc queries) | |||
Posting in the next row | Excel Worksheet Functions |