VBA Sum formula of a union of two ranges

[Hermac]

Hello

Could anyone tell me what I do wrong here ?

Sub TestSumFormula()

Dim R As Range

Set R = Union([J3:J22], [J25:J34])

MsgBox R.Address(1, 0)

[J35].Formula "=sum(" & R.Address(1, 0) & ")"

End Sub

MsgBox says “J$3:J$22,J$25:J$34” as expected but [J35] disappoints me with an Error 438.

Thanks a lot for any tip or better formula code for “sum” of a Union range, all cells of which are numbers.

 

[Joe4]

So close! You were just missing an equal sign.

This:

[J35].Formula "=sum(" & R.Address(1, 0) & ")"

should be this:

[J35].Formula = "=sum(" & R.Address(1, 0) & ")"

 

[Hermac]

Wow, Thanks a truckload Joe4. How could I not have noticed this after meticulously staring at this line for half an hour. Anyway, you made my day and stopped me loosing time.
Ofcourse, your answer is the only one and it solved my problem right away. But now again I can't even find the "Mark as solution" button that i'd like to click with an exclamation mark. I'm not drunk though, maybe just stupid or just age 75.
Have a beautiful day.
Herman

 

[Joe4]

Wow, Thanks a truckload Joe4. How could I not have noticed this after meticulously staring at this line for half an hour. Anyway, you made my day and stopped me loosing time.
Ofcourse, your answer is the only one and it solved my problem right away. But now again I can't even find the "Mark as solution" button that i'd like to click with an exclamation mark. I'm not drunk though, maybe just stupid or just age 75.
Have a beautiful day.
Herman

You are welcome.
I marked it for you. It is the check mark icon to the right of the post you want to mark.

No worries! Sometimes you just need a second set of eyes to look at it. I have the same issue myself sometimes. I will be starting at it, and I see what I want to see, and not what is really there!
I find sometimes if I put it away and come back to it the next day, I have a better chance of spotting the error myself (but not always!).