Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 96
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,805
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 96
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 96
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 915
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Posting---If function problem again chrisnsmith Excel Discussion (Misc queries) 0 February 7th 09 05:35 AM
Problem Posting Replies JorgeG.ACT Excel Discussion (Misc queries) 1 June 19th 07 02:21 AM
Posting problem? T. Valko Excel Discussion (Misc queries) 7 December 1st 06 06:04 AM
the owner of posting should be able to delete the posting Mahendra Excel Discussion (Misc queries) 7 August 8th 05 07:21 PM
Posting in the next row Greg Excel Worksheet Functions 1 January 18th 05 09:57 PM


All times are GMT +1. The time now is 05:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"