Excel VBA For Each Loop giving out of range error

Question

I have 2 workbooks open, and I am trying to copy one range of cells from one workbook into the other workbook based on a condition. The program keeps on breaking at the first For Each loop with the

Subscript out of range

error and I am lost as to why.

I looked at other threads here, and they said that the error comes from not having an Open workbook. I implemented that, and it still gives me this error.

I am new to VBA. Any ideas?

Sub TransferCells()
    Dim aggrange As Range
    Dim AnalyticalCell As Range
    Dim BatchCell As Range
    Dim analyticalwb, batchwb As Excel.Workbook
    Dim SEHPLC, CultureDay As Worksheet

    Set analyticalwb = Workbooks.Open("\\ntucsmafps06.na.jn.com\Hom$\APachall\Ta Big Data\Cas tical Results (4).xlsm")
    Set batchwb = Workbooks.Open("\\nctusmafp0s6.na.jn.com\Hom$\APachall\Ta Big Data\20180420_Fed Batch All Data_0.xlsx")

    For Each AnalyticalCell In analyticalwb.Worksheets("SE-HPLC").Range("A1:A87")
        For Each BatchCell In batchwb.Worksheets("Sheet3").Range("A2:A125271")
            If AnalyticalCell.Value = BatchCell.Value Then
                Set aggrange = Range(ActiveCell.Offset(0, 11), ActiveCell.Offset(0, 13))
                aggrange.Copy (Destination = Application.Workbooks("20180420_Fed Batch All Data_0.xlsx").Worksheets("Sheet3").Range(ActiveCell.Offset(0, 3), ActiveCell.Offset(0, 5)))
            End If
        Next BatchCell
    Next AnalyticalCell
End Sub

When declaring typed variables, each variable must have the As type information. in Dim analyticalwb, batchwb As Excel.Workbook, only batchwb is a workbook, analyticalwb is of the default type (variant). Same for Dim SEHPLC, CultureDay As Worksheet — Vincent G
– Vincent G, Commented Jul 27, 2018 at 13:07
Ok thank you! I fixed that, but it is still giving me the Subscript out of range error at the "For Each AnalyticalCell" line. — A.P
– A.P, Commented Jul 27, 2018 at 13:12
What is ActiveCell? It is a good idea to specify whose sheet Range your are talking about. — Vincent G
– Vincent G, Commented Jul 27, 2018 at 13:13
Did I not do that? I thought I said "For Each AnalyticalCell in analyticalwb.Worksheets.Range". Does that not specify the sheet and the range over which I am iterating? — A.P
– A.P, Commented Jul 27, 2018 at 13:17
AnalyticalCell is defined by your loop, but ActiveCell won't change except if you select it. see ActiveCell — Vincent G
– Vincent G, Commented Jul 27, 2018 at 13:23

Vityata · Accepted Answer · 2018-07-27 13:52:14Z

2

Change the problematic code to the following. There are 2 errors there:

With Worksheets(ActiveCell.Parent.Name)
    aggrange.Copy Destination:=Application.Workbooks("20180420_Fed Batch All Data_0.xlsx").Worksheets("Sheet3").Range(.Cells(ActiveCell.Offset(0, 3)), .Cells(ActiveCell.Offset(0, 5)))
End With

Destination is a named parameter, thus it should be passed with := and not with =;

To pass a range, based on two cells, you need to pass:

Range(.Cells(ActiveCell.Offset(0, 3)), .Cells(ActiveCell.Offset(0, 5))) and not Range(). Range() takes string as arguments.

Further ideas - the Dim should be done per variable. In other languages (C++, etc) it is ok, in vba it is a bit problematic:

Dim analyticalwb As Excel.Workbook, batchwb As Excel.Workbook
Dim SEHPLC As Worksheet, CultureDay As Worksheet

How to avoid using Select in Excel VBA

Write Option Explicit on the top of the Module and see whether it compiles.

edited Jul 27, 2018 at 13:52

answered Jul 27, 2018 at 13:08

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

BigBen Over a year ago

Hmm - I'm not sure on number 2. Cells(Range1, Range2) wouldn't work, since it has RowIndex and ColumnIndex arguments. . Range(Range1, Range2) would, right?

Vityata Over a year ago

@BigBen - true. Did not test it, because of the names of the worksheet, will check it again.

A.P Over a year ago

It does not compile.

BigBen Over a year ago

"It looks like Excel cannot find my workbook" - how, if there's no error on the Set analyticalwb line?

BigBen Over a year ago

Agreed - but Workbooks.Open returns an object that represents the opened Workbook.

|

Collectives™ on Stack Overflow

Excel VBA For Each Loop giving out of range error

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related