Transferring range to dynamic array gives Runtime Error 1004

Question

Attempting to copy a range from one workbook to another - there will be other tasks performed on the data set in the process but this is the gist of what I want for now. The range in the source workbook is not static; meaning each time a report is generated, the necessary information will appear in the same columns but not the same rows. There is one blank column in the middle of the range and several columns I will be ignoring - along with some calculations/converting of data. To try and help with the somewhat unpredictable nature of the location of the data in the source workbook, I've named my range as a variable in the sub - I'm not sure if that is the problem. Everything seems to be working out, except when I go to put the range in the array. Here's what I've got:

    Sub GetElemArray()

        Dim fndrng As Range
        Dim rEleAn As Range
        Dim frst As Range

        'the rest of the variables below are global

        Workbooks(SheetName).Activate
        Worksheets("Report").Activate
        Columns(2).Select

        Set fndrng = Selection.Find("Analyte")
        Set frst = fndrng.Offset(1, 0)
        Set rEleAn = Range(frst, fndrng.End(xlDown).End(xlToRight))

        EleList = rEleAn.Rows.Count
        ColList = rEleAn.Columns.Count
        ReDim ElAr(0 To EleList, 0 To ColList)

        For EleList = LBound(ElAR, 1) To UBound(ElAr, 1)
            For ColList = LBound(ElAr, 2) To UBound(ElAr, 2)

                'The following line returns the Runtime 1004 error
                ElAr(EleList, ColList) = Range(frst).Offset(EleList, _ 
                  ColList).Value
            Next ColList
        Next EleList

    Code to Do Other Stuff
    End Sub

Looking through the Locals Window, I can see that rEleAn stores its values as .Value2. I tried changing the Range to this property and it still returned the same error. Looking in the Locals Window, everything is assigned properly near as I can tell.

Any ideas as to what is happening?

You activate one worksheet then immediately activate another. Please read and heed [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — , May 23 '18 at 22:57
Is this the same you're trying todo: ElAr = rEleAn ? and you're Redim makes your array 1 row and column to big — EvR, May 23 '18 at 23:02
@Jeeped Pretty sure I'm activating a Workbooks(SheetName) - the SheetName variable is from a form - and then Worksheets("Report"). But I'll look into avoiding Select, thanks. — webfootedoldman, May 24 '18 at 05:01
@EvR Yes that's what I'm going for. I'll give EleList and ColList a minus one on the count in the morning when I get to work and see if that solves it. Thanks. — webfootedoldman, May 24 '18 at 05:02
@Jeeped, changed SheetName variable to FlNm to avoid confusion. GOing with EvR's suggestion to avoid select. Thanks! — webfootedoldman, May 24 '18 at 18:43

EvR · Accepted Answer · 2018-05-25T07:32:51.943

0

If I understand your comment correctly, try:

Sub GetElemArray()

            Dim fndrng As Range
            Dim rEleAn As Range
            Dim frst As Range

            'the rest of the variables below are global
            Set fndrng = Worksheets("Report").Columns(2).Find("Analyte")
            Set frst = fndrng.Offset(1, 0)
            Set rEleAn = Range(frst, fndrng.End(xlDown).End(xlToRight))
            ElAr = rEleAn

        'Code to Do Other Stuff
    End Sub

edited May 25 '18 at 07:32

answered May 24 '18 at 07:41

EvR

3,418
2
13
23

I had tried something similar to that earlier, though it was a bit more long winded. I tried almost exactly what you wrote - I had to change `Sheet1` to `Report` since I have no control over the name of the worksheet that I am getting data from. Unfortunately this still doesn't capture the data for the array. I have no variables in my local window. I added `EleList = rEleAn.Rows.Count - 1` and `ColList = rEleAn.Columns.Count - 1` per your earlier statement that the size of the range was incorrect. This did not work. I also kept the -1 to the count and changed `Value` to `Value2`. No go. – webfootedoldman May 24 '18 at 16:39
Nvm, I've solved it. `ElAr(EleLst, ColLst) = Range(frst).Offset(EleLst, ColLst).Value` needed to be `ElAr(EleLst, ColLst = frst.Offset(EleLst, ColLst).Value`. The Runtime Error 1004 was cropping up because the `Range(frst)` was looking for a named range in the worksheet, not as a VBA defined variable range. – webfootedoldman May 24 '18 at 18:26
And I just realized that yours works, too. @EvR. I was looking for variable information in the locals window instead of trying to print to a sheet. Marking as solved. Thanks for your help. – webfootedoldman May 24 '18 at 18:42

Transferring range to dynamic array gives Runtime Error 1004

1 Answers1