I have the following code to modify specific ranges in a number of exel
files.

Is it possible to set a variable in the code to pass the range name though
to the Range command so I dont have to repeat the process, but only check for
a given range name and update that field with the value?

i.e. i want to change [Range("loan_margin").Value = loanmargin]
"loan_margin" to refer to a variable that contains the range name to update.

thanks
george

Sub modcollat()

' ==============================
' Define Variables
' ==============================

Dim poolfile As String
Dim collatfile As String
Dim Theresponse As String

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

Dim drawamount As String
Dim poolamount As String
Dim loanmargin As String
Dim modrate As String

Dim drawamountwrite As String
Dim poolamountwrite As String
Dim loanmarginwrite As String

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

poolfile = ActiveWorkbook.Name

drawamountwrite = Range("draw_amount_write").Value
poolamountwrite = Range("pool_amount_write").Value
loanmarginwrite = Range("loan_margin_write").Value
modrate = Range("mod_rate").Value

drawamount = Range("i_draw_amount").Value
poolamount = Range("i_pool_amount_to_date").Value
loanmargin = Range("i_loan_margin").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(Collatloop)
collatfile = ActiveWorkbook.Name
collatincluded = collatincluded + 1

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

Windows(collatfile).Activate

'****************
'* This pastes the current draw amount and the pool amount to date
from the pool file
'* to each collateral file so the IC Memo tables show the correct %
'**************

If drawamountwrite = "Yes" Then
Range("draw_amount").Value = drawamount
End If

If poolamountwrite = "Yes" Then
Range("pool_amount_to_date").Value = poolamount
End If

If loanmarginwrite = "Yes" Then
Range("loan_margin").Value = loanmargin
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 ")

End Sub

Like this

Call mySub("i_loan_margin")

and use like so

Sub mySub(RangeName As String)

....

loanmargin = Range(RangeName).Value

....



--
HTH

Bob

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

"mopgcw" <(E-Mail Removed)> wrote in message
news:70416AF7-4CA1-4B26-BC3C-(E-Mail Removed)...
>I have the following code to modify specific ranges in a number of exel
> files.
>
> Is it possible to set a variable in the code to pass the range name though
> to the Range command so I dont have to repeat the process, but only check
> for
> a given range name and update that field with the value?
>
> i.e. i want to change [Range("loan_margin").Value = loanmargin]
> "loan_margin" to refer to a variable that contains the range name to
> update.
>
> thanks
> george
>
> Sub modcollat()
>
> ' ==============================
> ' Define Variables
> ' ==============================
>
> Dim poolfile As String
> Dim collatfile As String
> Dim Theresponse As String
>
> Dim numcollat As Integer
> Dim Collatloop As Integer
> Dim collatincluded As Integer
>
> Dim drawamount As String
> Dim poolamount As String
> Dim loanmargin As String
> Dim modrate As String
>
> Dim drawamountwrite As String
> Dim poolamountwrite As String
> Dim loanmarginwrite As String
>
> ' ==============================
> ' Initialize Variables
> ' ==============================
>
> poolfile = ActiveWorkbook.Name
>
> drawamountwrite = Range("draw_amount_write").Value
> poolamountwrite = Range("pool_amount_write").Value
> loanmarginwrite = Range("loan_margin_write").Value
> modrate = Range("mod_rate").Value
>
> drawamount = Range("i_draw_amount").Value
> poolamount = Range("i_pool_amount_to_date").Value
> loanmargin = Range("i_loan_margin").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(Collatloop)
> collatfile = ActiveWorkbook.Name
> collatincluded = collatincluded + 1
>
> '========================================
> 'Go to Collat File & Copy Data
> '========================================
>
> Windows(collatfile).Activate
>
> '****************
> '* This pastes the current draw amount and the pool amount to date
> from the pool file
> '* to each collateral file so the IC Memo tables show the correct %
> '**************
>
> If drawamountwrite = "Yes" Then
> Range("draw_amount").Value = drawamount
> End If
>
> If poolamountwrite = "Yes" Then
> Range("pool_amount_to_date").Value = poolamount
> End If
>
> If loanmarginwrite = "Yes" Then
> Range("loan_margin").Value = loanmargin
> 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 ")
>
> End Sub
>
>
>

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(Collatloop)
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





"Bob Phillips" wrote:

> Like this
>
> Call mySub("i_loan_margin")
>
> and use like so
>
> Sub mySub(RangeName As String)
>
> ....
>
> loanmargin = Range(RangeName).Value
>
> ....
>
>
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "mopgcw" <(E-Mail Removed)> wrote in message
> news:70416AF7-4CA1-4B26-BC3C-(E-Mail Removed)...
> >I have the following code to modify specific ranges in a number of exel
> > files.
> >
> > Is it possible to set a variable in the code to pass the range name though
> > to the Range command so I dont have to repeat the process, but only check
> > for
> > a given range name and update that field with the value?
> >
> > i.e. i want to change [Range("loan_margin").Value = loanmargin]
> > "loan_margin" to refer to a variable that contains the range name to
> > update.
> >
> > thanks
> > george
> >
> > Sub modcollat()
> >
> > ' ==============================
> > ' Define Variables
> > ' ==============================
> >
> > Dim poolfile As String
> > Dim collatfile As String
> > Dim Theresponse As String
> >
> > Dim numcollat As Integer
> > Dim Collatloop As Integer
> > Dim collatincluded As Integer
> >
> > Dim drawamount As String
> > Dim poolamount As String
> > Dim loanmargin As String
> > Dim modrate As String
> >
> > Dim drawamountwrite As String
> > Dim poolamountwrite As String
> > Dim loanmarginwrite As String
> >
> > ' ==============================
> > ' Initialize Variables
> > ' ==============================
> >
> > poolfile = ActiveWorkbook.Name
> >
> > drawamountwrite = Range("draw_amount_write").Value
> > poolamountwrite = Range("pool_amount_write").Value
> > loanmarginwrite = Range("loan_margin_write").Value
> > modrate = Range("mod_rate").Value
> >
> > drawamount = Range("i_draw_amount").Value
> > poolamount = Range("i_pool_amount_to_date").Value
> > loanmargin = Range("i_loan_margin").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(Collatloop)
> > collatfile = ActiveWorkbook.Name
> > collatincluded = collatincluded + 1
> >
> > '========================================
> > 'Go to Collat File & Copy Data
> > '========================================
> >
> > Windows(collatfile).Activate
> >
> > '****************
> > '* This pastes the current draw amount and the pool amount to date
> > from the pool file
> > '* to each collateral file so the IC Memo tables show the correct %
> > '**************
> >
> > If drawamountwrite = "Yes" Then
> > Range("draw_amount").Value = drawamount
> > End If
> >
> > If poolamountwrite = "Yes" Then
> > Range("pool_amount_to_date").Value = poolamount
> > End If
> >
> > If loanmarginwrite = "Yes" Then
> > Range("loan_margin").Value = loanmargin
> > 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 ")
> >
> > End Sub
> >
>

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" <(E-Mail Removed)> wrote in message
news:C903B3BA-0C14-4B02-B7C6-(E-Mail Removed)...
> 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(Collatloop)
> 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
>
>

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" <(E-Mail Removed)> wrote in message
> news:C903B3BA-0C14-4B02-B7C6-(E-Mail Removed)...
> > 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(Collatloop)
> > 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
> >
> >
>
>
>

For those interested, here is the final code:
Sub modcollat()

' ==============================
' Define Variables
' ==============================

Dim poolfile As String
Dim collatfile As String
Dim Theresponse As String

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

Dim drawamount As String
Dim poolamount As String
Dim updaterate As String
Dim modrate As String

Dim drawamountwrite As String
Dim poolamountwrite As String
Dim loanmarginwrite As String

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

poolfile = ActiveWorkbook.Name

drawamountwrite = Range("draw_amount_write").Value
poolamountwrite = Range("pool_amount_write").Value
loanmarginwrite = Range("loan_margin_write").Value


drawamount = Range("i_draw_amount").Value
poolamount = Range("i_pool_amount_to_date").Value

'** this defines the new rate to write to the collateral file
updaterate = Range("i_loan_margin").Value

'** this defines the range name of the interest rate to be updated
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(Collatloop)
collatfile = ActiveWorkbook.Name
collatincluded = collatincluded + 1

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

Windows(collatfile).Activate

'****************
'* This pastes the current draw amount and the pool amount to date
from the pool file
'* to each collateral file so the IC Memo tables show the correct %
'**************

If drawamountwrite = "Yes" Then
Range("draw_amount").Value = drawamount
End If

If poolamountwrite = "Yes" Then
Range("pool_amount_to_date").Value = poolamount
End If

If loanmarginwrite = "Yes" Then
Range(modrate).Value = updaterate
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



"mopgcw" wrote:

> 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" <(E-Mail Removed)> wrote in message
> > news:C903B3BA-0C14-4B02-B7C6-(E-Mail Removed)...
> > > 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(Collatloop)
> > > 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
> > >
> > >
> >
> >
> >