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

**barbosajulio77** · 08-12-2021, 11:55 PM

Hi

have the code below but I am getting run-time error 1004 application-defined or object-defined error and highlight this line:
rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value

Complete code:

Code:

Sub ImportRawData()
 
    Dim c           As Long
    Dim Col         As Variant
    Dim Filename    As String
    Dim Filepath    As Variant
    Dim rngBeg      As Range
    Dim rngEnd      As Range
    Dim rngDst      As Range
    Dim rngSrc      As Range
    Dim rowsize     As Long
    Dim wkbDst      As Workbook
    Dim wkbSrc      As Workbook
    
        Set wkbDst = ThisWorkbook
        Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17:C17, E17")
        
        Filepath = "C:\Users\jose.rossi\Desktop\AP_NCL_VENDOR STATEMENT.xlsm"
        Filename = "apvtrn.csv"
        
        On Error Resume Next
            Set wkbSrc = Workbooks(Filename)
            If Err = 9 Then
                If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
                Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
                If Filename = "False" Then Exit Sub
                Set wkbSrc = Workbooks.Open(Filename)
            End If
        On Error GoTo 0
        
        ' Clear previous data.
        'rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count).ClearContents
        
        ' Import the data.
        With wkbSrc.Worksheets("apvtrn").UsedRange
            ' Step through the source data columns.
            For Each Col In Array("DM", "DI", "DN", "DQ" )
       
' Data starts on row 1.
                Set rngBeg = .Parent.Cells(1, Col)
                
                ' Find the row where the data ends in this column.
                Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)
                
                ' Number of rows in this column.
                rowsize = rngEnd.Row - rngBeg.Row
                
                If rowsize > 0 Then
                    Set rngSrc = .Parent.Range(rngBeg, rngEnd)
                    rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
                End If
                
                ' Increment the column offset.
                c = c + 1
            Next Col
        End With
        
End SubSub ImportRawData()
 
    Dim c           As Long
    Dim Col         As Variant
    Dim Filename    As String
    Dim Filepath    As Variant
    Dim rngBeg      As Range
    Dim rngEnd      As Range
    Dim rngDst      As Range
    Dim rngSrc      As Range
    Dim rowsize     As Long
    Dim wkbDst      As Workbook
    Dim wkbSrc      As Workbook
    
        Set wkbDst = ThisWorkbook
        Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17:C17, E17")
        
        Filepath = "C:\Users\jose.rossi\Desktop\AP_NCL_VENDOR STATEMENT.xlsm"
        Filename = "apvtrn.csv"
        
        On Error Resume Next
            Set wkbSrc = Workbooks(Filename)
            If Err = 9 Then
                If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
                Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
                If Filename = "False" Then Exit Sub
                Set wkbSrc = Workbooks.Open(Filename)
            End If
        On Error GoTo 0
        
        ' Clear previous data.
        'rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count).ClearContents
        
        ' Import the data.
        With wkbSrc.Worksheets("apvtrn").UsedRange
            ' Step through the source data columns.
            For Each Col In Array("DM", "DI", "DN", "DQ" )
       
' Data starts on row 1.
                Set rngBeg = .Parent.Cells(1, Col)
                
                ' Find the row where the data ends in this column.
                Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)
                
                ' Number of rows in this column.
                rowsize = rngEnd.Row - rngBeg.Row
                
                If rowsize > 0 Then
                    Set rngSrc = .Parent.Range(rngBeg, rngEnd)
                    rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
                End If
                
                ' Increment the column offset.
                c = c + 1
            Next Col
        End With
        
End Sub

Thank you

**DocAElstein** · 08-14-2021, 12:17 PM

Hello barbosajulio77
Welcome to ExcelFox, the thinking man’s excel forum…

I am not 100% sure what your problem is, but possibly the Resize function does not work on a multi area range object. ( I don't know this for sure ** )

Let me explain:

This is your rngDst, A17:C17, E17

_Row\^Col	A	B	C	E
16
17	Cell in Area 1	Cell in Area 1	Cell in Area 1	Cell in Area 2
18

Worksheet: STATEMENT

Your range, rngDst , shown above has two areas, as I have shown, A17:C17 is Area item 1 and E17 is Area item 2

I did some experimenting, like in the macros below, and you can see that the first macro, ( which does something similar to your macro at your problem code line), does not work. It gives the same error as you are seeing.
The other 3 macros do work.

The first macro, which errors, is trying to resize a multi area range. The other macros, which do work, are applying the resize function to a single area range object

This first macro will error

Code:

Sub DoesNotWork() ' This will error -  run-time error 1004 application-defined or object-defined error
Dim rngDst As Range
 Set rngDst = ActiveSheet.Range("A17:C17, E17")
 Let rngDst.Resize(2, 1).Value = "You will never see this" ' ' This code line will error -  run-time error 1004 application-defined or object-defined error
End Sub

The following 3 macros all work and give the results shown

Code:

Sub DoesWork_1()
Dim rngDst As Range
 Set rngDst = ActiveSheet.Range("A17:C17")
 Let rngDst.Resize(2, 1).Value = "Resized Cells of Single Area Range"
End Sub

_Row\^Col	A	B	C	D
16
17	Resized Cells of Single Area Range
18	Resized Cells of Single Area Range
19

Worksheet: STATEMENT

Code:

Sub DoesWork_2()
Dim rngDst As Range
 Set rngDst = ActiveSheet.Range("A17:C17, E17")
 Let rngDst.Areas.Item(1).Resize(2, 1).Value = "Resized Cells of Area 1 of multi area Range"
End Sub

_Row\^Col	A	B	C	D
16
17	Resized Cells of Area 1 of multi area Range
18	Resized Cells of Area 1 of multi area Range
19

Worksheet: STATEMENT

Code:

Sub DoesWork_3()
Dim rngDst As Range
 Set rngDst = ActiveSheet.Range("A17:C17, E17")
 Let rngDst.Areas.Item(2).Resize(2, 1).Value = "Resized Cells of Area 2 of multi area Range"
End Sub

_Row\^Col	A	B	C	D	E
16
17					Resized Cells of Area 2 of multi area Range
18					Resized Cells of Area 2 of multi area Range
19
20

Worksheet: STATEMENT

** I was not aware that the resize function errors if applied to a multi area range object, so maybe I have learnt something as well from this Thread…
( Note , that as is usual and as is known to me, the resize function works on the top left of the supplied range. So for the first 2 working macros it is resizing cell A17. For the last macro it is resizing cell E17 )

Hope that is some help to you
Alan

**barbosajulio77** · 08-15-2021, 09:12 AM

Thank you.

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

Code:

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

Code:

("A17:C17, E17")

works?

Thank you

**DocAElstein** · 08-15-2021, 11:06 AM

Hi

Originally Posted by barbosajulio77

...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

Code:

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

to this

Code:

                    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

**barbosajulio77** · 08-17-2021, 12:40 AM

statement.PNG

statement.PNG

Thank you for the response.

what i am looking for is to import the data

Code:

("A17:E17")

but skip column D17.

trying to add an image of spreadsheet.

**DocAElstein** · 08-17-2021, 01:55 PM

Hi

Originally Posted by barbosajulio77

statement.PNGwhat i am looking for is to import the data

Code:

("A17:E17")

but skip column D17.

I am sorry, but I still do not understand what you want.

Originally Posted by barbosajulio77

..trying to add an image of spreadsheet.

The image is helpful to have, but it still does not explain what you are doing. The image does not explain what you want.

It might be helpful to have some sample files from you. Please try to keep the file size and the data size to the smallest possible. We only need as much information and as much data as necessary to demonstrate what you want and to demonstrate any problems you are having.

( here are some notes on attaching a file at excelfox
https://excelfox.com/forum/showthrea...ll=1#post11279
https://excelfox.com/forum/showthrea...ll=1#post15589 )

Alan

**barbosajulio77** · 08-17-2021, 11:39 PM

Files attached.

I had to save apvtrn.csv as .xls format will not let me attach csv file.

thank you,

**DocAElstein** · 08-17-2021, 11:59 PM

Thanks for the files.
Please could you try to upload the csv file again as a csv file. - I have changed the system so that you should now be able to upload csv files
Thanks
Alan

**barbosajulio77** · 08-18-2021, 03:11 AM

Done.

attached in csv format.

thank you,

**DocAElstein** · 08-18-2021, 04:56 PM

Hi
You have made it more difficult for me because you have not explained very well what you want nor given any expected results. I will take a guess you either don’t know or have difficulty with the English language.
Probably a bit of both…
No matter. I had a try at guessing what you want
https://excelfox.com/forum/showthrea...ll=1#post15604

Assuming that I have guessed correctly, then the solution is quite easy. .. There must be a thousand ways to do it, ( and the whole thing can be done better anyway ).. But as I assume you have little idea of anything that’s going on, I will try to keep the modifications as minimum as possible to avoid confusing you further…

So…. The solution attempt
I have tried already to explain that you are basing a lot of the working on resizing and offsetting from the top left of your destination range. So you might as well Set your destination range to wkbDst.Worksheets("STATEMENT").Range("A17")

As I tried to explain in my previous posts, doing a change like that will stop you getting the error.

The remaining problem is that your column offset variable, c, goes from 0-3 ( it does 0 1 2 3 ). But you want to put the last column of data in an extra column to the right.
You want c to do like 0 1 2 _4

A very simple cure for this would be to add a check and adjustment thus:
__ If c = 3 Then Let c = 4

So there are two simple changes
_ change Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17:C17, E17") to Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17")

_ add a code line of __ If c = 3 Then Let c = 4 just after the existing code line of c = c + 1

Alan