Edit formula in cell with a macro.

[Estimator#1]

I have a repetitive need to modify a formula the same way on a consistent basis.

If I had a true a keystroke type macro, it would be very easy to program... but VBA does not seem to work that way.

The specific example allows me to modify a cells contents based on the mere existence of data in another cell. I end up doing this a lot after the original formula is written.

I am trying to change from (With ActiveCell=k8)

=H8*G8/I8

to

=IF(E8<>"",H8*G8/I8,"").

After some scrounging around i have come up with this...and assign it to a hot key.

Sub ifzero()
'
' ifzero Macro
main = ActiveCell.Formula
pre = "=IF(RC[-6]<>"""","
Post = ","""")"
ActiveCell.FormulaR1C1 = pre & Mid(main, 2, 100) & Post

End Sub


I used the Mid statement to get rid of the "=" that is at the beginning of the line. (Without that I get an error)

This does not work. It puts the cell addresses in the formula in single quiotes... ie

=IF(I8<>"",'H8'*'G8'/'I8',"")

Is there a way to tell excel to open the editor, move to the beginning, add something and close the editor, or is this type of VBA the best way to do this.

I have thought about find replace, but for me, editing the cell directly is not any slower, esp when only dealing with one or two cells at a time.

 

[Scott Huish]

Sub test()
Dim dq As String
dq = Chr(34) & Chr(34)
ActiveCell.Formula = "=IF(E" & ActiveCell.Row & "=" & dq & "," & dq & "," & Mid(ActiveCell.Formula, 2) & ")"
End Sub

I did shorten your formula to this though, which will do the same thing:

=IF(E8="","",H8*G8/I8)

 

[Estimator#1]

Thanks,

I do not know enough about VBA to understand... I'll dig into it and figure it out.

What about if I want the the E8 reference to be relative so that I can use the macro in other various places in the sheet?

I Tried

ActiveCell.Formula = "=If(" & ActiveCell.Column - 1 & ActiveCell.Row & "=" & dq & "," & dq & "," & Mid(ActiveCell.Formula, 2) & ")"

with results of

=IF(58="","",H8*G8/I8)


Any thoughts there.

I did make the macro a little more interactive to get some of the functionality out of it

I added

Dim col As String
col = Application.InputBox(prompt:="Col Letter For pointer", Type:=2)

and

ActiveCell.Formula = "=IF(" & col & ActiveCell.Row & "=" & dq & "," & dq & "," & Mid(ActiveCell.Formula, 2) & ")"


It is a work around, but better than I had!!!

 

[Scott Huish]

Sub test()
Dim col As Long, dq As String
dq = Chr(34) & Chr(34)
col = Application.InputBox(prompt:="Col Letter For pointer", Type:=2)
ActiveCell.Formula = "=IF(" & Cells(ActiveCell.Row, col).Address(0, 0) & "=" & dq & "," & dq & "," & Mid(ActiveCell.Formula, 2) & ")"
End Sub

 

[Estimator#1]

Thanks!!!