Name cell and use in formula (VBA Excel 2007)

[zeno]

Hello,
I'm trying to figure out the following: I have a cell in Excel sheet that I define with a name. Next I want to use that specific cell in a VBA calculation, using that name. How can I write this code in VBA?
as an example: calculate an average of a group of cells, then multiply that average with the value of the named cell.
So far, I get an error message.

 

[tweedle]

as an example

'Add a named range
ThisWorkbook.Names.Add "mplier", RefersTo:=Sheet1.Range("C1")
'calc an average of cells
avgval = WorksheetFunction.Average(Range("D2:E10"))
'calc a multiply of the avg and the named range
myresult = avgval * Range("mplier")
'Set the cell below the named range as the result of the calc
Range("mplier").Offset(1, 0) = myresult

 

[mikerickson]

Range("A1").Name = "MyCell"
Range("B1").FormulaR1C1 = "=MyCell+1"

 

[zeno]

Thank you for your replies.
Is it possible to define the name as a variable? i.e.

Dim MyCell
Dim mplier

and then use them in a formula as e.g.

Range("MyCell") * average /3

 

[mikerickson]

You can a VBA variable to calculate a constant that can be put in a cell

The result of this code will be the value 12 in B1 and the formula =10+8 in C1.

Dim myCell as Range
Set myCell = Range("A1")

myCell.Value = 3
Range("B1").Value = myCell.Value * 4

myCell.Value = 10
Range("C1").FormulaR1C1 = "=" & myCell.Value & "+8"

 

[zeno]

Thank you mikerickson, this is working.
I have a follow-up question. In case I have multiple sheets from which to take values in my formulas, how do I ensure that the formula is taking the cell with that name on a specific sheet rather than another sheet?
Also, in the Excel sheet itself I have named the cell with that specific name (e.g. myCell) in the Name box.
But from my testing it seems that it is taking the value from the same sheet rather than the sheet where I named the cell "myCell".
How can I correct that?

 

[mikerickson]

For the Name, the defintion should specify which sheet it is taken from. In this definition, myCell looks only to Sheet1 to get its value.

Name: myCell
RefersTo: =Sheet1!$A$1

If you use a definition like =!$A$1 , a formula using the name myCell will look to A1 of the same sheet as the cell with the formula.

In VBA, one should qualify one's ranges and use the ExternalReference argument of .Address when needed.

Dim myRange as Range

Set myRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")

ThisWorkbook.Sheets("Sheet2").Range("B1:B10").FormulaR1C1 = "=SUM(" & myRange.Address(True, True, xlR1C1, True) & ")"

 

[zeno]

According to my tests I'm getting the values I'm looking for.
Thank you! Very much appreciated!