How to load table into array, manipulate array and then write array into a new table?

[Waimea]

Hi,

I am trying to load a multiple column table into an array.

1) Load table into array
2) Manipulate several of the columns stored in the array with select case
3) Write back array to spreadsheet

I have 1 working but I am not sure how to do #2 and #3?

Sub MultiColumnTable_To_Array()

Dim myTable As ListObject
Dim myArray As Variant
Dim x As Long

Set myTable = ActiveSheet.ListObjects("Table1")

myArray = myTable.DataBodyRange

For x = LBound(myArray) To UBound(myArray)
Debug.Print myArray(x, 3)
Next x
  
End Sub

I am trying to use select case myArray(x,6) to manipulate the information in the array but maybe there is a better way?

Select case myArray(x,6) 
Case Is = 0
Debug.Print ="Off"

Case Is = 0.5
Debug.Print = "On"

End Select

Also, is it possible to loop through a table/databodyrange from bottom to top?

I want to do all the calculations on the information in the array and then write the array back to a new sheet.

 

[Fluff]

What exactly are you trying to do?

 

[Waimea]

Hi Fluff,

I want to categorize data based on a value. I am using a for loop at the moment but excel freezes when I run it so I want to try to minimize the amount of reads/writes to the spreadsheet by using an array.

Say myArray(x,6) = 0

I want myArray(x, 10) = "Off"

Or

myArray(x,6) = 0.35

I want myArray(x,10) = "Halv efficiency"

I have a couple of other calculations as well that I want to perform.

After I have categorized the values I want to write the array back to the spreadsheet, however I don't understand how to resize the array into a new table?

 

[Fluff]

How about

For x = LBound(myArray) To UBound(myArray)
   Select Case myArray(x, 6)
      Case 0: myArray(x, 10) = "Off"
      Case 0.35: myArray(x, 10) = "Halv efficiency"
   End Select
Next x

 

[DanteAmor]

Try this

Sub MultiColumnTable_To_Array()
  Dim myTable As ListObject
  Dim myArray As Variant, b As Variant
  Dim i As Long
  
  Set myTable = ActiveSheet.ListObjects("Table1")
  myTable.DataBodyRange.Select
  myArray = myTable.DataBodyRange
  ReDim b(1 To UBound(myArray), 1 To 1)
  For i = 1 To UBound(myArray)
    Select Case myArray(i, 6)
      Case 0:     b(i, 1) = "Off"
      Case 0.35:  b(i, 1) = "Halv efficienc"
      Case Else:  b(i, 1) = myArray(i, 10)
    End Select
  Next i
  ActiveSheet.ListObjects("Table1").ListColumns(10).DataBodyRange.Value = b
End Sub

 

[Waimea]

Hi Fluff and DanteAmor,

thank you both for your replies! This code works really fast and more importantly, it doesn't freeze my old computer.

Can I add more calculations with this code in some way?

 

[DanteAmor]

Can I add more calculations with this code in some way?

All the calculations you want.

 

[Waimea]

Hi DanteAmor,

that sounds great. Looking at your code I am asking if I need to add a new "b" to the code?

 ReDim c(1 To UBound(myArray), 1 To 1)

and

ActiveSheet.ListObjects("Test").ListColumns(16).DataBodyRange.Value = c

Or how should I go about making a new calculation?

 

[Fluff]

What exactly are you trying to do? Otherwise we are just guessing!

 

[Waimea]

Hi Fluff,

to be honest I am not quite sure what I am trying to do. I have a big table with events and where the values go from 0 to x, I am trying to divide the events into different cases based off it's value.

At the moment I am trying to identify big cases like "Off" or "On" and perhaps smaller cases like "On" & "Low efficiency".