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/b...t-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-...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?