How to use arrays to filter and find information

[waimea]

I am reading about arrays on http://www.snb-vba.eu/VBA_Arrays_en.html and about using multiple column tables to read table data into an array variable on https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables.

I like the structure of list objects and I want to learn more about the combination with list objects and arrays.

Is it possible to use arrays like I use advanced filter? I would like to read multiple multiple column tables into arrays and then access the arrays with an ActiveX Combobox, where the selection reads information from the array.

I understand how to read data into an array but not how to loop through an array and not how to find and/or extract information from it.

I have a table called cities with 320 rows and with 100 columns, I would like to read that table into an array and then find all entries where multiple criteria is used.

Ex. the population is above 10 000 people or the number of traffic lights is lower then 1 000 or where the number of sports arenas is less then 5.

How can I learn this? And is this possible?

[p45cal]

You can use vba to do what you're asking and extract the records you want using a combobox or comboboxes to feed in parameters. You could also do something similar with SQL.
But, I wonder if you can't get what you want more easily still with a pivotable?
If you supply a workbook it would make it easier for others to show you some examples…

[waimea]

Hi p45cal,

thank you for your reply. I am not sure about the capabilities of pivot tables or SQL, I like the structure of list objects with code like
ActiveSheet.ListObjects("Data").ListColumns.Add Position:=4.

I am trying to get the same functionality as the advanced filter but using list objects and arrays.

[waimea]

I want to find, filter and search one or more arrays and extract data from the array and then create one or many new table columns and insert the manipulated data into the current list object.

I would also like to possibility to have column names in an array and then use select case for each column.


ColumnNames = Array("Name", "NumberOfCars", "NumberOfDogs", "Calculations")


Select case ColumnNames

Case is Name
' text column, do nothing

Case is NumberOfCars
' number column
'find the average of the column

Case is NumberOfDogs
'number column
find the standard deviation of the column

Case is Calculations
'use number from earlier columns
'to manipualte data

Case else
' do something

End select



I am also curious on what difference it would be to load an activex combobox into an array? At the moment I am using two for loops to loop through 6 comboboxes, each combobox change takes a while and I want to speed it up?

[snb]

Find all cities with more then 100 000 people, 10 or more sport arenas, 20 or more icehockey teams


Sub M_snb()
With Sheet1.ListObjects(1).DataBodyRange
.AutoFilter 2, ">100000"
.AutoFilter 3, ">9"
.AutoFilter 10, ">19"
End With
End Sub

Next step


Sub M_snb()
With Sheet1.ListObjects(1).DataBodyRange
.AutoFilter 2, ">100000"
.AutoFilter 3, ">9"
.AutoFilter 10, ">19"
.Copy Sheet1.Cells(1, 20)
ComboBox1.List = Sheet1.Cells(1, 20).CurrentRegion.Value
ComboBox1.ColumnCount = UBound(ComboBox1.List, 2) + 1
End With
End Sub

[waimea]

Hi snb, thank you for your reply! Your code indeed does what I want but I am trying to use arrays to be able to manipulate the values, store them in memory, perform some calculations and/or transformations and then inject them back into the table.

I am looking at this example https://chandoo.org/wp/using-arrays-to-update-table-columns/

[waimea]

Hi again,

I can't get the next step to work. I have a combobox1 in Sheet1 but it says runtime error 424: object required at
ComboBox1.List = Sheet1.Cells(1, 20).CurrentRegion.Value

I think that your code copies the options that match the criteria for autofilter to row 1, column 20 and then fills the combobox with those values?

[snb]

Dind't you create an ActiveX combobox in the sheet before running the macro ?

[waimea]

I did, I have tried it a couple of times and it still says object required. I don't know to fix this?

[waimea]

I have though more about what I am trying to do. I want to select an activex combobox and use the combobox change event to load multiple multiple table columns into multiple arrays and then use the match function to find every instance of what I selected in the combobox.

I think that this will be faster then using the combobox change event to recalculate the worksheet and then copy/assign the values from sheet1 to sheet2. As of now the my code takes minutes to run because of the loop using two comboboxes and 250+ vlookups.

I guess that the big problem with this way is that I have to offset the output into the correct cells in some way. I am also trying to loop through an array of table names so that I can decide what tables I want to match the combobox entry.

[snb]

See the attachment

[waimea]

Hi snb,

thank you for your reply! Your code runs but it doesn't update the combobox?

[waimea]

I would very much like a good example on how to use arrays, tables and select cases.

I want to store table names and table headers in two arrays and I think I understand how to. I then want to create a select case statement for table headers and loop through all cases so that I can manipulate data in multiple columns in different ways.

This part I don't understand. All suggestions are welcome!

[snb]

See

https://www.snb-vba.eu/VBA_Userform_in_database_en.html

[waimea]

Thank you for the link, I have read about arrays at that page but I am still struggling with the different parts!

[waimea]

Can anyone help me with this?

[waimea]

I am still working on this, I understand arrays better now but I still can't accomplish what I am trying to do.

Could anyone give me an example on how to load a table into an array, manipulate columns 2 and 3 in different ways and then write the array back to the worksheet!

[snb]

Sub M_snb()
sn=sheet1.listobjects(1).databodyrange

sn(3,2)="snb"
sn(6,3)="example"

sheet1.listobjects(1).databodyrange=sn
End Sub

[waimea]

Hi snb,

thank you for your reply. That is a good example!

Say that I wanted to calculate the average of Column 3 and column 4 and then calculate the standard deviation of column3 and column4 in my table.


Sub waimea()


Dim waimea As Variant
Dim i As Long


waimea = Sheet1.ListObjects(1).DataBodyRange


For i = 1 To 10 Step 1


waimea(i, 1) = Application.WorksheetFunction.Average(ActiveSheet.ListObjects("Table1").ListColumns(1).DataBodyRange.Select)
waimea(i, 2) = Application.WorksheetFunction.Average(ActiveSheet.ListObjects("Table1").ListColumns(2).DataBodyRange.Select)


Sheet1.ListObjects(1).DataBodyRange = waimea




Next i
End Sub


I am guessing that I should use LBound and Ubound?

[waimea]

Snb,

could you help me with another example where I loop through one or more table columns,

by column and by row?

[waimea]

I am really struggling with understanding arrays.

If anyone has good examples of how to use arrays to manipulate data, please post them here!

[Paul_Hossler]

Google found many tutorials on the web for learning to use VBA arrays

Here's an example

https://powerspreadsheets.com/excel-vba-array/


If you have a specific question, ask it here

[waimea]

Hi Paul,

thank you for your reply and for your link! I have read several tutorials and even taken a class on udemy on arrays in VBA.

I am trying to:

1. Read a table into an array
2. Iterate through columns and calculate the average and the standard deviation for each column, save the results in variables.
3. Use the variables to standardize the x values in the columns with the mean/average and the standard deviation using the standardize function
4. Output the standardized columns into a new worksheet

[Paul_Hossler]

Option Explicit

'1. Read a table into an array
'2. Iterate through columns and calculate the average and the standard deviation for each column, save the results in variables.
'3. Use the variables to standardize the x values in the columns with the mean/average and the standard deviation using the standardize function
'4. Output the standardized columns into a new worksheet

Sub demo()
Dim rData As Range
Dim aryData() As Variant, aryMean() As Double, aryStdDev() As Double
Dim iCol As Long, iRow As Long

'2 dim, #rows x #cols in range = (1 to 24, 1 to 5)
Set rData = Worksheets("Sheet1").Cells(1, 1).CurrentRegion

'# cols in range = (1 to 5)
With rData
ReDim aryData(1 To .Columns.Count)
ReDim aryMean(1 To .Columns.Count)
ReDim aryStdDev(1 To .Columns.Count)

'1 to 5 -- aryData is array (1-5) of arrays (1-24)
For iCol = 1 To .Columns.Count
aryData(iCol) = Application.WorksheetFunction.Transpose(rData.Columns(iCol))
Next iCol
End With

'1 to 5
For iCol = LBound(aryData) To UBound(aryData)
aryMean(iCol) = Application.WorksheetFunction.Average(aryData(iCol))
aryStdDev(iCol) = Application.WorksheetFunction.StDev(aryData(iCol))
Next iCol

'1 to 5
For iCol = LBound(aryData) To UBound(aryData)
aryMean(iCol) = Application.WorksheetFunction.Average(rData.Columns(iCol))
aryStdDev(iCol) = Application.WorksheetFunction.StDev(rData.Columns(iCol))
Next iCol

'for 1 to 5
For iCol = LBound(aryData) To UBound(aryData)
'for 1 to 24
For iRow = LBound(aryData, 1) To UBound(aryData, 1)
Worksheets("Sheet2").Cells(iRow, iCol).Value = Application.WorksheetFunction.Standardize(aryData(iRow)(iCol), aryMean(iCol), aryStdDev(iCol))
Next iRow
Next iCol
End Sub

[waimea]

Hi Paul,

thank you for your reply and for your code!

I don't understand why you transpose the data?

[Paul_Hossler]

Hi Paul,

thank you for your reply and for your code!

I don't understand why you transpose the data?


the .Value of a Range returns a 2 dimensional array since Ranges are 2D, and I needed a 1D array to use in Average and StDev

Actually (my bad) I noticed that I left some preliminary code in that I should have cleaned up

Really sorry about that - try this version





Option Explicit

Sub demo()
Dim rData As Range
Dim aryData() As Variant, aryMean() As Double, aryStdDev() As Double
Dim iCol As Long, iRow As Long

'2 dim, #rows x #cols in range = (1 to 24, 1 to 5)
Set rData = Worksheets("Sheet1").Cells(1, 1).CurrentRegion

'# cols in range = (1 to 5)
With rData
ReDim aryData(1 To .Columns.Count)
ReDim aryMean(1 To .Columns.Count)
ReDim aryStdDev(1 To .Columns.Count)

'1 to 5 -- aryData is array (1-5) of arrays (1-24)
For iCol = 1 To .Columns.Count
aryData(iCol) = Application.WorksheetFunction.Transpose(rData.Columns(iCol))
Next iCol
End With

'1 to 5
For iCol = LBound(aryData) To UBound(aryData)
aryMean(iCol) = Application.WorksheetFunction.Average(aryData(iCol))
aryStdDev(iCol) = Application.WorksheetFunction.StDev(aryData(iCol))
Next iCol

'for 1 to 5
For iCol = LBound(aryData) To UBound(aryData)
'for 1 to 24
For iRow = LBound(aryData(iCol)) To UBound(aryData(iCol))
Worksheets("Sheet2").Cells(iRow, iCol).Value = Application.WorksheetFunction.Standardize(aryData(iCol)(iRow), aryMean(iCol), aryStdDev(iCol))
Next iRow
Next iCol
End Sub

[waimea]

Hi Paul,

thank you for your reply and for your updated code. I understand parts of it.

Could you comment the code?

Merry Christmans!

[Paul_Hossler]

I tried to use self-documenting variable names and I like to only use comments when there's something important that's not obvious from the code itself

Most of the VBA commands can be found in online help

If there's something where the purpose is not clear and you can't puzzle it out, I'll be glad to explain

The comments that are there now I made specific to the example data (24 rows x 5 columns) to show how the structure of the array works with the data

[waimea]

Hi Paul,

you are correct and your code is very well written.

I will google the different parts!

[Paul_Hossler]

Hi Paul,

you are correct and your code is very well written.

I will google the different parts!

I'd start with VBA online help first

Put cursor on a VBA keyword and hit F1

Next stop would be Google and look for examples

And you can always ask here if those don't help you