activecell formula gives #name error

[kmr47]

Hi
I'm running Excel 2010 and would appreciate help on a #Name error when trying to enter a formula using active cell.offset via VBA

ActiveCell.Offset(0, 4).Formula = "=If(ActiveCell.Offset(0, 1)>0,ActiveCell.Offset(0, 17) / ActiveCell.Offset(0, 1),0)"

The above 'works' in that a 'formula' is inserted in the target cell, but it doesn't evaluate the result, just gives #NAME error and the cell contents seem to be a text string

 

[BarneyB]

When using VBA to enter a formula into a cell, it needs to result in a formula that Excel can understand. You are trying to enter VBA code into a cell and therefore getting your error. Try this:

ActiveCell.Offset(0,4).Formula = "=IF(OFFSET(" & ActiveCell.Address & ",0,1)>0,OFFSET(" & ActiveCell.Address & ",0,17) / OFFSET(" & ActiveCell.Address & ",0,1),0)"

The idea is that everything within parentheses are exactly how Excel needs to see the formula and you are concatenating the Address into the formula.

Hope that works for you!

 

[joeu2004]

I'm running Excel 2010 and would appreciate help on a #Name error when trying to enter a formula using active cell.offset via VBA

ActiveCell.Offset(0, 4).Formula = "=If(ActiveCell.Offset(0, 1)>0,ActiveCell.Offset(0, 17) / ActiveCell.Offset(0, 1),0)"

The above 'works' in that a 'formula' is inserted in the target cell, but it doesn't evaluate the result, just gives #NAME error and the cell contents seem to be a text string

Of course, ActiveCell is not an Excel function, and ActiveCell.Offset is not Excel syntax. That is why you get a #NAME error.

What do you want the Excel formula to look like?

Perhaps one of the following:

"=If(" & ActiveCell.Offset(0, 1).Address & ">0," & ActiveCell.Offset(0, 17).Address & "/ " & ActiveCell.Offset(0, 1).Address & ",0)"

or

"=If(" & ActiveCell.Offset(0, 1).Address(false,false) & ">0," &  ActiveCell.Offset(0, 17).Address(false,false) & "/ " & ActiveCell.Offset(0,  1).Address(false,false) & ",0)"

The difference is absolute v. relative referencs.

Or it might be easier to use R1C1 notation, to wit:

ActiveCell.Offset(0, 4).FormulaR1C1 = "=If(RC[1]>0, RC[17] / RC[1], 0)"

 

[kmr47]

Of course, ActiveCell is not an Excel function, and ActiveCell.Offset is not Excel syntax. That is why you get a #NAME error.

What do you want the Excel formula to look like?

Perhaps one of the following:

"=If(" & ActiveCell.Offset(0, 1).Address & ">0," & ActiveCell.Offset(0, 17).Address & "/ " & ActiveCell.Offset(0, 1).Address & ",0)"

or

"=If(" & ActiveCell.Offset(0, 1).Address(false,false) & ">0," &  ActiveCell.Offset(0, 17).Address(false,false) & "/ " & ActiveCell.Offset(0,  1).Address(false,false) & ",0)"

The difference is absolute v. relative referencs.

Or it might be easier to use R1C1 notation, to wit:

ActiveCell.Offset(0, 4).FormulaR1C1 = "=If(RC[1]>0, RC[17] / RC[1], 0)"

Firstly a big thank you to both BarneyB and Joeu2004 or their helpful replies, which have both helped my knowledge of Excel increase.

Both BarneyB and Joeu2004 (the R1C1 version) ideas work very well but I noticed a subtle difference between the two which took me a while to understand.

Using BarneyB method was the most straightforward and it fitted with my original coding exactly. In other words I only changed the syntax per BarneyB.

Using Joeu2004 R1C1 method required a change to my code (and the solution from Joe) in that the 'number' used in each 'C' element of RC didn't provide the required result. I had to change the formula section to
="=If(RC[-3]>0,RC[13]/RC[-3],0)" in place of ="=If(RC[1]>0,RC[17]/RC[1],0)". After looking hard I think I understand why

The R1C1 formula has been placed in a cell that is 4 columns to the right of the starting activecell via the Offset action therefore the 'C' references needed to have 4 'deducted' to provide the correct column reference in the finished formula

The OFFSET method from BarneyB works differently in that the offset values in the formula are referring to the starting activecell address not the cell where the formula is placed.

Might be obvious to you experts out there but it took me a while to spot the difference and then try to understand it.

So Thanks again for all your help.

 

[joeu2004]

Using Joeu2004 R1C1 method required a change to my code (and the solution from Joe) in that the 'number' used in each 'C' element of RC didn't provide the required result. I had to change the formula section to
="=If(RC[-3]>0,RC[13]/RC[-3],0)" in place of ="=If(RC[1]>0,RC[17]/RC[1],0)". After looking hard I think I understand why

Right. That was my mistake. Sorry.

The significant difference between the two are: OFFSET is a "volatile" function; so that formula and every cell that depends on that cell directly or indirectly is recalculated every time you edit any cell in any worksheet in the workbook.

Not a big deal if you only have a few such formulas. But it can really become noticable if you have many, or if you have COUNT or VLOOKUP formulas, for example, over a range that includes the cell with OFFSET. It is an example of why you see so many queries like "Excel stops responding", "it takes several minutes to open or save my workbook" and "Excel crashes when I save".

In contrast, the R1C1-based formula is probably how you would have written the formula in Excel to begin with, with relative cell references. Besides avoiding the "volatile" side-effects, it is more efficient.

The operative word is "probably". There are circumstances and requirements that necessitate the use of OFFSET. If that is indeed what you would have used in Excel in this circumstance, then the OFFSET solution might indeed be the better one to use.

To be honest, I do not use FormulaR1C1 much myself. That is my (lame) excuse for the oversight. I probably would have used the Active.Offset(...).Address form, which has the same effect. It is more characters to type. But it is less error-prone, and it might even make more sense when you read the code 6 months from now.

 

[joeu2004]

Errata (too late to edit)....

I probably would have used the Active.Offset(...).Address form, which has the same effect.

I mean the ActiveCell.Offset(...).Address(False,False) form.

 

[kmr47]

Errata (too late to edit)....


I mean the ActiveCell.Offset(...).Address(False,False) form.

Hi Joeu2004

I appreciate you taking the time to follow up on my post. In a way I'm glad you made a (small) mistake in your original answer as that made me think about how the different solutions worked and led me to understand more of the wonders of Excel coding.

I'm also grateful for the information on why the 'volatile' situation is best avoided - I've noted comments before but they didn't explain the all important 'why'.

If you are still feeling generous could you explain more of the (False,False) construct you referred to, as at present it is new to me.

Kind regards.

 

[joeu2004]

I mean the ActiveCell.Offset(...).Address(False,False) form.

could you explain more of the (False,False) construct you referred to, as at present it is new to me.

You could always experiment with True and False, and see for yourself.

Alternatively, look at the range.Address help page in VBA (not Excel). In explains the Address parameters, to wit:

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
expression   A variable that represents a Range object.
[....]
RowAbsolute   [...] True to return the row part of the reference as an absolute reference. The default value is True. 
ColumnAbsolute   [...] True to return the column part of the reference as an absolute reference. The default value is True.

Although it neglects to explain what False does, it "must" be not absolute, which is relative. So:

1. Range("a1").Address(True,True) is $A$1.
2. Range("a1").Address(False,False) is A1.
3. Range("a1").Address(True,False) is $A1.
4. Range("a1".Address(False,True) is A$1.

We can also write Address(RowAbsolute:=False, ColumnAbsolute:=False).

 

[kmr47]

You could always experiment with True and False, and see for yourself.

Alternatively, look at the range.Address help page in VBA (not Excel). In explains the Address parameters, to wit:

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
expression   A variable that represents a Range object.
[....]
RowAbsolute   [...] True to return the row part of the reference as an absolute reference. The default value is True. 
ColumnAbsolute   [...] True to return the column part of the reference as an absolute reference. The default value is True.

Although it neglects to explain what False does, it "must" be not absolute, which is relative. So:

1. Range("a1").Address(True,True) is $A$1.
2. Range("a1").Address(False,False) is A1.
3. Range("a1").Address(True,False) is $A1.
4. Range("a1".Address(False,True) is A$1.

We can also write Address(RowAbsolute:=False, ColumnAbsolute:=False).

Thank you, that's a great intro to the topic for me and I'll follow up your suggestion with the help pages. Your help is greatly appreciated.