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 change.

When the cell value changes I want to unlink the cell; ie, convert the formula into a value.

I understand that one way of doing this is using vba.

I need assistance!

Thank you

Eric

Here is an example of the date triggered formula in one of the cells:

=IF(AND(TODAY()>=DATE(2018,12,7),TODAY()<=DATE(2018,12,18)),'SEM 1 Summary'!S5,"")

where the data source is 'SEM 1 Summary'!S5

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

10 Replies

Philip West

Steel 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.

ericmjmacdonald

Copper Contributor

Aug 13, 2018

Hi Philip,
Getting an error. Here is what I am trying to run:

Sub conditional_replace()

start_row = 5

    For i = start_row To ActiveSheet.Cells(ActiveSheet.Rows.Count, “I,O,U,AC,AI,AO,AU”).End(xlUp).Row
        If ActiveSheet.Range("I,O,U,AC,AI,AO,AU" & i).Value <> "" Then
            ActiveSheet.Range("I,O,U,AC,AI,AO,AU" & i).Value = ActiveSheet.Range("I,O,U,AC,AI,AO,AU" & i).Value
        End If

    Next

End Sub

Philip West

Steel Contributor

Aug 13, 2018

Sorry,

I missed the part where you said you had several columns.

it will need to be like this:

Sub conditional_replace()

start_row = 5

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

 If ActiveSheet.Range("O" & i).Value <> "" Then
            ActiveSheet.Range("O" & i).Value = ActiveSheet.Range("O" & i).Value
        End If
 
 If ActiveSheet.Range("U" & i).Value <> "" Then
            ActiveSheet.Range("U" & i).Value = ActiveSheet.Range("U" & i).Value
        End If
 If ActiveSheet.Range("AC" & i).Value <> "" Then
            ActiveSheet.Range("AC" & i).Value = ActiveSheet.Range("AC" & i).Value
        End If
 If ActiveSheet.Range("AI" & i).Value <> "" Then
            ActiveSheet.Range("AI" & i).Value = ActiveSheet.Range("AI" & i).Value
        End If
 If ActiveSheet.Range("AO" & i).Value <> "" Then
            ActiveSheet.Range("AO" & i).Value = ActiveSheet.Range("AO" & i).Value
        End If
 If ActiveSheet.Range("AU" & i).Value <> "" Then
            ActiveSheet.Range("AU" & i).Value = ActiveSheet.Range("AU" & i).Value
        End If


   
    Next

End Sub

ericmjmacdonald
Copper Contributor
Aug 13, 2018
Philip,
Thank you for this.
I am trying to figure out how to incorporate this into the relevant sheet.
I appreciate your help.
Eric

Forum Discussion

Convert Formulas into Values when cell value changes Mac Excel 2011

10 Replies

Resources