If statment error

[rspalding]

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

 

[Special-K99]

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]

Perhaps

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]

Replace the whole code with this:

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]

Or, maybe...

    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]

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]

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

 

[rspalding]

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]

So is it?

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

 

[rspalding]

Yes but the "dim as range" is what is not working with it.