VBA copy formula in cell

opislak

Board Regular
Joined
Feb 28, 2017
Messages
68
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
VBA Code:
Sheets("StdLine").Range("K2").Formula = stdformula
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:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
@opislak
I see nothing wrong with the principle nor the code.
I think the issue is that your formula text in K4 is not correct and is therby generating the error.

You would appear to have too many parameters in one of the ROUND functions???

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)"))

Hope that helps.
 
Upvote 0
@opislak
I see nothing wrong with the principle nor the code.
I think the issue is that your formula text in K4 is not correct and is therby generating the error.

You would appear to have too many parameters in one of the ROUND functions???

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)"))

Hope that helps.
Thanks for helping, Snakehips.
However, this was not the issue, as the formula worked fine on its own (in Excel, not in VBA).
Meanwhile, I know what went wrong... as I am a European, it seems that keyboard layout may cause this problem. So, instead of using .Formula, one should use .FormulaLocal.
Problem solved, works like a charm.
But again, thank you for your input, much appreciated!
Patrick.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top