Assigning cell value to an array based on a condition

Question

This is my first time using array in VBA. I was trying to check the value of my array based on certain condition.

I check my array value through the Locals Window. The window is empty. What did I do wrong?

Option Explicit

Sub test()

'define dynamic array
Dim sn As Variant 
Dim i As Long

'Loop through all the row
For i = 1 To Rows.Count
    If Cells(i, 12).Value = "Renewal Reminder" And Not IsEmpty(Cells(i, 12).Value) Then
    'assign cell value to array
    sn = Cells(i, 1).Value
    Debug.Print "aaa" ' there are 8 cell values that meet the condition
    End If
Next i

End Sub

Update

Dim sn as Varient was highlighted with Error

user-defined type not defined

Without seeing your spreadsheet I can't say for sure, but here's a few thoughts. Is this using 1 or 0 based indexing? (Is column A1 "row 1 column 1" or is it "row 0 column 0"?) Have you set a breakpoint on the sn or Debug lines? Does the watch window still hold the value once the few milliseconds it takes the function to finish are over? — NeedsAnswers, Dec 29 '17 at 02:42
You are not using `sn` as an array - you are simply storing the value of a single cell into the variable. (FWIW, your `And Not IsEmpty(Cells(i, 12).Value)` is pointless because. if `Cells(i, 12).Value = "Renewal Reminder"` is `True` then you already know that that cell is not `Empty`.) — YowE3K, Dec 29 '17 at 02:51
Your edit says that you have a line saying `Dim sn as Varient`, but that does not appear in your posted code. If the error is correct, you have a typo. — YowE3K, Dec 29 '17 at 02:54
Now, what are you trying to do with an array? Are you trying to collect all the values from column A where column L is "Renewal Reminder"? — YowE3K, Dec 29 '17 at 02:58
@YowE3K Yes, I'm trying to get the cell value and stored in a dynamic array. The array values will be used to send email reminders. In JavaScript I can declare the array and use `array.push()` method. VBA seems alot more harder — Max, Dec 29 '17 at 03:00

YowE3K · Accepted Answer · 2017-12-29T03:17:08.150

Apart from the typo showing in the error message, you are not actually using sn as an array - you are simply storing each value in a scalar variable, replacing what was previously in that variable.

The following should work for you:

Option Explicit

Sub test()

    'define dynamic array
    Dim sn As Variant
    Dim cnt As Long
    Dim i As Long
    ReDim sn(1 To 1)
    cnt = 0
    'Loop through all the row
    For i = 1 To Cells(Rows.Count, "L").End(xlUp).Row
        If Cells(i, 12).Value = "Renewal Reminder" Then
            'assign cell value to array
            cnt = cnt + 1
            ReDim Preserve sn(1 To cnt)
            sn(cnt) = Cells(i, 1).Value
            Debug.Print "aaa" ' there are 8 cell values that meet the condition
        End If
    Next i

    For i = 1 To cnt
        Debug.Print sn(i)
    Next

End Sub

As mentioned in the answer by Chemiadel, it is better to declare your variables using the appropriate base type if you know what that is.

So, if you know that column A contains text, replace Dim sn As Variant with

Dim sn() As String

or, if it is a double-precision number, use

Dim sn() As Double

etc. If column A could contain various different types, using Variant could be appropriate.

Note: You don't have to include the () when using Variant because Variant variables can switch happily between being scalars, arrays, objects, etc.

Thanks for the solution! I was looking at [something similar](http://www.excel-easy.com/vba/examples/dynamic-array.html) but you're faster and better. — Max, Dec 29 '17 at 03:15

score 1 · Answer 2 · 2017-12-29T02:57:51.837

1

You need to declare Array with this way and avoid Variant data type :

Static Array : fixed-size array
```
dim sn(10) as String
```
Dynamic Array : you can size the array while the code is running.
```
dim sn() as String
```

Use ReDim Preserve to expand an array while preserving existing values

ReDim Preserve sn(UBound(sn) + 10)

Check the reference

edited Dec 29 '17 at 02:57

answered Dec 29 '17 at 02:50

1

You can also define `sn` as a `Variant` using `Dim sn As Variant`, and then place an array in it. – YowE3K Dec 29 '17 at 02:52
but I'm not sure the size of my array, as it purely depends on the condition. In that case, `ReDim Preserve sn(UBound(sn) + 10) ` only adds 10, what if I had more than 10? – Max Dec 29 '17 at 02:55
use ReDim and specify the type of the array in first avoid Variant datatype – Dec 29 '17 at 02:57

Assigning cell value to an array based on a condition

2 Answers2