Hello all,
In a sheet, I refer to a formula on another sheet (in cell K2). However, if an entire line is deleted in the first sheet, the formula on the second sheet gives #REF!.
I therefore typed the text of the formula (without the "="-sign) in another cell (in cell K4). I would like to restore the formula in K2 by copying the text in K4 and pasting it in K2.
This code does NOT work, it returns "Runtime error 1004: application-defined or object-defined error" on the line
For your information: the formula is this:
CONCATENATE(RAL!B2;IF(RAL!C2="";"";"|"&RAL!C2);": P"&MONTH(RAL!A2)&"/"&RIGHT(YEAR(RAL!A2);2);"-R"&MONTH(RAL!I2)&"/"&RIGHT(YEAR(RAL!I2);2);":";IF(RAL!D2="";"";" "&RAL!D2);IF(RAL!E2="";"";" ("&RAL!E2&")");": F"&ROUND(RAL!G2;2)&RAL!F2;" > iP"&IF(RAL!F2="d";ROUND(RAL!G2/7,2*8;2)&"d = "&ROUND(RAL!G2*8;2)&"h";ROUND(RAL!G2;2)&"h");TEXT(I2;" (dd/mm/jjjj uu:mm:ss)"))
Here is a part of my code:
In a sheet, I refer to a formula on another sheet (in cell K2). However, if an entire line is deleted in the first sheet, the formula on the second sheet gives #REF!.
I therefore typed the text of the formula (without the "="-sign) in another cell (in cell K4). I would like to restore the formula in K2 by copying the text in K4 and pasting it in K2.
This code does NOT work, it returns "Runtime error 1004: application-defined or object-defined error" on the line
VBA Code:
Sheets("StdLine").Range("K2").Formula = stdformula
CONCATENATE(RAL!B2;IF(RAL!C2="";"";"|"&RAL!C2);": P"&MONTH(RAL!A2)&"/"&RIGHT(YEAR(RAL!A2);2);"-R"&MONTH(RAL!I2)&"/"&RIGHT(YEAR(RAL!I2);2);":";IF(RAL!D2="";"";" "&RAL!D2);IF(RAL!E2="";"";" ("&RAL!E2&")");": F"&ROUND(RAL!G2;2)&RAL!F2;" > iP"&IF(RAL!F2="d";ROUND(RAL!G2/7,2*8;2)&"d = "&ROUND(RAL!G2*8;2)&"h";ROUND(RAL!G2;2)&"h");TEXT(I2;" (dd/mm/jjjj uu:mm:ss)"))
Here is a part of my code:
VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Target.EntireRow.Address Then
stdformula = "=" & Sheets("StdLine").Range("K4")
Sheets("StdLine").Range("K2").Formula = stdformula
Application.CutCopyMode = False
Exit Sub
End If
End Sub