Forum Discussion
Convert Formulas into Values when cell value changes Mac Excel 2011
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
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.
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
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 SubHi Philip,
Just to clarify. It is a run-time 1004 error:
Application-defined or object-defined error.
Eric
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
- 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