How do I pass a value in a range command

G

Guest

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Bob Phillips

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)
 
G

Guest

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Macro Error Message? 3
macro to copy from excel range to word doc 1
Find the correct sheet then find a value on that sheet 8
Check a range to see if a value exists in it 5
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Can Application.Vlookup access a range in a file which is not open? 4
Filesearch in 2007 1
Loop Repeats - PLS HELP 2

Top