PDA

View Full Version : Solved: Nomenclature for referencing named cells in .Formula Code



CodeMakr
07-23-2007, 03:39 PM
I am having difficulty coming up with how to use excel's named cells/ranges within VBA code.

Currently I have:


Cells("D5").Formula = "=IF(C5>=9, B5*TenYrAccrual, B5*BaseAccrual)"


TenYrAccrual & BaseAccrual are my named ranges in excel, but the above statement doesn't place the correct formula into my spreadsheet? Can anyone tell me the correct nomenclature for referencing these named ranges.

Thanks in advance for your help.

Bob Phillips
07-23-2007, 04:16 PM
That is the correct way. Unless you have embedded quotes within a formula, and you use A1 notation, you write it in VBA exactly as you would in Excel, all wrapped in a set of quotes.

Does yours fail? Does it put something else in the sheet? Have you checked that the range names are spelt correctly/exist?

daniel_d_n_r
07-23-2007, 04:18 PM
This works better than what you have there.

Sub sumrange()
Range("D5").Activate
ActiveCell.Formula = "=SUM(A2:A11)"
End Sub

though with mine i cannot test yours as i have not entered the named ranges
to access a named range you have to add it via insert--name--define

try it and if it does not work make sure you have defined the ranges.

from the look of your formula ,the accrual ranges would have to be single cells or else you are asking excel to place a range of values into B5 alone.

Bob Phillips
07-23-2007, 04:20 PM
Oops I see the problem, should be Range("D5") not Cells("D5")

CodeMakr
07-23-2007, 04:23 PM
I get a "run-time error '13:' - Type mismatch error when I run the code above. I have it at the workbook level vs. worksheet. Does that make a difference?

CodeMakr
07-23-2007, 04:34 PM
Ahh....thanks xld. That did the trick. Using the .formula or .formulaR1C1 it must always be with Range and not Cell.....correct.

Bob Phillips
07-23-2007, 04:40 PM
No, you could have used Cells, but not with that syntax. The syntax for Cells is

Cells(row, column)

whereas Range is

Rang(cell[:cell])

So you can use

Cells(5,4)

or Cells(5,"D")

but not Cells("D5")

Similarly, you can use

Range("D5")

or Range ("D" & 5)

but not Range("D", 5)

CodeMakr
07-23-2007, 05:18 PM
Thanks a ton!! I appreciate your time.