Like I said, I am not sure I correctly understood your pointer. to begin
with.

I eliminated the call and and just put the range(modrate) = updaterate in
the main sub and it worked!

thanks again for your help.
george

"Bob Phillips" wrote:

I am not clear on your logic of calling/called sub, but assuming it is
correct, the modratesub sub should at minimum should read

Sub modratesub(modrate As String)

updaterate = Range(modrate).Value

End Sub

but the whole sub does nothing

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mopgcw" wrote in message
...
Bob,

I really appreciate the pointer, but not sure I understand it so naturally
don't have it working correctly. and maybe i did not explain what i am
trying to do very well.

I have 3 different rates (3 different range names) in a large number of
different excel files. I want to update one of those rates based on the
range name specified in a master file by the user -- i.e. they select
"swap_rate" to to update the range name "swap_rate" in each collateral
file
with the rate in the master file.

here is how i tried to incorporate your pointer (and cleaned out some
irrelevant stuff) for ease, but it gets hung up in the called routine.

thanks again
george


Sub modcollat()

Dim poolfile As String
Dim collatfile As String

Dim numcollat As Integer
Dim Collatloop As Integer
Dim collatincluded As Integer

'** this defines the new rate to write to the collateral file
Dim updaterate As String

'** this defines the range name of the interest rate to be updated
Dim modrate As String

'** this determines whether to update the rate or not
Dim loanmarginwrite As String

' ==============================
' Initialize Variables
' ==============================

poolfile = ActiveWorkbook.Name

loanmarginwrite = Range("loan_margin_write").Value

'** this pulls the updated rate from the master file
updaterate = Range("i_loan_margin").Value

'** this pulls the range name of the rate to update in the collateral file
modrate = Range("mod_rate").Value

collatincluded = 0
Application.ScreenUpdating = False
numcollat = WorksheetFunction.Max(Range("array_collatfilenum") .Value)


' ==============================
' Loop through each collat file
' ==============================

For Collatloop = 1 To numcollat

If Range("array_collatfileinclude").Cells(Collatloop) = 1 Then

'============================
'Open the collat file
'============================

Workbooks.Open
Filename:=Range("array_collatfilename").Cells(Coll atloop)
collatfile = ActiveWorkbook.Name
collatincluded = collatincluded + 1

'========================================
'Go to Collat File & Update Data
'========================================

Windows(collatfile).Activate

If loanmarginwrite = "Yes" Then
Call modratesub("i_loan_margin")
End If


' ================================
' go to collateral file and close
' ================================

Range("A1").Copy ' JUST TO CLEAR CLIPBOARD

Windows(collatfile).Activate

ActiveWorkbook.Close savechanges:=True

End If

Next Collatloop

MsgBox ("IC Memo from " + WorksheetFunction.Text(collatincluded, "0") + "
collateral updated for Francis")

End Sub

Sub modratesub(modrate As String)

updaterate = Range("modrate").Value

End Sub