Macro running within Macro

[randy.gooch]

I have a macro which selects to the last field within a given range
which has data greater than zero. I have tried to use this macro in
another macro to reselect the range and set print range for printing.
After running the macro the first time, it hard codes the set print
range and will not change it after that. I need the Application.Run
"'09RadLabPath.xls'!Sheet3.find_it" in the code below to run each
time
and then have the set print range be updated which might be different
than the $AM$34. I am also including the code for the Find It macro
at the end. Please let me know if you can help resolve this issue.
Thanks!

Randy


Sub RLP802PrintServices()
'
' RLP802PrintServices Macro
' Macro recorded 4/4/2008 by
'


'
ActiveWorkbook.CustomViews("RLP-802 Print Services View").Show
Application.Run "'09RadLabPath.xls'!Sheet3.find_it"
ActiveSheet.PageSetup.PrintArea = "$D$12:$AM$34"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Run "'09RadLabPath.xls'!RLP802OriginalView"
End Sub


Sub find_it()
endcell = 0
Set rGoTo = Range("D12")
Set r = Range("D12:AN112")
For Each rr In r
If rr.Value = 0 Then
Else
endcell = rr.Address
Set rGoTo = rr
End If
Next
Range("D12:" & endcell).Select
End Sub

 

[Bernie Deitrick]

Randy,

Try it this way:

Sub RLP802PrintServices()
ActiveWorkbook.CustomViews("RLP-802 Print Services View").Show
ActiveSheet.PageSetup.PrintArea = find_it
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.Run "'09RadLabPath.xls'!RLP802OriginalView"
End Sub

Function find_it() As String
Dim rGoTo As Range
Dim r As Range
Dim rr As Range

Set rGoTo = Range("D12")
Set r = Range("D12:AN112")
For Each rr In r
If rr.Value <> 0 Then
If rr.Column > rGoTo.Column Then Set rGoTo = Cells(rGoTo.Row, rr.Column)
If rr.Row > rGoTo.Row Then Set rGoTo = Cells(rr.Row, rGoTo.Column)
End If
Next
find_it = Range("D12", rGoTo).Address
End Function

HTH,
Bernie
MS Excel MVP

 

[randy.gooch]

Randy,

Try it this way:

Sub RLP802PrintServices()
   ActiveWorkbook.CustomViews("RLP-802 Print Services View").Show
   ActiveSheet.PageSetup.PrintArea = find_it
   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
   Application.Run "'09RadLabPath.xls'!RLP802OriginalView"
End Sub

Function find_it() As String
Dim rGoTo As Range
Dim r As Range
Dim rr As Range

Set rGoTo = Range("D12")
Set r = Range("D12:AN112")
For Each rr In r
    If rr.Value <> 0 Then
        If rr.Column > rGoTo.Column Then Set rGoTo = Cells(rGoTo..Row, rr.Column)
        If rr.Row > rGoTo.Row Then Set rGoTo = Cells(rr.Row, rGoTo.Column)
    End If
Next
find_it = Range("D12", rGoTo).Address
End Function

HTH,
Bernie
MS Excel MVP








- Show quoted text -

That worked perfectly!!! Thanks a bunch Bernie!