I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
..Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures") _
..Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31 worksheets
that need to be updated daily. So I have placed all worksheets to update in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
..Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
..Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures") _
..Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

Hi

Either use:

If A1= B1 Then call MySub

or

if A1=B1 then
Call MySub
else
'Else and ElseIf is optional
'Do whatEver
End if

Your statement should look like:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
..Cells (1, 1). Value = Date -3 Then
Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures") _
..Cells (1, 1).Value = Date - 1 Then
Call UpdateForms
End If

Regards,
Per

"chrisnsmith" skrev i meddelelsen
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures") _
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31
worksheets
that need to be updated daily. So I have placed all worksheets to update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures") _
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

If Weekday(Date, vbMonday) = 1 And _
Worksheets("Cus Futures").Cells (1, 1). Value = Date -3 Then
Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And _
Worksheets("Cus Futures").Cells (1, 1).Value = Date - 1 Then
Call UpdateForms
'etc.


--
__________________________________
HTH

Bob

"chrisnsmith" wrote in message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures") _
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31
worksheets
that need to be updated daily. So I have placed all worksheets to update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures") _
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

Something is wrong with the first part of your ElseIf statement, namely this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you trying to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures") _
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31
worksheets
that need to be updated daily. So I have placed all worksheets to update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures") _
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement, namely this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you trying to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures") _
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31
worksheets
that need to be updated daily. So I have placed all worksheets to update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures") _
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

You can't do it that way... you can't do a double test in a single logical
expression. However, VB's Weekday function calculate starting with any day
of the week, so let's start with Tuesday. Give this ElseIf statement a
try...

ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus Futures").Cells(1,
1).Value = Date -1 Then

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement, namely
this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you trying
to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures") _
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31
worksheets
that need to be updated daily. So I have placed all worksheets to
update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures") _
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

Ok, I made the changes to the Elseif statement.

Now, how do I put my procedure(UpdateForms) in a separate module and have it
called from the (This Workbook) module?

"Rick Rothstein" wrote:

You can't do it that way... you can't do a double test in a single logical
expression. However, VB's Weekday function calculate starting with any day
of the week, so let's start with Tuesday. Give this ElseIf statement a
try...

ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus Futures").Cells(1,
1).Value = Date -1 Then

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement, namely
this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you trying
to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures") _
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31
worksheets
that need to be updated daily. So I have placed all worksheets to
update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures") _
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

I'm guessing UpdateForms is a subroutine (you didn't show us your complete
code), although I am not sure how self-contained it is (does it have an
argument list?) or what its dependencies are (for example, do you have your
ranges qualified or not?). Will other subroutines, functions and/or event
procedures from other Modules (worksheet code windows included) call it or
will it be called only from one worksheet code window? If only one... then
just put UpdateForms in that code window too. If more than one... click
Insert/Module and put it in that code window (but now you will have to
qualify your ranges with worksheet references, probably ActiveWindow, but
that is only a guess without seeing your code). Where you put code, and how
you structure it, depends on how it is to be used... you haven't really
given us enough to know what your intentions are. As for calling it from any
other procedure... just use its name (and pass any arguments it may have
into it). You might want to look up the Sub Statement and the Function
Statement in VB's help files to get a better handle on calling subroutines
and functions.

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
Ok, I made the changes to the Elseif statement.

Now, how do I put my procedure(UpdateForms) in a separate module and have
it
called from the (This Workbook) module?

"Rick Rothstein" wrote:

You can't do it that way... you can't do a double test in a single
logical
expression. However, VB's Weekday function calculate starting with any
day
of the week, so let's start with Tuesday. Give this ElseIf statement a
try...

ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus
Futures").Cells(1,
1).Value = Date -1 Then

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement,
namely
this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you
trying
to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures")
_
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31
worksheets
that need to be updated daily. So I have placed all worksheets to
update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures")
_
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

This is the procedure I want to Call. I've only listed 6 for simplicty, but
I have 31 worksheets total.
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
'
'
Sheets("Cus Calls").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("Cus Puts").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("House Options").Select
Range("H9:I50").Copy Range("D9:E50")
Range("F9:K50").ClearContents
Range("K2") = Date
'
'
Sheets("Cus Indexes").Select
Range("F9:G50").Copy Range("B9:G50")
Range("D9:K50").ClearContents
Range("K2") = Date

What I want to do is place this in a separate module named UpdateForms and
call it from the main module.
For example If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms.


"Rick Rothstein" wrote:

I'm guessing UpdateForms is a subroutine (you didn't show us your complete
code), although I am not sure how self-contained it is (does it have an
argument list?) or what its dependencies are (for example, do you have your
ranges qualified or not?). Will other subroutines, functions and/or event
procedures from other Modules (worksheet code windows included) call it or
will it be called only from one worksheet code window? If only one... then
just put UpdateForms in that code window too. If more than one... click
Insert/Module and put it in that code window (but now you will have to
qualify your ranges with worksheet references, probably ActiveWindow, but
that is only a guess without seeing your code). Where you put code, and how
you structure it, depends on how it is to be used... you haven't really
given us enough to know what your intentions are. As for calling it from any
other procedure... just use its name (and pass any arguments it may have
into it). You might want to look up the Sub Statement and the Function
Statement in VB's help files to get a better handle on calling subroutines
and functions.

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
Ok, I made the changes to the Elseif statement.

Now, how do I put my procedure(UpdateForms) in a separate module and have
it
called from the (This Workbook) module?

"Rick Rothstein" wrote:

You can't do it that way... you can't do a double test in a single
logical
expression. However, VB's Weekday function calculate starting with any
day
of the week, so let's start with Tuesday. Give this ElseIf statement a
try...

ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus
Futures").Cells(1,
1).Value = Date -1 Then

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement,
namely
this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you
trying
to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus Futures")
_
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have 31
worksheets
that need to be updated daily. So I have placed all worksheets to
update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus Futures")
_
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

Do you want ALL of those ClearContents to run when the UpdateForms procedure
is called, or only a SINGLE one which you specify? For instances, in your
example...

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms

if the condition is met, do you want all the sheets processed (contents
cleared) or only the "Cus Futures" one?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
This is the procedure I want to Call. I've only listed 6 for simplicty,
but
I have 31 worksheets total.
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
'
'
Sheets("Cus Calls").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("Cus Puts").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("House Options").Select
Range("H9:I50").Copy Range("D9:E50")
Range("F9:K50").ClearContents
Range("K2") = Date
'
'
Sheets("Cus Indexes").Select
Range("F9:G50").Copy Range("B9:G50")
Range("D9:K50").ClearContents
Range("K2") = Date

What I want to do is place this in a separate module named UpdateForms and
call it from the main module.
For example If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms.


"Rick Rothstein" wrote:

I'm guessing UpdateForms is a subroutine (you didn't show us your
complete
code), although I am not sure how self-contained it is (does it have an
argument list?) or what its dependencies are (for example, do you have
your
ranges qualified or not?). Will other subroutines, functions and/or event
procedures from other Modules (worksheet code windows included) call it
or
will it be called only from one worksheet code window? If only one...
then
just put UpdateForms in that code window too. If more than one... click
Insert/Module and put it in that code window (but now you will have to
qualify your ranges with worksheet references, probably ActiveWindow, but
that is only a guess without seeing your code). Where you put code, and
how
you structure it, depends on how it is to be used... you haven't really
given us enough to know what your intentions are. As for calling it from
any
other procedure... just use its name (and pass any arguments it may have
into it). You might want to look up the Sub Statement and the Function
Statement in VB's help files to get a better handle on calling
subroutines
and functions.

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
Ok, I made the changes to the Elseif statement.

Now, how do I put my procedure(UpdateForms) in a separate module and
have
it
called from the (This Workbook) module?

"Rick Rothstein" wrote:

You can't do it that way... you can't do a double test in a single
logical
expression. However, VB's Weekday function calculate starting with any
day
of the week, so let's start with Tuesday. Give this ElseIf statement a
try...

ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus
Futures").Cells(1,
1).Value = Date -1 Then

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement,
namely
this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you
trying
to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in
message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus
Futures")
_
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have
31
worksheets
that need to be updated daily. So I have placed all worksheets to
update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus
Futures")
_
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

I want to clear contents on all sheets

"Rick Rothstein" wrote:

Do you want ALL of those ClearContents to run when the UpdateForms procedure
is called, or only a SINGLE one which you specify? For instances, in your
example...

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms

if the condition is met, do you want all the sheets processed (contents
cleared) or only the "Cus Futures" one?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
This is the procedure I want to Call. I've only listed 6 for simplicty,
but
I have 31 worksheets total.
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
'
'
Sheets("Cus Calls").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("Cus Puts").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("House Options").Select
Range("H9:I50").Copy Range("D9:E50")
Range("F9:K50").ClearContents
Range("K2") = Date
'
'
Sheets("Cus Indexes").Select
Range("F9:G50").Copy Range("B9:G50")
Range("D9:K50").ClearContents
Range("K2") = Date

What I want to do is place this in a separate module named UpdateForms and
call it from the main module.
For example If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms.


"Rick Rothstein" wrote:

I'm guessing UpdateForms is a subroutine (you didn't show us your
complete
code), although I am not sure how self-contained it is (does it have an
argument list?) or what its dependencies are (for example, do you have
your
ranges qualified or not?). Will other subroutines, functions and/or event
procedures from other Modules (worksheet code windows included) call it
or
will it be called only from one worksheet code window? If only one...
then
just put UpdateForms in that code window too. If more than one... click
Insert/Module and put it in that code window (but now you will have to
qualify your ranges with worksheet references, probably ActiveWindow, but
that is only a guess without seeing your code). Where you put code, and
how
you structure it, depends on how it is to be used... you haven't really
given us enough to know what your intentions are. As for calling it from
any
other procedure... just use its name (and pass any arguments it may have
into it). You might want to look up the Sub Statement and the Function
Statement in VB's help files to get a better handle on calling
subroutines
and functions.

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
Ok, I made the changes to the Elseif statement.

Now, how do I put my procedure(UpdateForms) in a separate module and
have
it
called from the (This Workbook) module?

"Rick Rothstein" wrote:

You can't do it that way... you can't do a double test in a single
logical
expression. However, VB's Weekday function calculate starting with any
day
of the week, so let's start with Tuesday. Give this ElseIf statement a
try...

ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus
Futures").Cells(1,
1).Value = Date -1 Then

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement,
namely
this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are you
trying
to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in
message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus
Futures")
_
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I have
31
worksheets
that need to be updated daily. So I have placed all worksheets to
update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus
Futures")
_
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an If.

What am I doing wrong and how do I correct it?

In that case, put a Sub..End Sub around it, put it in a Module
(Insert/Module from VB's menu bar) and then call it just a you showed in
your example statement (you can either use the Call keyword or not). I would
note, though, that to do what you showed in your code, it is not necessary
to physically Select each sheet to work on them... you can just qualify the
lines with the sheet reference individually; or (my preference) use a
With..End With housing around the lines instead and "dot" the references.
So...

Sub UpdateForms()
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
'
With Sheets("Cus Calls")
.Range("G9:H50").Copy .Range("C9:D50")
.Range("E9:J50").ClearContents
.Range("J2") = Date
End With
'
' <<and so on
'
End Sub

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
I want to clear contents on all sheets

"Rick Rothstein" wrote:

Do you want ALL of those ClearContents to run when the UpdateForms
procedure
is called, or only a SINGLE one which you specify? For instances, in your
example...

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms

if the condition is met, do you want all the sheets processed (contents
cleared) or only the "Cus Futures" one?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
This is the procedure I want to Call. I've only listed 6 for
simplicty,
but
I have 31 worksheets total.
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
'
'
Sheets("Cus Calls").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("Cus Puts").Select
Range("G9:H50").Copy Range("C9:D50")
Range("E9:J50").ClearContents
Range("J2") = Date
'
'
Sheets("House Options").Select
Range("H9:I50").Copy Range("D9:E50")
Range("F9:K50").ClearContents
Range("K2") = Date
'
'
Sheets("Cus Indexes").Select
Range("F9:G50").Copy Range("B9:G50")
Range("D9:K50").ClearContents
Range("K2") = Date

What I want to do is place this in a separate module named UpdateForms
and
call it from the main module.
For example If Weekday(Date, vbMonday) = 1 And Worksheets("Cus
Futures") _
.Cells(13, 2).Value = Date - 3 Then Call UpateForms.


"Rick Rothstein" wrote:

I'm guessing UpdateForms is a subroutine (you didn't show us your
complete
code), although I am not sure how self-contained it is (does it have
an
argument list?) or what its dependencies are (for example, do you have
your
ranges qualified or not?). Will other subroutines, functions and/or
event
procedures from other Modules (worksheet code windows included) call
it
or
will it be called only from one worksheet code window? If only one...
then
just put UpdateForms in that code window too. If more than one...
click
Insert/Module and put it in that code window (but now you will have to
qualify your ranges with worksheet references, probably ActiveWindow,
but
that is only a guess without seeing your code). Where you put code,
and
how
you structure it, depends on how it is to be used... you haven't
really
given us enough to know what your intentions are. As for calling it
from
any
other procedure... just use its name (and pass any arguments it may
have
into it). You might want to look up the Sub Statement and the Function
Statement in VB's help files to get a better handle on calling
subroutines
and functions.

--
Rick (MVP - Excel)


"chrisnsmith" wrote in message
...
Ok, I made the changes to the Elseif statement.

Now, how do I put my procedure(UpdateForms) in a separate module and
have
it
called from the (This Workbook) module?

"Rick Rothstein" wrote:

You can't do it that way... you can't do a double test in a single
logical
expression. However, VB's Weekday function calculate starting with
any
day
of the week, so let's start with Tuesday. Give this ElseIf
statement a
try...

ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus
Futures").Cells(1,
1).Value = Date -1 Then

--
Rick (MVP - Excel)


"chrisnsmith" wrote in
message
...
If it is Tuesday thru Friday

"Rick Rothstein" wrote:

Something is wrong with the first part of your ElseIf statement,
namely
this
part...

Weekday(Date,vbMonday) = 2 < 7

What is "equal 2, less than 7" supposed to mean? What test are
you
trying
to
perform?

--
Rick (MVP - Excel)


"chrisnsmith" wrote in
message
...
I'm having a problem with the If and Elseif functions.
When I enter the following everything works fine.


If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells(13, 2).Value = Date - 3 Then


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


ElseIf Weekday(Date,vbMonday) = 2 < 7 And Worksheets("Cus
Futures")
_
.Cells(1, 1).Value = Date -1 Then

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


I only list two worksheets for simplicities sake, however I
have
31
worksheets
that need to be updated daily. So I have placed all worksheets
to
update
in
a module called UpdateForms and did the following.

I changed my If statement to;
If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
End if

When I run this I get a message End If without a block If.

I changed both my If statement and Elseif statement to:

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures") _
.Cells (1, 1). Value = Date -3 Then Call UpdateForms
Elseif Weekday(Date, vbMonday) =2 < 7 And Worksheets("Cus
Futures")
_
.Cells (1, 1).Value = Date - 1 Then Call UpdateForms

When I run this code I get a similar message Else without an
If.

What am I doing wrong and how do I correct it?

Ok, I've done as you suggested. I put my procedure in a module as follows:

Sub UpdateForms()
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
'
With Sheets("Cus Calls")
.Range("G9:H50").Copy Range("C9:D50")
.Range("E9:J50").ClearContents
.Range("J2") = Date
End With
'
With Sheets("Cus Puts")
.Range("G9:H50").Copy Range("C9:D50")
.Range("E9:J50").ClearContents
.Range("J2") = Date
End With
'
With Sheets("House Options")
.Range("H9:I50").Copy Range("D9:E50")
.Range("F9:K50").ClearContents
.Range("K2") = Date
End With
'
With Sheets("Cus Indexes")
.Range("F9:G50").Copy Range("B9:G50")
.Range("D9:K50").ClearContents
.Range("K2") = Date
End With
End Sub


I then changed my statements in the main module (This Workbook) to the
following:

Private Sub Workbook_Open()
If Weekday(Date, vbMonday) = 1 _
And Worksheets("Cus Futures").Range("M2").Value = Date - 3 Then Call
UpdateForms



ElseIf Weekday(Date, vbTuesday) < 5 _
And Worksheets("Cus Futures").Range("M2").Value = Date - 1 Then Call
UpdateForms

End If
End Sub

Now when I try to run the code I get a message, Else without an If, it
appears to me
that the If statement is not calling the UpdateForms procedure.
Am I right, and if so what do I need to do?

First off, you missed some "dots" (always a good idea to copy/paste posted code rather than retype it)... the destination Range function for the Copy statements should have a "dot" in front of it also.

Second, there are 2 types of If..Then constructions and you have mixed their syntax together. The first is a single line If..Then such as this...

If <Logical Expression Then <Single Statement To Run

Note that there is no End If statement used with the single line form. You can't do ElseIf conditional testing with the single line form of the the If..Then statement. The other form is an If..Then block such as this...

If <Logical Expression Then
<Single or Multiple Statements To Run
End If

With the block form of the If..Then construction, you can have ElseIf and Else blocks of code too. For example...

If <Logical Expression Then
<Single or Multiple Statements To Run
ElseIf <Logical Expression Then
<Single or Multiple Statements To Run
Else
<Single or Multiple Statements To Run
End If

where there could be multiple ElseIf sections of code if needed (only one Else though). So, your code should have been constructed like this...

If Weekday(Date, vbMonday) = 1 And Worksheets("Cus Futures").Range("M2").Value = Date - 3 Then
Call UpdateForms
ElseIf Weekday(Date, vbTuesday) < 5 And Worksheets("Cus Futures").Range("M2").Value = Date - 1 Then
Call UpdateForms
End If

--
Rick (MVP - Excel)


--
Rick (MVP - Excel)


"chrisnsmith" wrote in message ...
Ok, I've done as you suggested. I put my procedure in a module as follows:

Sub UpdateForms()
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
'
With Sheets("Cus Calls")
.Range("G9:H50").Copy Range("C9:D50")
.Range("E9:J50").ClearContents
.Range("J2") = Date
End With
'
With Sheets("Cus Puts")
.Range("G9:H50").Copy Range("C9:D50")
.Range("E9:J50").ClearContents
.Range("J2") = Date
End With
'
With Sheets("House Options")
.Range("H9:I50").Copy Range("D9:E50")
.Range("F9:K50").ClearContents
.Range("K2") = Date
End With
'
With Sheets("Cus Indexes")
.Range("F9:G50").Copy Range("B9:G50")
.Range("D9:K50").ClearContents
.Range("K2") = Date
End With
End Sub


I then changed my statements in the main module (This Workbook) to the
following:

Private Sub Workbook_Open()
If Weekday(Date, vbMonday) = 1 _
And Worksheets("Cus Futures").Range("M2").Value = Date - 3 Then Call
UpdateForms



ElseIf Weekday(Date, vbTuesday) < 5 _
And Worksheets("Cus Futures").Range("M2").Value = Date - 1 Then Call
UpdateForms

End If
End Sub

Now when I try to run the code I get a message, Else without an If, it
appears to me
that the If statement is not calling the UpdateForms procedure.
Am I right, and if so what do I need to do?