1

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
A.P
  • 23
  • 4
  • 1
    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 Jul 27 '18 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 Jul 27 '18 at 13:12
  • What is ActiveCell? It is a good idea to specify whose sheet Range your are talking about. – Vincent G Jul 27 '18 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 Jul 27 '18 at 13:17
  • AnalyticalCell is defined by your loop, but ActiveCell won't change except if you select it. see [ActiveCell](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-activecell-property-excel) – Vincent G Jul 27 '18 at 13:23
  • And Range() without an object qualifier points to the activesheet. – Vincent G Jul 27 '18 at 13:24
  • Ok, where should I use this ActiveCell property? Should I get rid of my loop variables and use this ActiveCell instead? I thought about using ActiveCell, but I am trying to compare two cells in two different workbooks to each other, so how would the program compare two Active Cells in two workbooks? – A.P Jul 27 '18 at 13:30
  • Don't use ActiveCell at all, use AnalyticalCell and BatchCell. – Vincent G Jul 27 '18 at 13:38
  • Ok, so I changed all the references to ActiveCell in my code to either AnalyticalCell or BatchCell. The program is still unable to move from the initial For Each Loop. Am I misreading something here? – A.P Jul 27 '18 at 13:43
  • If you add the line `Set SEHPLC = analyticalwb.Worksheets("SE-HPLC")` after `Set batchwb...`, does the error move to the new line? – BigBen Jul 27 '18 at 14:06
  • No, it now says that the subscript out of range error is now in the Set SEHPLC line. – A.P Jul 27 '18 at 14:09
  • Do you have a trailing space on the end of the worksheet name maybe, like "SE-HPLC "? – BigBen Jul 27 '18 at 14:10
  • No, it doesn't look like I do. I feel like it has something to do with the way I access my workbooks, but I already opened them. It seems like Excel doesn't know where they are, even though I included the correct path. – A.P Jul 27 '18 at 14:14
  • If there's no error on the `Set analyticalwb` line, then the sheet name "SE-HPLC" must be causing the error. You don't by chance have `On Error Resume Next` in your code, do you? Navigate to the "SE-HPLC" tab, then in the Immediate window, put `? ActiveSheet.Name` and `? Len(ActiveSheet.Name)`. Are the results "SE-HPLC" and 7? – BigBen Jul 27 '18 at 14:18
  • @BigBen, the results are SE-HPLC and 7. – A.P Jul 30 '18 at 12:36
  • Hmm - to test your hypothesis that the workbook is causing the issue, does `analyticalwb.Activate` throw an error? If not, does `analyticalwb.Worksheets("SE-HPLC").Activate` throw an error? – BigBen Jul 30 '18 at 16:29
  • I have figured this part out, thanks guys! It turns out that I had to close the workbooks before I ran the macro. If the workbooks are already open, it can't "open" them up again so it gives an out-of-range error. – A.P Aug 08 '18 at 13:05

1 Answers1

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
  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.
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    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? – BigBen Jul 27 '18 at 13:16
  • @BigBen - true. Did not test it, because of the names of the worksheet, will check it again. – Vityata Jul 27 '18 at 13:16
  • I changed my code to your suggestion, but the program is still stopping at the initial for loop entrance, so I am unable to see if the suggestion worked. – A.P Jul 27 '18 at 13:49
  • @A.P - Can you write `Option Explicit` on the top of the module and see whether it compiles? https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/option-explicit-statement – Vityata Jul 27 '18 at 13:51
  • 1
    It does not compile. – A.P Jul 27 '18 at 13:53
  • @A.P - that's really good! It means that if you make sure it compiles, you would probably resolve the error. The VBEditor would help you. – Vityata Jul 27 '18 at 13:54
  • Hmm ok...I still do not understand the error though. I have defined Analytical Cell as a Range, and I am telling the program to loop through the specified range in the workbook. Why is it telling me that there is a subscript out of range when everything is defined? – A.P Jul 27 '18 at 14:04
  • @A.P - Idk. As a first step, try to delete all the code in the loop, and leave only the `For ... Each and Next` and see whether you can compile. E.g. this -> https://gist.github.com/Vitosh/5697ec1682ebafe936ef178453c16302 – Vityata Jul 27 '18 at 14:05
  • Ok, I will try. Also, does it look like I am accessing my workbooks properly, i.e syntax, variables? Or am I missing an Activate command? It looks like Excel cannot find my workbook, even though I explicitly told it the path. – A.P Jul 27 '18 at 14:17
  • 1
    "It looks like Excel cannot find my workbook" - how, if there's no error on the `Set analyticalwb` line? – BigBen Jul 27 '18 at 14:20
  • @BigBen - `analyticalwb` is a `Variant` in the OP's code. But there should be an error, yup. – Vityata Jul 27 '18 at 14:22
  • 1
    Agreed - but `Workbooks.Open` returns an object that represents the opened `Workbook`. – BigBen Jul 27 '18 at 14:23
  • I also had another question about point #2 made above: that part of the code does not work, even after I have changed it to Vityata's suggestion using Range(.Cells(...))). The complier gives me the "Invalid or Unqualified reference" error...Any other ideas? – A.P Aug 08 '18 at 13:09