[VBA] Edit formula

[darkfreedoom]

Dear Mr/Ms,

I would like to create a VBA to edit my excel cells' formula by adding some info to the starting & ending of the formula.

I created this but it doesn't work:

Dim Srow, Erow, Lrow, Scol, Ecol, Lcol
Srow = Selection.Row
Erow = Srow + Selection.Rows.Count - 1
Scol = Selection.Column
Ecol = Scol + Selection.Columns.Count - 1


For Lrow = Srow To Erow Step 1
    Dim i
    i = Cells(Lrow, Scol).FormulaR1C1
    If IsEmpty(i) Then
        GoTo CSub
    Else
        Cells(Lrow, Scol).FormulaR1C1 = "=Round(" & i & ",2)"
    End If
CSub:
Next Lrow

if I change i = Cells(Lrow, Scol) ,
it run, but it paste the value of the cell.
i want it to paste the formula back.
Any idea?

Thanks in advance.

Regards
Vincent
WinXP / MSExcel07

 

[Jon von der Heyden]

Perhaps;

Cells(Lrow, Scol).FormulaR1C1 = "=Round(" & Mid$(i,2) & ",2)"</pre>

 

[yogiraj]

Hi friend,

use below code--->

************************************************************
Sub MyCode()

Dim Srow, Erow, Lrow, Scol, Ecol, Lcol
Srow = Selection.Row
Erow = Srow + Selection.Rows.Count - 1
Scol = Selection.Column
Ecol = Scol + Selection.Columns.Count - 1

For Lrow = Srow To Erow Step 1
Dim i As String
i = Replace(Cells(Lrow, Scol).Formula, "=", "")
If IsEmpty(i) Then
GoTo CSub
Else
Cells(Lrow, Scol).Formula = "=Round(" & i & ",2)"
End If
CSub:
Next Lrow

End Sub

************************************************************

Yogesh Dalimbkar

 

[darkfreedoom]

Hi friend,

use below code--->

************************************************************
Sub MyCode()

Dim Srow, Erow, Lrow, Scol, Ecol, Lcol
Srow = Selection.Row
Erow = Srow + Selection.Rows.Count - 1
Scol = Selection.Column
Ecol = Scol + Selection.Columns.Count - 1

For Lrow = Srow To Erow Step 1
Dim i As String
i = Replace(Cells(Lrow, Scol).Formula, "=", "")
If IsEmpty(i) Then
GoTo CSub
Else
Cells(Lrow, Scol).Formula = "=Round(" & i & ",2)"
End If
CSub:
Next Lrow

End Sub

************************************************************

Yogesh Dalimbkar

thanks.
i see my mistakes.
so i have to eliminate the "=" sign before adding the info.


thanks, LIKE given

///

but if i apply with your codes, when i selected blank cells, they won't be blank anymore :/

 

[darkfreedoom]

Thanks for your hints, and here's the code i rebuilt that work fine

Dim Srow, Erow, Lrow, Scol, Ecol, Lcol
Srow = Selection.Row
Erow = Srow + Selection.Rows.Count - 1
Scol = Selection.Column
Ecol = Scol + Selection.Columns.Count - 1


For Lrow = Srow To Erow Step 1
    Dim i
    i = Replace(Cells(Lrow, Scol).FormulaR1C1, "=", "")
    If i = "" Then
        GoTo CSub
    Else
        Cells(Lrow, Scol).FormulaR1C1 = "=Round(" & i & ",2)"
    End If
CSub:
Next Lrow