This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(92.8, 15%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELK%3C/text%3E%3C/svg%3E)
I have a bit of a problem, I get a runtime error 7 when using .value = .value. It is a relative small data set (maximum 60 rows) I suspect, it is because I am part of insider program, and using the new formulas (DETECTLANGUAGE AND TRANSLATE). Anyone else have faced this issue?
regards
Lars
How to reproduce the problem practically and under what circumstances does it make sense to write “.value = .value”?
I am using danish version, formulas showing semicolon instead of comma. I am using .value = .value because the macro is in a different workbook, and I want to get rid of references, and I also need to be able to easy copy/paste the text into WhatsApp.
This is formula in O2 =TRANSLATE(F2;DETECTLANGUAGE(LEFT(F2;9));IF([@Language]="dk";"da";"en"))
This is the formula in P2
=IF([@Language]="DK";"OVERVÅGNING TIL I AFTEN" & CHAR(10) &
A2 & " STOP " & B2 & " (" & D2 & ")" & CHAR(10) &
[@[Translated Comment]] & IF(AND(J2<>""; K2<>""); " se billeder"; IF(J2<>""; " se billede"; ""));
"MONITORING FOR TONIGHT" & CHAR(10) &
A2 & " STOP " & B2 & " (" & D2 & ")" & CHAR(10) &
[@[Translated Comment]] & IF(AND(J2<>""; K2<>""); " look at pictures"; IF(J2<>""; " look at picture"; "")))
This is the actual code, that is giving the runtime error
With Range("tblOvervågning[Text To Send]")
.Value = .Value
'Convert the formula to values for easy - copy-paste
End With
It shows #BUSY, and I suspect, that is triggering the runtime error.
Is there a solution to get around #BUSY
Thank you in advance
Lars
When is the shown VBA code executed and what happens if .Value = .Value is removed?
It is executed in the end of the macro, and it works fine if I remove .Value = .Value, and I can manually copy and paste as value.
Perhaps the statement could lead to a recursive infinite execution until the memory is exhausted. It depends on when the displayed VBA code executed.