(VBA) Dynamically populating an array

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
Hi forum

I am trying to populate an array in a loop. However, out of inexperience with arrays, I can't seem to populate my array.

Code:
Dim SuppressArray As Variant

i = 1
   For Each c In m_wb.Sheets("Componenten").Range(Cells(cell.Row, "N"), Cells(cell.Row, LCol))
        If c.Value <> "" And IsNumeric(Left(m_wb.Sheets("Componenten").Cells(1, c.Column), 1)) = True Then
        SuppressArray(i) = Left(m_wb.Sheets("Componenten").Cells(1, c.Column), InStr(1, m_wb.Sheets("Componenten").Cells(1, c.Column), "M") - 1)
        i = i + 1
        End If
    Next c
    If i = 1 Then GoTo nosuppress

In short: If the cell in the range is not empty and the header's first character is a number, then populate the array with the first numbers in that header. Then go to the next cell and repeat the process.

The code is breaking on the line that is supposed to fill the array (Type Mismatch).
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
You need to give the array a size.
Are you just looping through a single row?
 
Upvote 0
Fluff,

I've read about that, but I am not sure how to do that since my array size is unknown until the loop stops. I am indeed looping through a single row.

I wonder, can I size the Array from 1 to 100 but populate it with only a couple of values? That would, I think, solve the issue.
 
Last edited:
Upvote 0
try
Code:
Redim SuppressArray(1 to LCol)
 
Upvote 0
I wonder, can I size the Array from 1 to 100 but populate it with only a couple of values? That would, I think, solve the issue.

You could use that approach
Code:
Dim SuppressArray() As Variant
Dim c As Range, Pointer As Long

Redim SuppressArray(1 to 100)
Pointer = 0

For Each c In m_wb.Sheets("Componenten").Range(Cells(cell.Row, "N"), Cells(cell.Row, LCol))
    If c.Value <> "" And IsNumeric(Left(m_wb.Sheets("Componenten").Cells(1, c.Column), 1)) = True Then
        Pointer = Pointer + 1
        SuppressArray(Pointer) = Left(m_wb.Sheets("Componenten").Cells(1, c.Column), InStr(1, m_wb.Sheets("Componenten").Cells(1, c.Column), "M") - 1)
       
    End If
Next c

Redim Preserve SuppressArray(1 to Pointer)
 
Last edited:
Upvote 0
Cheers Fluff, that does what I want.

I wonder, why ReDim? This function isn't known to me. I've only used ReDim and dropped Dim as Variant.
 
Last edited:
Upvote 0
I wonder, why ReDim? This function isn't known to me. I've only used ReDim and dropped Dim as Variant.
I normally declare all my variables at the top of the sub. Therefore I do not know how big an array needs to be when it's declared. As you can onlyDim as variable once, you use ReDim.
From what you've shown of your code it looks like LCol has a value before the Dim statement, in which case you could simply use Dim SuppressArray (1 to LCol)
 
Upvote 0
Thanks for that explanation.

I normally don't declare my variables at all which is a terrible practice but so far has yet to cost me. :)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top