Excel Macro - Adding If statement 1

[jamminjaymeyer]

I have a number of cells that now require an if statement to be included at the begining. The macro code that I am trying to use is:

ActiveCell.FormulaR1C1 = "=if(N7=0,0," & Right(ActiveCell.FormulaR1C1, Len(ActiveCell.FormulaR1C1) - 1) & ")"

All of it work fine except the N7 reference. When I run this macro it puts 'N7' rather than the cell reference. Is there anyway to not make Excel treat this N7 reference as a string?

Thanks,
jamminjaymeyer

 

[AnotherHiggins]

I imagine you recorded this and then manipulated the code that Excel generated, right? I ask because Excel is fond of inserting that little 'FormulaR1C1'. What that bit tellls Excel is that you are about to use R1C1 referencing, but you don't - you use N7. In R1C1, N7 is expressed as R7C14 (row 7 column 14). All you need to do is get rid of the 'FormulaR1C1'.

[COLOR=blue white]ActiveCell = "=if(N7=0,0," & Right(ActiveCell, Len(ActiveCell) - 1) & ")"[/color]

But be aware that if your 'activecell' is ever empty, your code will error out. If you want help getting around that problem, post back.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[jamminjaymeyer]

Thank you so much. You are exactly correct as to my procedure. Now it seems to be evaluating the

Right(ActiveCell, Len(ActiveCell) - 1)

into a value rather than keeping the actual "formula text. Is there any way to not have it evaluate the text in to a number?

 

[AnotherHiggins]

The trick is that you want to place a formula in a cell ( let's say A1) that evaluates what is in that same cell (A1). That creates a 'circular reference'. Try typing =A1 into cell A1 on a sheet. Excel will freak out and give you an error.

That's the beauty of using VBA - you can create a formula that references the value in the cell where you are about to drop the formula.

If you want to have the formula display normally, try placing the formula in a different cell. For example, if the 'Active Cell' is A1, then try this code

[COLOR=blue white]range("b2") = "=if(N7=0,0," & Right(ActiveCell, Len(ActiveCell) - 1) & ")"[/color]

For more info on circular references, you can see

http://office.microsoft.com/en-us/assistance/HP052002851033.aspx

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[jamminjaymeyer]

OK that makes sense. When I try what you mentioned above it still evaluates the active cell formula into a numeric value and puts that number in the formula in place of the full formula that I need.

The problem I am having is rather than evaluating the formula that I am trying to place back into the cell I want to place the actual text of the formula back in the cell. The end goal of this is really to save me some typing, I don't want to have to go back to each cell and type in the IF statement I am trying to add.

 

[jamminjaymeyer]

OK I figured out my problem. I used Activecell.formula rather than just active cell. That must mean to take the formula value of the cell rather than evaluating it.

Thanks to everyone for helping.
jamminjaymeyer

 

[AnotherHiggins]

The end goal of this is really to save me some typing, I don't want to have to go back to each cell and type in the IF statement I am trying to add.

Can you tell me a little more about this? Are you going to put in an IF statement in several cells in the same column? There's probably a better way to duplicate the IF statement than with code.

As far as the original question, sorry, I wasn't thinking. To get the IF formula to look normal, you'd actually use this:

[COLOR=blue white]Range("b2") = "=if(N7=0,0,Right(" & ActiveCell.Address & ", Len(" & ActiveCell.Address & ") - 1))"
[/color]
But again, I stress that we should find a more efficient way to do this.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886