Posting---If function problem again

C

chrisnsmith

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_Procedure:
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_Procedure:
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
 
D

Dave Peterson

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_Procedure:
With Sheets("Cus Futures")
.Range("H9:I50").Copy _
destination:=worksheets("Someotherenamehere").Range("D9")
.Range("F9:I50").ClearContents
worksheets("Someotherenamehere").Range("M2").value = Date
End With

'
'
With Sheets("House Futures")
.Range(" H9:I50").Copy _
destination:=worksheets("Someotherenamehere").Range("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.
 
C

chrisnsmith

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?
 
C

chrisnsmith

I assumed you meant to place my_procedure in a separate module.
When I do that I get the message Else without an If.
 
S

smartin

chrisnsmith said:
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
 
D

Dave Peterson

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.


I assumed you meant to place my_procedure in a separate module.
When I do that I get the message Else without an If.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top