Run-time error 1004 when trying to resize a multi area range object

[barbosajulio77]

Thank you.

I am not good in VBA how to i add this to my complete code so it works,

Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17:C17, E17")

---

Sorry ignore my previous reply.

What I meant is how to add your working suggestions to my code that the range

("A17:C17, E17")

works?

Thank you

[DocAElstein]

Hi

...how to add your working suggestions to my code that the range ("A17:C17, E17") works?..

I am not sure exactly what it is you are trying to do, so I am not sure what exactly you mean by “works”
I can’t be sure what your problem is without knowing exactly what you are trying to do. I might also need to see all your files and data, along with a full description of what you are trying to do.

---

My initial investigation and experimenting for you suggested that the resize function errors if applied to a multi area range object.
"A17:C17, E17" is a multi area range object.
( Area item 1 is cells A17:C17 __ Area item 2 is the cell E17 )

As I showed it has two areas. Your code line that errors tries to apply the resize function to that multi area range object, so it errors.

So we may have found out what is causing the error.

---

As I also mentioned at the end of my first post, the resize function actually works on the top left cell of the range you give it.

So lets look again in detail at the erroring code bit

rngDst.Offset(0, 4).Resize(rowsize + 1, 1).Value

That is the same as
wkbDst.Worksheets("STATEMENT").Range("A17:C17, E17").Offset(0, 4).Resize(rowsize + 1, 1).Value
So you can see the problem again. You are trying to resize the multi area range object, "A17:C17, E17"
We have found that this will error

I don’t know what you want to do. I do not know what you mean by “works”
But let me take a guess that you want to resize the cell A17, since that is the top left cell of your total range. Remember: The resize function resizes based on the top left cell of the range that you give it
If you want to resize based on the top left of your range, then a modification to the problem code line would be to do this:
rngDst.Areas.Item(1).Offset(0, 4).Resize(rowsize + 1, 1).Value
That is the same as
wkbDst.Worksheets("STATEMENT").Range("A17:C17, E17").Areas.Item(1).Offset(0, 4).Resize(rowsize + 1, 1).Value
It is also the same as this
wkbDst.Worksheets("STATEMENT").Range("A17:C17").Offset(0, 4).Resize(rowsize + 1, 1).Value

Do you see the difference? - The modified code line is now applying the resize function to a single area range object, "A17:C17".
( Range("A17:C17, E17").Areas.Item(1) = Range("A17:C17") )
So it probably will not now error.

---

The short answer

Change this

                    rngDst.Offset(0, 4).Resize(rowsize + 1, 1).Value = rngSrc.Value '

to this

                    rngDst.Areas.Item(1).Offset(0, 4).Resize(rowsize + 1, 1).Value = rngSrc.Value

If that is no good for you, then I can’t help more unless you supply me some data and files and explain fully what it is that you are trying to do.


Alan