VBA insert two columns into array

Question

I want to add cell values from two columns into array. I can do it for one column values using:

            ss = Range("B1:R" & lstrow).Value
            For i = 2 To lstrow
            If ss(i, 2) = "FLAG" Then 
            If IsEmpty(dd) Then
            dd(i) = Array(ss(i, 1))
             Else
           ReDim Preserve dd(UBound(dd) + 1)
           dd(UBound(dd)) = ss(i, 1)
                            End If

What If I want to add to dd array another column value for example ss(i,3) so IF ss(i,2) = flag add values of ss(i,1) AND ss(i,3) to dd array

Appreciate your help

Cody Geisler · Accepted Answer · 2016-06-16 15:49:21Z

1

Use

Redim Preserve dd(1 To UBound(dd) + 1, 1 To 3)

This provides 3 columns for every array row.

edited Jun 16, 2016 at 15:49

answered Jun 16, 2016 at 15:27

Cody Geisler

8,6272 gold badges41 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Evgeni Over a year ago

I get EXPECTED: LIST SEPERATOR OR)

Cody Geisler Over a year ago

There's some things I can't see whats going on with your code, such as what lstrow is, or what happens after end if. But the main point of my answer is that you can Define a variant multi-dimensional array using "Dim myArray(1 to 3, 1 to 3) as Variant "

Cody Geisler Over a year ago

UBound(myArray, 1) will give you the row upper bound, UBound(myArray, 2) will give you the column upper bound.

Henrik Eckhoff · Accepted Answer · 2023-10-24 16:03:49Z

Looks like you want to modify your code to include values from both the second and third columns when ss(i, 2) = "FLAG". You can simply update your array creation logic inside the loop to include both values. Here's the modified code:

ss = Range("B1:R" & lstrow).Value

For i = 2 To lstrow
    If ss(i, 2) = "FLAG" Then 
        If IsEmpty(dd) Then
            ' If dd is empty, create a new array with both values
            dd(i) = Array(ss(i, 1), ss(i, 3))
        Else
            ' If dd is not empty, append a new array with both values
            ReDim Preserve dd(UBound(dd) + 1)
            dd(UBound(dd)) = Array(ss(i, 1), ss(i, 3))
        End If
    End If
Next i

This modification creates an array with both ss(i, 1) and ss(i, 3) values when ss(i, 2) = "FLAG".

Collectives™ on Stack Overflow

VBA insert two columns into array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related