CODEWITHSUNDEEP
vbaexcelfor-loop
A.P
A.P

Reputation: 23

Excel VBA For Each Loop giving out of range error

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

Upvotes: 1

Views: 239

Answers (1)

Vityata
Vityata

Reputation: 43585

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
  1. Destination is a named parameter, thus it should be passed with := and not with =;
  1. 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.

  1. Further ideas - the Dim should be done per variable. In other languages (C++, etc) it is ok, in it is a bit problematic:
Dim analyticalwb As Excel.Workbook, batchwb As Excel.Workbook
Dim SEHPLC As Worksheet, CultureDay As Worksheet
  1. How to avoid using Select in Excel VBA
  1. Write Option Explicit on the top of the Module and see whether it compiles.

Upvotes: 2

Related Questions