Need help revising print range selection

[Joel Mills]

Below is the code I am currently using to set the print range, header title,
and put a border around the selected print area. I need some help revising
the code to allow for a variable range. The beginning cell will always be
A1 (1, 1), but the lower left cell that defines the boundary of the print
area will change depending on data that is imported into the worksheet.
Column B will determine the row that will defines the last cell. It should
be two rows below the Cell in Column B that Contains the text "Target %
Comp." The same row will determine will determine the column for the last
cell. It will be one column over from the last cell that contains data on
the row where "Target % Comp." is located. All other cells on this row will
be blank.

For example: Now "Target % Comp." is in Row "41" and the last data is in
Column "AS", by going 2 rows down and one row to the left the last cell
should be AT43 as listed in the code below.


Sub SetPrintRange()
With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = "$A$1:$AT$43"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
Range("$A$1:AT43").BorderAround Weight:=xlThin
End With
End Sub

 

[Guest]

I assume you meant "2 rows down and 1 column to the right":

Public Function MyPrintArea(SheetName As String) As Range

Dim FoundIt As Range

Set FoundIt = Sheets(SheetName).Range("B:B").Find("Target % Comp.")

If Not (FoundIt Is Nothing) Then

Set MyPrintArea = FoundIt.End(xlToRight)
Set MyPrintArea = MyPrintArea.Offset(2, 1).Range("A1")
Set MyPrintArea = Sheets(SheetName).Range("A1:" & MyPrintArea.Address)

End If

End Function

Sub SetPrintRange()

Dim PrintRange as Range
Set PrintRange = MyPrintArea("Charts")

With Worksheets("Charts").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHeader = "&""Arial,Regular""&22" & Range("b51").Value
PrintRange.BorderAround Weight:=xlThin
End With
End Sub

 

[Joel Mills]

Yes you are right. I've tried pasting the following code into a module.
Alt+F11, but I get a runtime error '5' invalid procedure call or argument.
When I click on debug ".PrintArea = PrintRange" is highlighted in yellow.
I'm not sure what I'm doing wrong.

 

[Tom Ogilvy]

change
.PrintArea = PrintRange

to

.PrintArea = PrintRange.Address(External:=True)

PrintArea is looking for a string.

 

[Joel Mills]

K.Dales & Tom, thanks for the help. This works now.