vbscript loop cells into array

[edTech]

I am reading an excel sheet and I would like to store the value into an array, but It is not working. Any help would be most appreciated.

With objReadWS

lastRow2 = .Range("G" & .Rows.Count).End(xlUp).Row
lastCol2 = .Cells(2, .Columns.Count).End(xlToLeft).Column

Redim Array100(i)

For each x in .Range(.Cells(lastRow2, 18), .Cells(lastRow2, lastCol2))

Array100(i) = x.Value
i = i + 1


Next
End With

For i = LBound(Array100) To UBound(Array100)

msgbox Array100(i)

next

 

[DanteAmor]

I guess you are going to load a range of cells, in this example it goes from G2 to the last row of column G and the last column of row 2:

Array100 = .Range("G2", .Cells(lastRow2, lastCol2)).Value

Try this:

Sub test()
  Dim objReadWS As Worksheet, Array100 As Variant, lastRow2 As Long, lastCol2 As Long
  Dim i As Long
  Set objReadWS = Sheets("Sheet1")
  With objReadWS
    lastRow2 = .Range("G" & Rows.Count).End(xlUp).Row
    lastCol2 = .Cells(2, Columns.Count).End(xlToLeft).Column
    Array100 = .Range("G2", .Cells(lastRow2, lastCol2)).Value
  End With
 'Here you will only see the values of column G
  For i = 1 To UBound(Array100)
    MsgBox Array100(i, 1)
  Next
End Sub

You could explain what your ultimate goal is. Maybe I can help you.

 

[edTech]

Thank you DanteAmor,
Here is what I am trying to solve. I have data that comes in an excel sheet and I always have to read the last row with data up to 11 cells in a row, because every 11 cells is a new sequence of data. Then those cells will be plotted in a graph. The last row gets generated with new data every time it refreshes. So my assumption was to put every sequence in a an array to plot the data accordingly.

I hope this helps.



edTech

I guess you are going to load a range of cells, in this example it goes from G2 to the last row of column G and the last column of row 2:

Array100 = .Range("G2", .Cells(lastRow2, lastCol2)).Value

Try this:

Sub test()
  Dim objReadWS As Worksheet, Array100 As Variant, lastRow2 As Long, lastCol2 As Long
  Dim i As Long
  Set objReadWS = Sheets("Sheet1")
  With objReadWS
    lastRow2 = .Range("G" & Rows.Count).End(xlUp).Row
    lastCol2 = .Cells(2, Columns.Count).End(xlToLeft).Column
    Array100 = .Range("G2", .Cells(lastRow2, lastCol2)).Value
  End With
'Here you will only see the values of column G
  For i = 1 To UBound(Array100)
    MsgBox Array100(i, 1)
  Next
End Sub

You could explain what your ultimate goal is. Maybe I can help you.

 

[DanteAmor]

I still don't understand what you need.
Do you want to read the data in the last row with data from 11 in 11 cells?

 

[edTech]

I still don't understand what you need.
Do you want to read the data in the last row with data from 11 in 11 cells?

I want to read the data on the last row and every 11 cells I need to graph. So the first graph has data from cell say 1 -11 then the second graph has from 12 - 22 then the third from 13 - 23

 

[DanteAmor]

the third from 13 - 23

You mean 23 to 33?

 

[DanteAmor]

Try this

Sub loop_cells()
  Dim sh As Worksheet, i As Long, lr As Long, lc As Long, rng As Range
  Set sh = ActiveSheet
  With sh
    lr = .Range("G" & .Rows.Count).End(xlUp).Row
    lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
    For i = 1 To lc Step 11
      Set rng = Cells(lr, i).Resize(1, 11)
      'You Graph with the object "rng"
    Next
  End With
End Sub

 

[edTech]

Try this

Sub loop_cells()
  Dim sh As Worksheet, i As Long, lr As Long, lc As Long, rng As Range
  Set sh = ActiveSheet
  With sh
    lr = .Range("G" & .Rows.Count).End(xlUp).Row
    lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
    For i = 1 To lc Step 11
      Set rng = Cells(lr, i).Resize(1, 11)
      'You Graph with the object "rng"
    Next
  End With
End Sub

How do I print the rng so that I can see that its parsing correctly? I have tried msgbox rng and msgbox rng.value and I get an error on both

 

[DanteAmor]

In the rng you have several cells, so you cannot simply use rng.value.
To see the value of each cell within the rng you can do the following:

Sub loop_cells()
  Dim sh As Worksheet, i As Long, lr As Long, lc As Long, rng As Range, c As Range
  Set sh = ActiveSheet
  With sh
    lr = .Range("G" & .Rows.Count).End(xlUp).Row
    lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
    For i = 1 To lc Step 11
      Set rng = Cells(lr, i).Resize(1, 11)
      rng.Select
      'or
      MsgBox rng.Address
      'or
      For Each c In rng
        MsgBox c.Value
      Next
    Next
  End With
End Sub

 

[edTech]

In the rng you have several cells, so you cannot simply use rng.value.
To see the value of each cell within the rng you can do the following:

Sub loop_cells()
  Dim sh As Worksheet, i As Long, lr As Long, lc As Long, rng As Range, c As Range
  Set sh = ActiveSheet
  With sh
    lr = .Range("G" & .Rows.Count).End(xlUp).Row
    lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
    For i = 1 To lc Step 11
      Set rng = Cells(lr, i).Resize(1, 11)
      rng.Select
      'or
      MsgBox rng.Address
      'or
      For Each c In rng
        MsgBox c.Value
      Next
    Next
  End With
End Sub

Thank you