Excel: Copying formula with variables into cell gives error

[Taktiker]

My code...

Please Login or Register  to view this content.

creates for example: "=IF($Q$4-12=0;"";$Q$4-12)"

This works wonderfully, if mycell2.value is a normal number (1 or 5 or something like that). But if the cell's value is 5,5 it gives me a runtime error 1004: Application defined error ...

Anyone knows how to fix my code, so it also calculates with decimal digits?

[Norie]

Is the value in Mycell2 text?

If it was a number I would have assumed Mycell2.Value would return 5.5 not 5,5.

[Taktiker]

Please Login or Register  to view this content.

Thats the full code if it helps. All mycell2-cells are part of a table and contain normal numbers. Some of these numbers are 10, 50 etc. and some are 17,5 or 22,5. And the latter ones create errors. Is it understandable? If you need other information, tell me.

[Norie]

It isn't really understandable why you are getting the error unless values like 17,5 and 22,5 are being treated as text.

Can you attach a workbook?

[Taktiker]

Ernährungs-Planer_Public.xlsm

Here it is. I added comments for help.

[Norie]

The code works without error when I run it.

Did you change anything in the workbook you attached?

[Taktiker]

Strange. Of course I took only that sheet and added some comments. But I tried the macro again and it produced the same error for me.

"Microsoft Visual Basic
Run-Time error '1004':
Application-defined or object-defined error.

Try to run the macro twice. Or delete all values in the second green table. Then add some decimal numbers in the first green table. Like in the column "Wdh." a 12,5. And then run the macro twice.

[Norie]

Sorry, but sill no error.

I'm in the UK though and the decimal separator is . not , .

[Taktiker]

yeah, that seems to be the problem
I reduced the formula to that code:
Mycell2.Offset(0, 1).Value = "=" & Mycell2.Value & "+ 0.5"
If the number is a non-decimal number and I add 0.5 like in that formula, it is fine. If I write that code:
Mycell2.Offset(0, 1).Value = "=" & Mycell2.Value & "+ 0,5"
I get an error. But the problem is, my cells are formatted with , and not "." . I cant change the mycell2.value from 12,5 to 12.5 to make it work. Do you maybe know how I can do that?

[JapanDave]

Try this,

Please Login or Register  to view this content.

[Norie]

Taktiker

EXCEL/VBA should recognise 5,5 as a number and convert it to 5.5 which is the format VBA prefers.

[Taktiker]

You are right. I experimented again and found out that I get an error not by the .5 number but by the "-" before the mycell2.value. I changed the code now and it does all calculating in the macro and just pastes the result into the cell. Without an error

Please Login or Register  to view this content.

[Norie]

That's very strange.

What happens if you try this?

Please Login or Register  to view this content.

That's two changes I've made - first putting the formula in a string before putting it in a cell and second using .Formula instead of .Value, since it is a formula not a value going in the cell.

Actually just changing from .Value to .Formula is probably worth a shot in the original code.

[Taktiker]

It generated also an error. Maybe my excel is a bit weird, but Im not in such a bad situation that I am willing to reinstall it and update it.
Thanks anyways for your help, that the case couldnt be solved that way was not your mistake, so I give you rep.

[Norie]

No problem.

Still kind of buggin me though.

I know VBA seems to work in US format but the only problems I've had in the past is with dates, not decimals.