I'm not sure what I'm doing wrong here. the first sub calls the second sub
which sets 2 vars (prog_name & Levels). These values are not being returned
to the calling sub.

thanks for looking.

see below.


Public Next_Prog, Last_Prog, Start_add As Variant
Public ProgName, Levels As String



Sub Step2_Extract_Profiles()
Dim logOn, Prof_row, Row_nbr, Last_row As Variant
Dim copy_From, ProgName, Levels As String

Dim rCell As Range
Dim cell As Range

Range("A2").Select
Prog_row = ActiveCell.Row
Selection.End(xlDown).Select
Last_prog = ActiveCell.Row
Range("A2").Select
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address

Range("D4").Select
Row_nbr = ActiveCell.Row
Start_add = ActiveCell.Address
Selection.End(xlDown).Select
Last_row = ActiveCell.Row
Range(Start_add).Select
Do While ActiveCell.Row <= Last_row
If ActiveCell.Value = ProgName Then
End If
GoSub Set_Prg_Name
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub



Sub Set_Prg_Name()
Range(next_prog).select
if activecell.row Last_prog then
else
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0,1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address 'values change here but are not
returned to calling sub
end if
End Sub

You are using a GoSub command to call another procedure, which is incorrect.
GoSub is used to call a subroutine within the same procedure. Try the Call
command instead. Also, delete the End If statement just before the GoSub.

Hope this helps,

Hutch

"El Bee" wrote:

I'm not sure what I'm doing wrong here. the first sub calls the second sub
which sets 2 vars (prog_name & Levels). These values are not being returned
to the calling sub.

thanks for looking.

see below.


Public Next_Prog, Last_Prog, Start_add As Variant
Public ProgName, Levels As String



Sub Step2_Extract_Profiles()
Dim logOn, Prof_row, Row_nbr, Last_row As Variant
Dim copy_From, ProgName, Levels As String

Dim rCell As Range
Dim cell As Range

Range("A2").Select
Prog_row = ActiveCell.Row
Selection.End(xlDown).Select
Last_prog = ActiveCell.Row
Range("A2").Select
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address

Range("D4").Select
Row_nbr = ActiveCell.Row
Start_add = ActiveCell.Address
Selection.End(xlDown).Select
Last_row = ActiveCell.Row
Range(Start_add).Select
Do While ActiveCell.Row <= Last_row
If ActiveCell.Value = ProgName Then
End If
GoSub Set_Prg_Name
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub



Sub Set_Prg_Name()
Range(next_prog).select
if activecell.row Last_prog then
else
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0,1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address 'values change here but are not
returned to calling sub
end if
End Sub

Tom,

I caught the Gosub mistake and changed it to a call. The "end if" was part
of a previous "if" statement; sorry for the confusion. I copied a portion of
the macro and didn't edit it close enough.

With those corrections made. I added a msgbox in the calling sub routine to
see if the values would be change, and they were, but they are not being
returned to the calling sub routine. The original values for ProgName and
Levels do not get updated by the call to the Set_Prg_Name sub.

"Tom Hutchins" wrote:

You are using a GoSub command to call another procedure, which is incorrect.
GoSub is used to call a subroutine within the same procedure. Try the Call
command instead. Also, delete the End If statement just before the GoSub.

Hope this helps,

Hutch

"El Bee" wrote:

I'm not sure what I'm doing wrong here. the first sub calls the second sub
which sets 2 vars (prog_name & Levels). These values are not being returned
to the calling sub.

thanks for looking.

see below.


Public Next_Prog, Last_Prog, Start_add As Variant
Public ProgName, Levels As String



Sub Step2_Extract_Profiles()
Dim logOn, Prof_row, Row_nbr, Last_row As Variant
Dim copy_From, ProgName, Levels As String

Dim rCell As Range
Dim cell As Range

Range("A2").Select
Prog_row = ActiveCell.Row
Selection.End(xlDown).Select
Last_prog = ActiveCell.Row
Range("A2").Select
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address

Range("D4").Select
Row_nbr = ActiveCell.Row
Start_add = ActiveCell.Address
Selection.End(xlDown).Select
Last_row = ActiveCell.Row
Range(Start_add).Select
Do While ActiveCell.Row <= Last_row
If ActiveCell.Value = ProgName Then
End If
GoSub Set_Prg_Name
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub



Sub Set_Prg_Name()
Range(next_prog).select
if activecell.row Last_prog then
else
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0,1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address 'values change here but are not
returned to calling sub
end if
End Sub

You have ProgName and Levels declared as Public variables, and they are
declared again as private variables within the Step2_Extract_Profiles
procedure. The locally declared variable will take preference within a
procedure over a Public variable with the same name. Your called procedure is
updating the Public variables, but Step2_Extract_Profiles is working with the
ProgName and Levels variables declared within itself. Remove ProgName and
Levels from the DIm statement in Step2_Extract_Profiles.

Hutch

"El Bee" wrote:

Tom,

I caught the Gosub mistake and changed it to a call. The "end if" was part
of a previous "if" statement; sorry for the confusion. I copied a portion of
the macro and didn't edit it close enough.

With those corrections made. I added a msgbox in the calling sub routine to
see if the values would be change, and they were, but they are not being
returned to the calling sub routine. The original values for ProgName and
Levels do not get updated by the call to the Set_Prg_Name sub.

"Tom Hutchins" wrote:

You are using a GoSub command to call another procedure, which is incorrect.
GoSub is used to call a subroutine within the same procedure. Try the Call
command instead. Also, delete the End If statement just before the GoSub.

Hope this helps,

Hutch

"El Bee" wrote:

I'm not sure what I'm doing wrong here. the first sub calls the second sub
which sets 2 vars (prog_name & Levels). These values are not being returned
to the calling sub.

thanks for looking.

see below.


Public Next_Prog, Last_Prog, Start_add As Variant
Public ProgName, Levels As String



Sub Step2_Extract_Profiles()
Dim logOn, Prof_row, Row_nbr, Last_row As Variant
Dim copy_From, ProgName, Levels As String

Dim rCell As Range
Dim cell As Range

Range("A2").Select
Prog_row = ActiveCell.Row
Selection.End(xlDown).Select
Last_prog = ActiveCell.Row
Range("A2").Select
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address

Range("D4").Select
Row_nbr = ActiveCell.Row
Start_add = ActiveCell.Address
Selection.End(xlDown).Select
Last_row = ActiveCell.Row
Range(Start_add).Select
Do While ActiveCell.Row <= Last_row
If ActiveCell.Value = ProgName Then
End If
GoSub Set_Prg_Name
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub



Sub Set_Prg_Name()
Range(next_prog).select
if activecell.row Last_prog then
else
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0,1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address 'values change here but are not
returned to calling sub
end if
End Sub

Thanks, Tom!!

That was another oversight on my part. It's now working!!!

"Tom Hutchins" wrote:

You have ProgName and Levels declared as Public variables, and they are
declared again as private variables within the Step2_Extract_Profiles
procedure. The locally declared variable will take preference within a
procedure over a Public variable with the same name. Your called procedure is
updating the Public variables, but Step2_Extract_Profiles is working with the
ProgName and Levels variables declared within itself. Remove ProgName and
Levels from the DIm statement in Step2_Extract_Profiles.

Hutch

"El Bee" wrote:

Tom,

I caught the Gosub mistake and changed it to a call. The "end if" was part
of a previous "if" statement; sorry for the confusion. I copied a portion of
the macro and didn't edit it close enough.

With those corrections made. I added a msgbox in the calling sub routine to
see if the values would be change, and they were, but they are not being
returned to the calling sub routine. The original values for ProgName and
Levels do not get updated by the call to the Set_Prg_Name sub.

"Tom Hutchins" wrote:

You are using a GoSub command to call another procedure, which is incorrect.
GoSub is used to call a subroutine within the same procedure. Try the Call
command instead. Also, delete the End If statement just before the GoSub.

Hope this helps,

Hutch

"El Bee" wrote:

I'm not sure what I'm doing wrong here. the first sub calls the second sub
which sets 2 vars (prog_name & Levels). These values are not being returned
to the calling sub.

thanks for looking.

see below.


Public Next_Prog, Last_Prog, Start_add As Variant
Public ProgName, Levels As String



Sub Step2_Extract_Profiles()
Dim logOn, Prof_row, Row_nbr, Last_row As Variant
Dim copy_From, ProgName, Levels As String

Dim rCell As Range
Dim cell As Range

Range("A2").Select
Prog_row = ActiveCell.Row
Selection.End(xlDown).Select
Last_prog = ActiveCell.Row
Range("A2").Select
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address

Range("D4").Select
Row_nbr = ActiveCell.Row
Start_add = ActiveCell.Address
Selection.End(xlDown).Select
Last_row = ActiveCell.Row
Range(Start_add).Select
Do While ActiveCell.Row <= Last_row
If ActiveCell.Value = ProgName Then
End If
GoSub Set_Prg_Name
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub



Sub Set_Prg_Name()
Range(next_prog).select
if activecell.row Last_prog then
else
ProgName = ActiveCell.Value
Levels = ActiveCell.Offset(0,1)
ActiveCell.Offset(1, 0).Select
next_prog = ActiveCell.Address 'values change here but are not
returned to calling sub
end if
End Sub