Using Cell and Column Number references inside Range

[lkrznchc]

I am wondering if this syntax is ok or what I can do to change it.
I am getting a Run-time error 1004: Application-defined or object-defined error with this line when I run my code:

TestInfo_CopyArray1(i, 1) = ThisWorkbook.Worksheets("Summary").Range(Cells(13 + i, 4))

I have definined my Array as a Range and believe that the error is due to the syntax of:

.Range(Cells(13 + i, 4))

In past I have changed the code to be:

.Range(Cells(13 + i, 4),Cells(13 + i, 4))

But this seems rather repetitive and unnecessary. What is the recommended method of using row and column numbers to index a particular cell?

This is the more full code section:

            For i = 1 To 5
                TestInfo_CopyArray1(i, 1) = ThisWorkbook.Worksheets("Summary").Range(Cells(13 + i, 4))
                TestInfo_CopyArray2(i, 1) = ThisWorkbook.Worksheets("Summary").Range(Cells(13 + i, 5 + num_TestRuns))
            Next i

 

[Jonmo1]

When using Range(Cells) syntax, and you specify the Book/Sheet on the Range, you need to also specify the same book/sheet on the Cells

ThisWorkbook.Worksheets("Summary").Range(Cells(13 + i, 4))

Should be
ThisWorkbook.Worksheets("Summary").Range(ThisWorkbook.Worksheets("Summary").Cells(13 + i, 4))

You may have intermittent issues where sometimes it works without that and other times it doesn't.
If the code is in a standard module, and the sheet Summary just happens to be the active sheet, then it works.
But if any other sheet is currently active, then it wont work without the book/sheet reference on the Cells.

 

[Jonmo1]

Further, in your given example.
why not just eliminate the range alltogether and just use Cells by itself?

ThisWorkbook.Worksheets("Summary").Cells(13 + i, 4)

Presumeably because you're going to do
Range(Cells(beginrow,begincol), Cells(endrow,endcol))

In which case the book/sheet reference needs to be included on All 3 parts (range and both cells)
The With Structure can be very helpfull for that

With ThisWorkbook.Worksheets("Summary")
    .Range(.Cells(beginrow,begincol), .Cells(endrow,endcol))
End With

 

[lkrznchc]

I removed the .range( ) portion of the code since I do only want to reference a single cell as an object. However, now I am getting a Run-time error 91: Object variable or With block variable not set.

What I am trying to do is reference a range of cells on my worksheet throughout my code, but they need to be treated as an object not simply the value. Am I allowed to set the array as a range?

Here is the complete procedure. Perhaps you have a better method to do the operation than an autofill the formulas accross the desired variable range, while maintaining cell references in formula to change as if they were autofilled?

Sub TestSummarySetup()
    Dim num_TestRuns As Integer
    Dim num_xmitters As Integer
    Dim TestInfo_CopyArray1(1 To 5, 1) As Range
    Dim TestInfo_CopyArray2(1 To 5, 1) As Range
    Dim TestInfo_PasteArray1() As Range
    Dim TestInfo_PasteArray2() As Range
    Dim TestPointRowInfo_CopyArray() As Range
    Dim TestPointRowInfo_PasteArray() As Range
    Dim TestPointRowData_CopyArray() As Range
    Dim TestPointRowData_PasteArray() As Range
    Dim i, j As Integer
    
    'Checks Number of Test Runs and Transmitters to set up for
        num_TestRuns = Application.WorksheetFunction.countA(ThisWorkbook.Worksheets("Summary").Range("B5:B11"))
        num_xmitters = ThisWorkbook.Worksheets("Instr List").UsedRange.Rows.Count
    'Redefines Array Dimensions
        ReDim TestInfo_PasteArray1(1 To 5, 1 To num_TestRuns)
        ReDim TestInfo_PasteArray2(1 To 5, 1 To num_TestRuns)
        ReDim TestPointRowInfo_CopyArray(1, 1 To 4)
        ReDim TestPointRowInfo_PasteArray(1 To num_xmitters, 1 To 4)
        ReDim TestPointRowData_CopyArray(1, 1 To 1 + num_TestRuns * 2)
        ReDim TestPointRowData_PasteArray(1 To num_xmitters, 1 To 1 + num_TestRuns * 2)
    'Clears Previous Values
        ThisWorkbook.Worksheets("Summary").Range("E14:Z18").ClearContents
        ThisWorkbook.Worksheets("Summary").Range("A19:Z250").ClearContents
    '***Define Cell Ranges to Array
        'Test Information Header
            For i = 1 To 5
                TestInfo_CopyArray1(i, 1) = ThisWorkbook.Worksheets("Summary").Cells(13 + i, 4)
                TestInfo_CopyArray2(i, 1) = ThisWorkbook.Worksheets("Summary").Range(Cells(13 + i, 5 + num_TestRuns))
            Next i
        'Test Point Info Rows
            For i = 1 To 4
                TestPointRowInfo_CopyArray(1, i) = ThisWorkbook.Worksheets("Summary").Range(Cells(18, i))
            Next i
    
    'Defines Equations to Arrays
        'Averaging Cell Block Equations
            TestInfo_CopyArray1(1, 1).Formula = "=OFFSET($A$5,COLUMNS($A$5:A$5)-1,0)"
            TestInfo_CopyArray1(2, 1).Formula = "=SUM(OFFSET($C5,COLUMNS($B$5:B$5)-1,0),OFFSET($D5,COLUMNS($B$5:B$5)-1,0))"
            TestInfo_CopyArray1(3, 1).Formula = "=SUM(OFFSET($C5,COLUMNS($B$5:B$5)-1,0),OFFSET($E5,COLUMNS($B$5:B$5)-1,0))"
            TestInfo_CopyArray1(4, 1).Value = "Average"
            TestInfo_CopyArray1(5, 1).Formula = "=AVERAGE(INDEX(Data_Log,VLOOKUP(E$14,$A$5:$G$11,6),HLOOKUP($C18,Data_Log,2)):INDEX(Data_Log,VLOOKUP(E$14,$A$5:$G$11,7),HLOOKUP($C18,Data_Log,2)))"
        'Standard Deviation % Cell Block Equations
            For i = 1 To 5
                If i < 4 Then
                    TestInfo_CopyArray2(i, 1).Formula = TestInfo_CopyArray1(i, 1).Formula
                ElseIf i = 4 Then
                    TestInfo_CopyArray2(i, 1).Value = "Std Dev %"
                ElseIf i = 5 Then
                    TestInfo_CopyArray2(i, 1).Formula = "=STDEVP(INDEX(Data_Log,VLOOKUP(E$14,$A$5:$G$11,6),HLOOKUP($C18,Data_Log,2)):INDEX(Data_Log,VLOOKUP(E$14,$A$5:$G$11,7),HLOOKUP($C18,Data_Log,2)))"
                End If
            Next i
        'Test Point Row Info Equations
            TestPointRowInfo_CopyArray(1, 1).Formula = "=CLEAN(VLOOKUP(C18,'Instr List'!$B$3:$D$39,3,FALSE))"
            TestPointRowInfo_CopyArray(1, 3).Formula = "=OFFSET('Data Log'!$C$11,0,ROWS(C$17:C17)-1)"
            TestPointRowInfo_CopyArray(1, 4).Formula = "=IF(LEFT(C18,1)=""P"",""PSIA"",IF(LEFT(C18,1)=""T"",""F"",IF(LEFT(C18,1)=""W"",""inWC"",""---"")))"
        'Test Point Row Data Equations
            For i = 1 To 1 + num_TestRuns * 2
                If i < num_TestRuns + 1 Then
                    TestPointRowData_CopyArray(1, i) = TestInfo_PasteArray1(5, i)
                ElseIf i >= num_TestRuns + 1 Then
                    TestPointRowData_CopyArray(1, i) = TestInfo_PasteArray2(5, i)
                End If
            Next i
    'Copies Array Formulas to Paste Arrays
        'Test Information
            For i = 1 To 5
                For j = 1 To num_TestRuns
                    TestInfo_CopyArray1(i, 1).AutoFill Destination:=TestInfo_PasteArray1(i, j), Type:=xlFillDefault
                    TestInfo_CopyArray2(i, 1).AutoFill Destination:=TestInfo_PasteArray2(i, j), Type:=xlFillDefault
                Next j
            Next i
        'Test Point Rows
            For i = 1 To num_xmitters
                For j = 1 To 1 + num_TestRuns * 2
                    TestPointRowData_CopyArray(1, j).AutoFill Destination:=TestPointRowData_PasteArray(j, i), Type:=xlFillDefault
                Next j
            Next i
    'Assigns Arrays to Cell References
        'Test Information Header
            For i = 1 To 5
                For j = 1 To num_TestRuns
                    ThisWorkbook.Worksheets("Summary").Range(Cells(13 + i, 4 + j)) = TestInfo_PasteArray1(i, j)
                    ThisWorkbook.Worksheets("Summary").Range(Cells(13 + i, 5 + num_TestRuns + j)) = TestInfo_PasteArray2(i, j)
                Next j
            Next i
        'Test Point Data Rows
            For i = 1 To num_TestRuns * 2
                For j = 1 To num_xmitters
                    ThisWorkbook.Worksheets("Summary").Range(Cells(17 + j, i)) = TestPointRowInfo_PasteArray(j, i)
                    ThisWorkbook.Worksheets("Summary").Range(Cells(17 + j, 4 + i)) = TestPointRowData_PasteArray(j, i)
                Next j
            Next i
   
End Sub