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

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

So appreciative of this Philip!
I have edited the module with the updated code.
However, the run aborts and generates a run-time error 1004.
Can you suggest a fix?
This is Mac Excel 2011 and Mac Excel 2016. Microsoft Office 365. Version 16.12
Eric

Forum Discussion

Convert Formulas into Values when cell value changes Mac Excel 2011