FormulaR1C1 in combination with constants

[Sargad_Strut]

Hi all,

I have a rather simple code where I want to split a row in two (say a bill). Some of the cells will contain the same information (e.g. date), which is just copied from the row I am splitting. When the row is split, I want some cells in the new row to be the residuals of the total amount (as in the original row) minus whatever value I descide to assign to that same row after running the code. E.g. the bill is for $500, I press "Split" and a row is inserted below. I enter $300 in the original row and the one below now shows $200. This is what I have:

Sub delaRad()

    rad = ActiveCell.Row
    summa = Range("G" & rad).Value
    moms = Range("H" & rad).Value
    tot = Range("I" & rad).Value


    Rows(rad + 1 & ":" & rad + 1).Select
    Selection.Insert Shift:=xlDown
    
    Range("B" & rad & ":F" & rad).Select
    Selection.Copy
    Range("B" & rad + 1).Select
    ActiveSheet.Paste
    
    Range("G" & rad + 1) = "=" & summa & "-R[-1]C[0]"
    Range("H" & rad + 1) = "=" & moms & "-R[-1]C[0]"
    Range("I" & rad + 1) = "=" & tot & "-R[-1]C[0]"
    
    Application.CutCopyMode = False
    Range("B" & rad + 1).Select
        
End Sub

At first I thught it worked perfectly, but I later found out that this was only the case for integers; as soon as there are decimals the code crashes. How can I solve this? The three cells you see in the code are formatted as accounting. Or maybe there's a better way of doing this..

Best regards,
Tobias

 

[Norie]

Tobias

Where are the decimals coming from?

 

[Sargad_Strut]

Hi Norie,

I am pulling three numbers from the row I am splitting, col G, H, and I. If any of those numbers contain decimal places the code crashes. This part to be precise:

    Range("G" & rad + 1) = "=" & summa & "-R[-1]C[0]"
    Range("H" & rad + 1) = "=" & moms & "-R[-1]C[0]"
    Range("I" & rad + 1) = "=" & tot & "-R[-1]C[0]"

I.e. "summa", "moms", and "tot" are pulled from cells, but something is clearly wrong and I'm not sure about the format. I get a 1004 run-time error; application-defined or object-defined error. Any help is greatly apprechiated.

Thanks,
Tobias

 

[Norie]

Tobias

I can't replicate the behaviour.

What do you use for the decimal separator?

What happens if you change the code to this?

Range("G" & rad + 1).FormulaR1C1 = "=" & summa & "-R[-1]C[0]"
Range("H" & rad + 1).FormulaR1C1 = "=" & moms & "-R[-1]C[0]"
Range("I" & rad + 1).FormulaR1C1 = "=" & tot & "-R[-1]C[0]"

 

[Sargad_Strut]

Hi,

I think you're onto something here, seems to my dencimal separator (comma) is causing the problem. It has never really been a problem before, I just have to be careful to use "." in VBA. I thought that .Value would extract exactly what it sounds like, but I guess it doesn't. Can you suggest a good way to solve this?

Thank you for your time,
Tobias

 

[Sargad_Strut]

Forgot the second part; the code you posted encounters the same problem as my original one.

 

[Norie]

Tobias

So you use , as the decimal separator and ; in formulas to separate parts of formulas?

Perhaps instead of FormulaR1C1 you could try using FormulaLocal?

 

[Sargad_Strut]

Correct.

YES, that's the solution I was looking for! I also found FormulaR1C1Local in the list which does the trick as well.

Guess my earlier statement about .Value was wrong; is does indeed extract the value. The problem was more likely when I tried to put the value back as a formula after "=". The decimal separator is shown as a comma in the locals window, if I simply put test=0.25 it would show as 0,25 as well. But when I want to use it in a formula in the sheet it will likely still be treated as a "." decimal point, which causes the code to break down when not using FormulaLocal. Just trying to get my head around this for future reference. Does my resoning seem about right?

Thank you, Norie!
Tobias

 

[Norie]

Tobias

That seems about right.