Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Problem with IF Statements Within VBA Code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Problem with IF Statements Within VBA Code
AGAIN.... Midnight here and I can't seem to wrap my brain around the
problem I'm having with my code. I've posted it below and this is what
I'm trying to do. The code is designed to print out worksheets that
will calculate the prorated amount of rent while populating check
boxes. It worked fine until I tried adding the IF statements: IF H47
="X", then four copies of the worksheet will print out; IF H49 ="X",
then it should print out the three copies. The different cell
references represent two different programs. The problem is that my
code is now not working (with the two different IF statements). I have
a feeling that it has to do with the transition strip:

Exit Sub
Else
End If

....... but I can't seem to figure it out. Any help would be
fantastic!!!


Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
..Unprotect "led52not"
..Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..PrintOut
..Shapes("Text Box 1").Select
Selection.Characters.Text = ""
..Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select
[a1].Select
..Protect "led52not"

Exit Sub
Else
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
..Unprotect "led52not"
..Shapes("Text Box 1").Select
Selection.Characters.Text = ""
..Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select

End With
End If
End With
End If
End Sub


Thanks so much,

Golf
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Problem with IF Statements Within VBA Code
Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
..Unprotect "led52not"
..Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..PrintOut
..Shapes("Text Box 1").Select
Selection.Characters.Text = ""
..Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select
[a1].Select
..Protect "led52not"
End With
Exit Sub
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
..Unprotect "led52not"
..Shapes("Text Box 1").Select
Selection.Characters.Text = ""
..Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select

End With
End If

End Sub

--
Regards,
Tom Ogilvy

"golf4" wrote in message
om...
AGAIN.... Midnight here and I can't seem to wrap my brain around the
problem I'm having with my code. I've posted it below and this is what
I'm trying to do. The code is designed to print out worksheets that
will calculate the prorated amount of rent while populating check
boxes. It worked fine until I tried adding the IF statements: IF H47
="X", then four copies of the worksheet will print out; IF H49 ="X",
then it should print out the three copies. The different cell
references represent two different programs. The problem is that my
code is now not working (with the two different IF statements). I have
a feeling that it has to do with the transition strip:

Exit Sub
Else
End If

...... but I can't seem to figure it out. Any help would be
fantastic!!!


Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"

Exit Sub
Else
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If
End With
End If
End Sub


Thanks so much,

Golf


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 104
Default Problem with IF Statements Within VBA Code
Golf4,
Just to make things clearer for future reference.

In VBE (like most other programming languages) you MUST end For loops,
Withs, Ifs, etc in the reverse order to the order they were set up.

eg:
For A......
If J = 5 Then
For B.....
For C......
With XYZ
For K
Some code here
Next K 'Reverse order
End With 'Reverse order
Next C 'Reverse order
Next B 'Reverse order
End If 'Reverse order
Next A 'Reverse order

Otherwise you will not only confuse yourself, but the compiler as well.

For A....
For B....
some code here
Next A 'same order
Next B 'same order

will either not compile or, if it does, will give you some very strange
results.

HTH
Henry


"Tom Ogilvy" wrote in message
...
Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"
End With
Exit Sub
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If

End Sub

--
Regards,
Tom Ogilvy

"golf4" wrote in message
om...
AGAIN.... Midnight here and I can't seem to wrap my brain around the
problem I'm having with my code. I've posted it below and this is what
I'm trying to do. The code is designed to print out worksheets that
will calculate the prorated amount of rent while populating check
boxes. It worked fine until I tried adding the IF statements: IF H47
="X", then four copies of the worksheet will print out; IF H49 ="X",
then it should print out the three copies. The different cell
references represent two different programs. The problem is that my
code is now not working (with the two different IF statements). I have
a feeling that it has to do with the transition strip:

Exit Sub
Else
End If

...... but I can't seem to figure it out. Any help would be
fantastic!!!


Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"

Exit Sub
Else
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If
End With
End If
End Sub


Thanks so much,

Golf




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Problem with IF Statements Within VBA Code
Hi, Tom -

Thanks so much for the response. Your corrections of my code works
like a charm --- should really help out with further streamlining my
rent calculation tool.

I really appreciate the help.

Take care,

Golf



"Tom Ogilvy" wrote in message ...
Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"
End With
Exit Sub
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If

End Sub

--
Regards,
Tom Ogilvy

"golf4" wrote in message
om...
AGAIN.... Midnight here and I can't seem to wrap my brain around the
problem I'm having with my code. I've posted it below and this is what
I'm trying to do. The code is designed to print out worksheets that
will calculate the prorated amount of rent while populating check
boxes. It worked fine until I tried adding the IF statements: IF H47
="X", then four copies of the worksheet will print out; IF H49 ="X",
then it should print out the three copies. The different cell
references represent two different programs. The problem is that my
code is now not working (with the two different IF statements). I have
a feeling that it has to do with the transition strip:

Exit Sub
Else
End If

...... but I can't seem to figure it out. Any help would be
fantastic!!!


Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"

Exit Sub
Else
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If
End With
End If
End Sub


Thanks so much,

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

Advanced Search
Display Modes
Linear Mode Linear 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
Multiple If Statements or Macro's in Worksheet:View Code Ben Dummar Excel Discussion (Misc queries) 4 December 29th 06 12:31 AM
Problem with IF statements Bob@HP Excel Discussion (Misc queries) 1 April 20th 06 05:13 PM
I'm having a really strange problem with 3 if statements. Marc Excel Worksheet Functions 4 April 5th 06 02:36 PM
IF statements/formula problem Admiral70 Excel Worksheet Functions 15 August 23rd 05 09:34 PM
Better Way to Code IF Statements? TheRobsterUK Excel Discussion (Misc queries) 4 July 18th 05 03:37 PM


All times are GMT +1. The time now is 08:48 AM.

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