Using an array to manipulate and standardize data

[Waimea]

Hi, I am working with tables in Excel and I am trying to automatically load the multicolumn table into an array,

I then want to calculate the AVERAGE for each table list column, the STDEV.P for each table list column and finally STANDARDIZE the data using the AVERAGE and STDEV.P I calculated earlier.

I think I understand how to load the table into an array but now how to actually loop through the data and manipulate it?

How can I loop through all list columns and calculate the AVERAGE for that list column, calculate the STDEV.P for that list column and finally calculate the STANARDIZE score?

Sub ArrayToStandardize()


Dim TableToArray As ListObject
Dim NewArray As Variant
Dim x As Long
Dim R As Long


ReDim NewArray(1 To 3, 1 To 100)


Set TableToArray = Sheets("Test1").ListObjects("Table1") 'Set path for Table variable
NewArray = TableToArray.DataBodyRange 'Create Array List from Table
For x = LBound(NewArray) To UBound(NewArray)
    
    NewArray(x, 1) = WorksheetFunction.Average(x, 1)
           
    'NewArray(x, 2) = WorksheetFunction.Average(x, 2)
    
    Debug.Print NewArray(x, 2)
    'Debug.Print NewArray(x, 2)
    'Debug.Print NewArray(x, 3) 
Next x
        
R = Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Test1").Cells(2, 5).Resize(UBound(NewArray, 1), UBound(NewArray, 2)) = NewArray
  
End Sub

 

[steve the fish]

Heres an example of how to loop through the columns:

For i = 1 To UBound(NewArray, 2)
    ave = Application.Average(Application.Index(NewArray, 0, i))
Next

 

[Waimea]

Hi steve the fish,

this is exactly what I was looking for, a good example on how to loop through columns!

Could you give me an example on how to write the array back to the worksheet?

 

[Waimea]

I am still trying to manipulate an Excel table read into an array.

Can anyone give me some more examples of how to manipulate the array?

 

[steve the fish]

Firstly what do you mean by write it back to the worksheet? Are you manipulating the values first? Otherwise nothing has changed and its still on the worksheet.

 

[Waimea]

Hi steve the fish, thank you for your reply. I am reading about arrays on http://www.snb-vba.eu/VBA_Arrays_en.html and at the moment I am only using your code to calculate the average of two columns in the immediate window.

I am using an array in my workbook to hold information and then I use the following code to write the array back to the worksheet. This code is part of two For loops.

Dim R As Long
Dim wkarr As Variant
ReDim wkarr(1 To 40 * 40, 1 To 92)
wkarr(i, 1) = sht.Range("AC39").Value
i = i + 1
R = Cells(Rows.Count, 1).End(xlUp).Row + 1

.Cells(R, 1).Resize(UBound(wkarr, 1), UBound(wkarr, 2)) = wkarr

I am trying to calculate the average of column1, calculate the standard deviation of column1. Will this require additional loops or can I do both in one loop as you showed in your example?

Can I have a select case for each column? Stored in another array with column names?

 

[steve the fish]

Theres all sorts you could do. You could loop through the columns based on their headers stored in an array yes. Once you have that column you can use it more than once yes. Im not seeing where you are using the average and sd. Where is it being placed?

 

[Waimea]

Hi, thank you for your reply. I can't get the standard deviation calculation to work because I don't understand right now how loops work with arrays.

Sub ArrayToStandardize()


Dim NewArray As Variant
Dim R As Long
Dim std As Variant
Dim ave As Variant
Dim i As Long


ReDim NewArray(1 To 3, 1 To 100)


    For i = 1 To UBound(NewArray, 2)
       ave = Application.Average(Application.Index(NewArray, 0, i))
       std = Application.WorksheetFunction.StDev_S(Application.Index(NewArray, 0, i)) ' 1004 error
       Debug.Print ave
       Debug.Print std
    Next i
    
R = Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Test1").Cells(1, 5).Resize(UBound(NewArray, 1), UBound(NewArray, 2)) = NewArray
  
End Sub

 

[Waimea]

I would like to create an array with column names and then have a if or select case statement for each column.

I can create an array with column names using this code:

myarray = Array("Name", "Age", "Length")

I would then want to create a select case statement for

Select case myarray

     case is = "Name"
     ' do nothing on text columns

     case is ="Age"
     calculate average and standard deviation

     case is ="Length"
     calculate average 

End select

 

[steve the fish]

Wouldnt it be STDEV.S