Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Still having problems with If --Elseif
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 96
Default Still having problems with If --Elseif
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?


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,533
Default Still having problems with If --Elseif
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?



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,420
Default Still having problems with If --Elseif

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?




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,934
Default Still having problems with If --Elseif
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?



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 96
Default Still having problems with If --Elseif
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?




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,934
Default Still having problems with If --Elseif
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?





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 96
Default Still having problems with If --Elseif
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?






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,934
Default Still having problems with If --Elseif
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?







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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
Elseif? ibrokit Excel Worksheet Functions 5 November 25th 08 04:28 PM
Compile Error ElseIf Without If.........But there is an If ! ??? dim Excel Discussion (Misc queries) 11 December 30th 07 07:01 PM
if elseif flow23 Excel Discussion (Misc queries) 0 November 14th 05 02:09 PM
elseif formula macrodummy Excel Discussion (Misc queries) 1 October 7th 05 01:43 PM
SP3 problems Ron Weaver Excel Discussion (Misc queries) 3 March 19th 05 04:57 PM


All times are GMT +1. The time now is 03:17 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement