Convert Formulas into Values when cell value changes Mac Excel 2011

Hello, Here's what I want to do: There are several columns linking cells to a data source. These are date-triggered to capture the value on a certain date. upon triggering, the cell value will...

Excel on Mac

Macros and VBA

Philip West
Aug 13, 2018
Oh i see what happened.
“I”
Its the wrong type of quote marks. not really sure how that happened.

Replace this line:
For i = start_row To ActiveSheet.Cells(ActiveSheet.Rows.Count, "I").End(xlUp).Row

Philip West

Iron Contributor

Aug 12, 2018

Not sure if you using a Mac makes a difference to anything, so hopefully this works for you.

If you add this a macro assigned to a button then when you press the button it will run down the column and replace the formulas with the values where the cell has something in it.

Sub conditional_replace()

start_row = 4

    For i = start_row To ActiveSheet.Cells(ActiveSheet.Rows.Count, "j").End(xlUp).Row
        If ActiveSheet.Range("j" & i).Value <> "" Then
            ActiveSheet.Range("j" & i).Value = ActiveSheet.Range("j" & i).Value
        End If
    
    Next

End Sub

Just be sure to edit the start_row number to whatever row your data starts at, and all the "j"s (there are 4 of them) to whatever column you want it to check.

Back up anything important before you press any buttons :D There is no Undo once run this.