Forum Discussion

Copper Contributor

Aug 12, 2018

Solved

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

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

Iron 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
Hi Philip,
Just to clarify. It is a run-time 1004 error:
Application-defined or object-defined error.
Eric
- Philip West
  Iron Contributor
  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
  - ericmjmacdonald
    Copper Contributor
    Aug 14, 2018
    Philip,
    That was it! Works beautifully. Thank you so much!
    Eric
- Philip West
  Iron Contributor
  Aug 13, 2018
  Can you let me have a copy of the spreadsheet with some dummy data in it? I'll get it working for you.
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