ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Problem with IF Statements Within VBA Code (https://www.excelbanter.com/excel-programming/294620-problem-if-statements-within-vba-code.html)

golf4
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

Tom Ogilvy
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

Henry[_5_]
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

golf4
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


All times are GMT +1. The time now is 10:16 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com