List Object - Add Columns into array - certain conditions

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
939
Hi all, I have a listobject as seen on the screenshot.

I am looking to add items in an array provided that column "On/off" has value "On" and column "§" does not contain the letters "I" or "T".

So in my example the array would contain a single column with just the values:

Kiwis
Strawberries

So far I have come up with this code:

VBA Code:
Sub mytable_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, 2)
  Next x
 
End Sub

But I am not sure how to loop through both of the requirements at the same time. Any ideas would be much appreciated!
 

Attachments

  • Screenshot 2021-03-11 at 13.33.05.jpg
    Screenshot 2021-03-11 at 13.33.05.jpg
    27.8 KB · Views: 4

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
939
I thought about creating a second array like this:

VBA Code:
Private Sub nominals_array()
Dim myTable As ListObject
Dim myArray As Variant
Dim ary As Variant
Dim x As Long
Dim r As Long, nr As Long


Set myTable = x_ledger.ListObjects("accounts_table")
myArray = myTable.DataBodyRange


For x = LBound(myArray) To UBound(myArray)
   If myArray(x, 5) = "On" Then
   If myArray(x, 4) <> "T" Or myArray(x, 4) <> "I" Then
   nr = nr + 1
   ary(nr) = myArray(x, 1)
   End If
   End If
  
Next x

But I am geting a mismatch error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Set myTable = x_ledger.ListObjects("accounts_table")
myArray = myTable.DataBodyRange
ReDim ary(1 To UBound(myArray))

For x = LBound(myArray) To UBound(myArray)
   If myArray(x, 5) = "On" Then
   If myArray(x, 4) <> "T" And myArray(x, 4) <> "I" Then
   nr = nr + 1
   ary(nr) = myArray(x, 1)
   End If
   End If
  
Next x
 

Watch MrExcel Video

Forum statistics

Threads
1,130,323
Messages
5,641,531
Members
417,215
Latest member
Diaryman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top