Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a value in a range command
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(Coll atloop) 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a value in a range command
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" wrote in message ... 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(Coll atloop) 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a value in a range command
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 "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" wrote in message ... 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(Coll atloop) 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a value in a range command
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a value in a range command
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a value in a range command
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(Coll atloop) 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass Command Button info | Excel Programming | |||
Pass Defined Name to a Command Button | Excel Programming | |||
Pass a cell in sheet to macro or command button | Excel Programming | |||
How to: Pass Command Line Parameter ??? | Excel Worksheet Functions | |||
Command and pass value to Acces from Excel | Excel Programming |