VBA: Fill Array2 from Array1 with >= Values

[zero269]

Hello,

I'm just learning to work with arrays, and right now I'm able to fill an Array with values from another Array where the Value is >= 990000.

1st Array = arrAR
2nd Array = arrNonAR

I'm first storing a Range of values into the 1st array that will be used to populate the 2nd array with values that are greater than or equal to 990000.

I'm expecting to get 1,238 values into the 2nd array, however it's only being filled with 989… based on the Array size (0 to 988). I should note that the Table column is formatted as General, and changing it to Number had no positive effect.

​

Any help would be greatly appreciated.

Sub WIP_Array_NonAR()

  'Declarations
  Dim arrAR As Variant, arrNonAR As Variant, i As Long
  
  'Fill Array - All Quiz Numbers
  arrAR = wsBooks.Range("t_Books[Quiz]") 'A3:A49946
  
  'Initialize Array for NonAR Values
  ReDim arrNonAR(0)

  'Fill NonAR Array with values >= "990000"
  For i = LBound(arrAR) To UBound(arrAR)
    If (arrAR(i, 1) >= "990000") Then
      arrNonAR(UBound(arrNonAR)) = arrAR(i, 1) 'Add Quiz number to array for selected row
      ReDim Preserve arrNonAR(UBound(arrNonAR) + 1) 'Resize array +1 index
      i = i + 1 'index counter
    End If
  Next i

  'Remove last/empty index
  ReDim Preserve arrNonAR(UBound(arrNonAR) - 1) 'expecting 1238; returning 988
  
End Sub

Interestingly enough, if I remove the double-quotes from the criteria number, it returns a smaller number of results:

If (arrAR(i, 1) >= 990000) Then

Thank you and best regards,

 

[NdNoviceHlp]

It seem that there a a few things wrong. HTH. Dave

'need to make 1 dimensional array
arrAR = Application.WorksheetFunction.Transpose(wsBooks.Range("t_Books[Quiz]")) 'A3:A49946
  
'need 1 array posn to start
ReDim arrNonAR(1)

'don't need. Already increments with loop
'i = i + 1 'index counter

 

[Peter_SSs]

Firstly, only giving partial code makes us guess a bit. You have wsBooks in your code but the code does not define of populate it. It is not critical this time but its best if have all the information.

If (arrAR(i, 1) >= "990000") Then

This is doing al alphabetical comparison so "999" is bigger than "990000" alphabetically and "10000000" is small than "990000" alphabetically.

Interestingly enough, if I remove the double-quotes from the criteria number, it returns a smaller number of results:

.. due to my comment above.

If the data is numerical then you should not have the double quotes.

i = i + 1 'index counter

Having this line within an "i" For ... Next loop means you will be skipping even looking at some values. For example, if i=1 produces a value for the new array, the above line increases i to 2 and then the Next i line increases it again to 3 so the second item checked will be the third item in the first array.


The original array is a 1-based array and you new array is a 0-based array. Is that what you really want? See @NdNoviceHlp's comment above.

Instead of increasing the dimension of the new array every time a new value is found, my suggestion is to make it big enough to hold all values and then reduce the size once at the end if required.

Sub WIP_Array_NonAR_v2()
  'Declarations
  Dim arrAR As Variant, arrNonAR As Variant, i As Long, k As Long, wsBooks As Worksheet
 
  Set wsBooks = ActiveSheet   '<- .. or whatever
 
  'Fill Array - All Quiz Numbers
  arrAR = wsBooks.Range("t_Books[Quiz]")
 
  'Initialize Array for NonAR Values
  ReDim arrNonAR(1 To UBound(arrAR))

  'Fill NonAR Array with values >= 990000
  For i = LBound(arrAR) To UBound(arrAR)
    If (arrAR(i, 1) >= 990000) Then
      k = k + 1
      arrNonAR(k) = arrAR(i, 1) 'Add Quiz number to array for selected row
    End If
  Next i

  'Remove last/empty indexex
  ReDim Preserve arrNonAR(1 To k)
 
End Sub

 

[kevin9999]

Does the following also give you your desired end result?

Sub Another_Option()
    Dim arrNonAR
    arrNonAR = Evaluate("FILTER(t_Books[Quiz],t_Books[Quiz]>=990000,"""")")
End Sub

 

[zero269]

Hi NdNoviceHlp,

Thank you for looking into this for me. I made some changes based on your recommendations…

'need to make 1 dimensional array arrAR = Application.WorksheetFunction.Transpose(wsBooks.Range("t_Books[Quiz]")) 'A3:A49946

I plan to use a 2D Array later on as I'll be adding some additional criteria that will make this initial list even smaller. However, I do have a need for some 1D arrays in other areas of my workbook, so this method will work perfectly. Especially when I'm just pulling a list of Names from a Table.

'need 1 array posn to start ReDim arrNonAR(1)

Setting the starting position to 1 didn't necessarily fix my issues with the 2nd Array. However, after looking at Peter's post, I realize I'm not setting my Upper/Lower boundaries correctly. Makes since, considering I don’t really know what I'm doing… just winging it you could say.

As you can see, setting it to 0 (my original setting) I get an empty value and an Array from 0 to 1238.

Setting it to 1 resolves that empty value, and an Array from 0 to 1237.

'don't need. Already increments with loop 'i = i + 1 'index counter

As for my use of the i = i + 1 I'll just chalk that up to my earlier response about not knowing what I'm doing.

I'm trying to transition from some massive formula uses in a particular workbook to VBA. Right now I'm just trying to figure out if it's going to benefit me; but first I need to learn more about properly leveraging Arrays. It's certainly a complex topic.

Thanks again and best regards,

 

[zero269]

Firstly, only giving partial code makes us guess a bit. You have wsBooks in your code but the code does not define of populate it. It is not critical this time but its best if have all the information.

If (arrAR(i, 1) >= "990000") Then

This is doing al alphabetical comparison so "999" is bigger than "990000" alphabetically and "10000000" is small than "990000" alphabetically.


.. due to my comment above.

If the data is numerical then you should not have the double quotes.

i = i + 1 'index counter

Having this line within an "i" For ... Next loop means you will be skipping even looking at some values. For example, if i=1 produces a value for the new array, the above line increases i to 2 and then the Next i line increases it again to 3 so the second item checked will be the third item in the first array.


The original array is a 1-based array and you new array is a 0-based array. Is that what you really want? See @NdNoviceHlp's comment above.

Instead of increasing the dimension of the new array every time a new value is found, my suggestion is to make it big enough to hold all values and then reduce the size once at the end if required.

Sub WIP_Array_NonAR_v2()
  'Declarations
  Dim arrAR As Variant, arrNonAR As Variant, i As Long, k As Long, wsBooks As Worksheet
 
  Set wsBooks = ActiveSheet   '<- .. or whatever
 
  'Fill Array - All Quiz Numbers
  arrAR = wsBooks.Range("t_Books[Quiz]")
 
  'Initialize Array for NonAR Values
  ReDim arrNonAR(1 To UBound(arrAR))

  'Fill NonAR Array with values >= 990000
  For i = LBound(arrAR) To UBound(arrAR)
    If (arrAR(i, 1) >= 990000) Then
      k = k + 1
      arrNonAR(k) = arrAR(i, 1) 'Add Quiz number to array for selected row
    End If
  Next i

  'Remove last/empty indexex
  ReDim Preserve arrNonAR(1 To k)
 
End Sub

Hi Peter,

There's certainly a lot to say after reading your post. You've done a great job highlighting my major deficiencies… and as always… I'm thankful for that. I learn more and more each time you jump in to help me.

Long story short… I took heed to your wisdom and I think I was able to get some working code that will help me learn even more about using arrays… when suitable.

Because my Reading Tracker has become such a massive workbook with endless formulas and a tons of Macros… it's become quite slow. As a possible solution, I'm trying to separate the two main functions into two separate workbooks…

Book Tracker and Student Tracker. Let's just say that keeping it all in one workbook will likely be the end result… however, that doesn't negate my need to try… as painful as it is.

Here's what I've got based on yours and NdNoviceHlp's expert feedback:

Sub WIP_Array_NonAR_2D()

  'Declarations
  Dim arrAR As Variant, arrNonAR As Variant
  Dim i As Long, j As Long
  
  'Fill 2D Array - All Quiz Numbers
  arrAR = wsBooks.Range("t_Books[Quiz]") 'A3:A49946
  
  'Initialize Array for NonAR Values
  ReDim arrNonAR(1 To UBound(arrAR))
  
  'Fill NonAR Array with values >= 990000
  For i = LBound(arrAR) To UBound(arrAR)
    If (arrAR(i, 1) >= 990000) Then
      j = j + 1 'index counter for 2nd Array
      arrNonAR(j) = arrAR(i, 1) 'Add Quiz number
    End If
  Next i

  'Remove last/empty index
  ReDim Preserve arrNonAR(1 To j)
  
End Sub

You recommended setting the boundary's so it didn't have to be resized at the end, so I thought I would try this which seems to do the trick:

Replacing THIS: ReDim arrNonAR(1 To UBound(arrAR))
With THIS: ReDim arrNonAR(1 To WorksheetFunction.CountIf(wsBooks.Range("t_Books[Quiz]"), ">=" & 990000))

As you noted, there would be no need to resize the array at the end either, thus removing this: ReDim Preserve arrNonAR(1 To j)

So far, so good, but I'm sure I'll be back soon as I expand on this further…

Thanks a lot for your help Peter, and best regards,

Sub WIP_Array_NonAR_2D()

  'Declarations
  Dim arrAR As Variant, arrNonAR As Variant
  Dim i As Long, j As Long
  
  'Fill 2D Array - All Quiz Numbers
  arrAR = wsBooks.Range("t_Books[Quiz]") 'A3:A49946
  
  'Initialize Array for NonAR Values
  ReDim arrNonAR(1 To WorksheetFunction.CountIf(wsBooks.Range("t_Books[Quiz]"), ">=" & 990000))
  
  'Fill NonAR Array with values >= 990000
  For i = LBound(arrAR) To UBound(arrAR)
    If (arrAR(i, 1) >= 990000) Then
      j = j + 1 'index counter for 2nd Array
      arrNonAR(j) = arrAR(i, 1) 'Add Quiz number
    End If
  Next i
  
End Sub

 

[kevin9999]

Just out of interest, did you try the code in post #4 - which fills your array in one step without the need to loop?

 

[zero269]

Does the following also give you your desired end result?

Sub Another_Option()
    Dim arrNonAR
    arrNonAR = Evaluate("FILTER(t_Books[Quiz],t_Books[Quiz]>=990000,"""")")
End Sub

Hi Kevin,

That's a resounding YES! At first I thought this was just going to populate the 1st Array…. I then realized after inspecting the results that this method actually creates the 2nd Array; 2-Dimensional which isn't an issue for me in this case.

Right now I'm trying to get more experience with the whole Array Looping so to speak… this way I can then learn how to manipulate data in multi-dimensional arrays that I can export, write back to a range, or perform calculations…

Just sorting an array seems quite complex after a few minutes exploring that idea earlier today…

This Evaluate approach is going to come in handy. I've already got another macro where I think this will be better suited.

It's after midinight... and I need some sleep!

Thanks Kevin, and best regards,