(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).
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,376
Office Version
  1. 365
Platform
  1. Windows
You need to give the array a size.
Are you just looping through a single row?
 

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,376
Office Version
  1. 365
Platform
  1. Windows
try
Code:
Redim SuppressArray(1 to LCol)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,781

ADVERTISEMENT

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:

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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:

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512

ADVERTISEMENT

Thank you for the insight Mike
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,376
Office Version
  1. 365
Platform
  1. Windows
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)
 

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,073
Messages
5,526,651
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top