If statment error

rspalding

Active Member
Joined
Sep 4, 2009
Messages
280
I'm trying to add the "IF" statements to change my active sheet range. When doing this I get an error.

Set rng = Nothing

rng = if ActiveSheet.Range("P97").Value = "0" Then
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$73"
End With
End If
If ActiveSheet.Range("P97").Value > "0" Then
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$99"
End With
End If
If ActiveSheet.Range("P123").Value > "0" Then
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$125"
End With
End If
If ActiveSheet.Range("P149").Value > "0" Then
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$151"
End With
End If
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Does this work?

rng =

If ActiveSheet.Range("P97").Value = "0" Then
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$73"
End With
End If

Else

If ActiveSheet.Range("P97").Value > "0" Then
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$99"
End With
End If

Else

If ActiveSheet.Range("P123").Value > "0" Then
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$125"
End With
End If

Else

If ActiveSheet.Range("P149").Value > "0" Then
With ActiveSheet.PageSetup
ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$151"
End With
End If
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps

Code:
If ActiveSheet.Range("P97").Value = "0" Then
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$73"
ElseIf ActiveSheet.Range("P97").Value > "0" Then
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$99"
ElseIf ActiveSheet.Range("P123").Value > "0" Then
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$125"
ElseIf ActiveSheet.Range("P149").Value > "0" Then
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$151"
End If
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Replace the whole code with this:

Code:
If ActiveSheet.Range("P97").Value = 0 Then
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$73"
End If
If ActiveSheet.Range("P97").Value > 0 Then
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$99"
End If
If ActiveSheet.Range("P123").Value > 0 Then
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$125"
End If
If ActiveSheet.Range("P149").Value > 0 Then
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AF$151"
End If
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406

ADVERTISEMENT

Or, maybe...

Code:
    With ActiveSheet.PageSetup
        If Range("P97").Value = "0" Then
            .PrintArea = "$A$1:$AF$73"
        ElseIf Range("P97").Value > "0" Then
            .PrintArea = "$A$1:$AF$99"
        ElseIf Range("P123").Value > "0" Then
            .PrintArea = "$A$1:$AF$125"
        ElseIf Range("P149").Value > "0" Then
            .PrintArea = "$A$1:$AF$151"
        End If
    End With
 

rspalding

Active Member
Joined
Sep 4, 2009
Messages
280
This is the origional statement I'm trying to add the "If" statement to. It is the set rng that i want the "if" statement added to.

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set rng = Nothing
Set rng = ActiveSheet.Range("A1:AF73")
'You can also use a sheet name
'Set rng = Sheets("YourSheet").UsedRange
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Sorry but it isn't clear what you are trying to do.
 

rspalding

Active Member
Joined
Sep 4, 2009
Messages
280
VOG,

This is part of an e-mail program. I need the range to change depending on the data in the file so that it will capture the proper range to e-mail. I hope this clarifies what I'm trying to do.

Robert
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
So is it?

Code:
If ActiveSheet.Range("P97").Value = "0" Then
    Set Rng = Range("$A$1:$AF$73")
ElseIf ActiveSheet.Range("P97").Value > "0" Then
    Set Rng = Range("$A$1:$AF$99")
ElseIf ActiveSheet.Range("P123").Value > "0" Then
    Set Rng = Range("$A$1:$AF$125")
ElseIf ActiveSheet.Range("P149").Value > "0" Then
    Set Rng = Range("$A$1:$AF$151")
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top