0

I am trying to select a range of cells offset from my active cell and I am not having much luck. The range of cells that I am trying to select is between and including the 11th cell and the 13th cell in the same row as the active cell. I receive the error below:

Run-time error '1004':
Application-defined or object-defined error.

I am still a little new to VBA. If you see any other glaring problems in my code, please feel free to correct me! The error occurs at the lines with the asterixes.

Sub TransferCells()
Dim aggrange As Range
Dim AnalyticalCell As Range
Dim BatchCell As Range
Dim analyticalwb As Excel.Workbook
Dim batchwb As Excel.Workbook
Dim SEHPLC As Worksheet
Dim CultureDay As Worksheet
Set batchwb = Workbooks.Open("\\cntusmafpsjnj.com\HomeH$\Tala Big Data\20180420_Fed Batch All Data_0.xlsx")
Set CultureDay = batchwb.Worksheets("Culture Day")
Set analyticalwb = Workbooks.Open("\\cntusmafps.com\HomeH$\Tala Big Data\Castle Analytical Results (4).xlsm")
Set SEHPLC = analyticalwb.Worksheets("SE-HPLC")
MsgBox ActiveSheet.Name

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

End Sub
A.P
  • 23
  • 4
  • You should really [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/) - but, try this (less parenthesis, and removing those single quote marks: `Set aggrange = Range(Activecell.Offset(0,11),Activecell.Offset(0,13))`? Edit: **Note** You need to set what worksheet that range is on too. You have a bunch of worksheets, so be sure to qualify that range, ie `Set aggrante = batchwb.Worksheets("Culture Day").Range(ActiveCell.Offset(0,11), ...)` – BruceWayne Aug 08 '18 at 14:23
  • Qualifying the range did the trick, thank you! Now the next line just below it gives the "subscript out of range error"...any suggestions with that? Edit: The next line should copy the range from above and paste it into another range in another worksheet, if that was not clear! – A.P Aug 08 '18 at 14:32

0 Answers0