Refering to Cell's Name in Macro

  • Thread starter Thread starter LSB
  • Start date Start date
L

LSB

I am trying to work on some calculation in my macro. I
have named one the cells as "Brokerage_Commission" in, in
my Macro I would like to get this value to multiply with
another value. How can I refer the "Brokerage_Commission"
in my macro. The Brokerage_Commission is in different
worksheet as I want to put the result in another worksheet.

Please help.

Thanks.
 
activecell.value = [Brokerage_Commission]

will put the value of the named range in the activec cell of any sheet
in the workbook.
 
LSB,

Range("Brokerage_Commission")
should do it.

Example:

Say Brokerage Commission = .05
In cell A1 (on the active sheet) you have 150

Sub TestMe()
MsgBox Range("Brokerage_Commission")*Range("A1")
End Sub

John
 
brkCom = Range("Brokerage_Commission").Value


if you will be doing this in a sheet module and brokerage_commission is on
another sheet, you need to preface it with the sheetname. Assume
Brokerage_Commission is on sheet3.

brkCom = Worksheets("sheet3").Range("Brokerage_Commission").Value
 
I can't duplicate that.In sheet1 I name A1 as nRange
In Sheet1 module I have a sub with:
activecell.value = [nRange]
In sheet3 I call the sub.
It returns the value from nRange to the activecell without prefacing
with the sheet name.
OfficeXP
 
I don't believed I used tunneling as you have: [nRange]

Tunneling, while much slower, is perhaps less susceptible to this.

If you have never had a problem with references to other sheets in a sheet
module, god bless you. People post here all the time with such problems.

Regards,
Tom Ogilvy


Thomas said:
I can't duplicate that.In sheet1 I name A1 as nRange
In Sheet1 module I have a sub with:
activecell.value = [nRange]
In sheet3 I call the sub.
It returns the value from nRange to the activecell without prefacing
with the sheet name.
OfficeXP

Tom said:
brkCom = Range("Brokerage_Commission").Value

if you will be doing this in a sheet module and brokerage_commission is on
another sheet, you need to preface it with the sheetname. Assume
Brokerage_Commission is on sheet3.

brkCom = Worksheets("sheet3").Range("Brokerage_Commission").Value
Click to expand...
Click to expand...
 
Back
Top