Convert several lines of string text (think range names) to actual formulas using a macro

[mwf05]

I am trying to determine how to edit several rows existing string text into a live formula. For instance STEPS_TO_FRONT_DOOR * GUESTS_PER_DAY, STEPS_TO_BACK_DOOR * DOG_BREAKS, etc. When I run the macro to convert the string text to a formula F2, Ctrl+Home, = Enter is saves the string text that was in the cell with the result. I just want it to edit the cell and convert it to an actual formula by putting an "=" at the beginning and then I want it to go to the next row with string text and convert it to a formula. Unfortunately, it just replicates the first formula edit when the macro was recorded.

 

[Micron]

Not sure what you're saying. If you successfully convert the string to a formula, what you'll see is the formula result and not the formula text. If you want to see the formula as a string, start that string with a single quote. If that doesn't help, post what you're trying to convert and what you're trying to convert it to.

 

[MARK858]

The macro below

Sub ConvCell()
    Dim myCell As Range, strRng As String

    For Each myCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        strRng = myCell.Value
        myCell.Formula = "=" & strRng
    Next

End Sub

converts

Book1
	A	B	C	D	E	F	G
2	Back*Dog						6
3	Front*Guests						3
4	Guests*Dog						2
5	Front*Dog						5
Sheet1

to

Book1
	A	B	C	D	E	F	G
2	10						6
3	18						3
4	15						2
5	30						5
Sheet1

Cell Formulas
Range	Formula
A2	A2	=Back*Dog
A3	A3	=Front*Guests
A4	A4	=Guests*Dog
A5	A5	=Front*Dog

Named Ranges
Name	Refers To	Cells
Back	=Sheet1!$G$4	A2
Dog	=Sheet1!$G$5	A2, A4:A5
Front	=Sheet1!$G$2	A5, A3
Guests	=Sheet1!$G$3	A3:A4

 

[mwf05]

MARK858,
Thank you, your process works, but not for the scenario I have. My cells (while in the same column) that I want to convert aren't all together. They are several rows apart which varies. The macro I tried by recording my steps is:
Sub Macro4()
'
' Macro4 Macro
'

'
Selection.End(xlDown).Select
ActiveCell.FormulaR1C1 = "= SQ_FT_COOLER_FLOOR"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

When the above is repeated for each cell with a formula that needs to be converted, each cell has the same formula of = SQ_FT_COOLER_FLOOR.

Your process worked, but all the rows that had nothing were also converted into formula cells.

 

[MARK858]

Sub ConvCell()
    Dim myCell As Range, strRng As String

    For Each myCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If myCell <> "" Then
            strRng = myCell.Value
            myCell.Formula = "=" & strRng
        End If
    Next

End Sub

 

[mwf05]

Sub ConvCell()
    Dim myCell As Range, strRng As String

    For Each myCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If myCell <> "" Then
            strRng = myCell.Value
            myCell.Formula = "=" & strRng
        End If
    Next

End Sub

Works perfectly! Thank you very much.

 

[MARK858]

You're welcome