How can I write array to incremental columns (.cells not clear to me)

[GeeWhiz7]

Hi,
Below is a nested loop that 1) loops through a list of worksheet names, 2) gets the last row of data from each sheet and 3) is supposed to aggregate each row of data into a DashBoard sheet by transposing it.
I am trying to get each subsequent bit of data to get pasted into the next column, starting at C4 going to D4, E4, etc. I'm not exactly clear why I get a "Range of Object _Worksheet" failed error.

Option Explicit

Sub Dash1()
    Dim ws, Dash As Worksheet
    Dim wsNames As Variant, wsName, nUms, dAtes As Variant
    Dim lr As Long, lc As Long, i As Long, j As Long
   
    wsNames = ThisWorkbook.Worksheets("Comps Sheet Names").Range("C1").CurrentRegion.Value
    Set Dash = Worksheets("DashBoard")
    Dash.Cells.Clear
   
    '****Starting location to paste array values eq. to "C4"
   
    i = 4 'row
    j = 3 'col
   
    For Each wsName In wsNames
        Set ws = ThisWorkbook.Worksheets(wsName)
       
        With ws
            lr = .Cells(Rows.Count, 1).End(xlUp).Row
            lc = .Cells(1, Columns.Count).End(xlToLeft).Column
            nUms = .Range(.Cells(lr, 3), .Cells(lr, lc)).Value
            dAtes = .Range(.Cells(lr, 1), .Cells(lr, lc)).Value

            With Dash  '#####Write nUms array(s) to DashBoard
                '######This doesn't work######
                .Range(i,j).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

                '######This doesn't work either######
                .Range(.Cells(i, j)).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

                '######But if I change to this, it will write correctly to C4, but I can't increment to the next column (E4) it######
                .Range("C" & i).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

            End With
        End With
        i = i + 1
        j = j + 1
    Next wsName

End Sub

 

[igold]

Untested, but none of these should throw an error...

Sub Dash1()
    Dim ws, Dash As Worksheet
    Dim wsNames As Variant, wsName, nUms, dAtes As Variant
    Dim lr As Long, lc As Long, i As Long, j As Long
   
    wsNames = ThisWorkbook.Worksheets("Comps Sheet Names").Range("C1").CurrentRegion.Value
    Set Dash = Worksheets("DashBoard")
    Dash.Cells.Clear
   
    '****Starting location to paste array values eq. to "C4"
   
    i = 4 'row
    j = 3 'col
   
    For Each wsName In wsNames
        Set ws = ThisWorkbook.Worksheets(wsName)
       
        With ws
            lr = .Cells(Rows.Count, 1).End(xlUp).Row
            lc = .Cells(1, Columns.Count).End(xlToLeft).Column
            nUms = .Range(.Cells(lr, 3), .Cells(lr, lc)).Value
            dAtes = .Range(.Cells(lr, 1), .Cells(lr, lc)).Value

            With Dash  '#####Write nUms array(s) to DashBoard
                '######This doesn't work######
                .Cells(i, j).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

                '######This doesn't work either######
                .Cells(i, j).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

                '######But if I change to this, it will write correctly to C4, but I can't increment to the next column (E4) it######
                .Range("C" & i).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

            End With
        End With
        i = i + 1
        j = j + 1
    Next wsName

End Sub

 

[rlv01]

Run this

Debug.Print .Range(I, J).Address

And you'll get the same error. Using type long as arguments for the range property is not legal. You are conflating it with the way the cell property works.

 

[GeeWhiz7]

Untested, but none of these should throw an error...

Sub Dash1()
    Dim ws, Dash As Worksheet
    Dim wsNames As Variant, wsName, nUms, dAtes As Variant
    Dim lr As Long, lc As Long, i As Long, j As Long
  
    wsNames = ThisWorkbook.Worksheets("Comps Sheet Names").Range("C1").CurrentRegion.Value
    Set Dash = Worksheets("DashBoard")
    Dash.Cells.Clear
  
    '****Starting location to paste array values eq. to "C4"
  
    i = 4 'row
    j = 3 'col
  
    For Each wsName In wsNames
        Set ws = ThisWorkbook.Worksheets(wsName)
      
        With ws
            lr = .Cells(Rows.Count, 1).End(xlUp).Row
            lc = .Cells(1, Columns.Count).End(xlToLeft).Column
            nUms = .Range(.Cells(lr, 3), .Cells(lr, lc)).Value
            dAtes = .Range(.Cells(lr, 1), .Cells(lr, lc)).Value

            With Dash  '#####Write nUms array(s) to DashBoard
                '######This doesn't work######
                .Cells(i, j).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

                '######This doesn't work either######
                .Cells(i, j).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

                '######But if I change to this, it will write correctly to C4, but I can't increment to the next column (E4) it######
                .Range("C" & i).Resize(UBound(nUms, 2), UBound(nUms, 1)).Value = Application.WorksheetFunction.Transpose(nUms)

            End With
        End With
        i = i + 1
        j = j + 1
    Next wsName

End Sub

Now its real obvious I don't know my .Range/.Cells/.Cell vernacular and uses very much.
But, this works, thank you igold!

 

[igold]

You're welcome. Thanks for the feedback.