Still having problems with If --Elseif

C

chrisnsmith

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

Per Jessen

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
 
B

Bob Phillips

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.
 
R

Rick Rothstein

Something is wrong with the first part of your ElseIf statement, namely this
part...
Weekday(Date,vbMonday) = 2 < 7
Click to expand...

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

chrisnsmith

If it is Tuesday thru Friday

Rick Rothstein said:
Something is wrong with the first part of your ElseIf statement, namely this
part...


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

Rick Rothstein

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
 
C

chrisnsmith

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

Rick Rothstein

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

chrisnsmith

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.
 
R

Rick Rothstein

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
Click to expand...

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

chrisnsmith

I want to clear contents on all sheets>

Rick Rothstein said:
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 the condition is met, do you want all the sheets processed (contents
cleared) or only the "Cus Futures" one?
Click to expand...
 
R

Rick Rothstein

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
 
C

chrisnsmith

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

Rick Rothstein

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
 

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

Similar Threads

Posting---If function problem again 6
VB Code 6
Running code on workdays 2
Select Case code error 8
VBA Coding Help for Beginner 0
Need Help with a VBA subroutine 0
VBA for Dependents shortcut menu 0
Development combinations with exclusion of columns 0

Top