CODEWITHSUNDEEP
excelvba
Clauric
Clauric

Reputation: 1886

Entering values into an array

I am looking for a way to "dump" all the values in 2 columns into 2 separate arrays. Currently these columns contain 12k+ values in each (each column has the same amount of data).

I came across a suggestion that I could use the Range function to do the dump (as below):

With ActiveSheet
    eLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

aRow = 2

cBIN = Range(Cells(2, 26), Cells(eLastRow, 26))
dDealer = Range(Cells(2, 4), Cells(eLastRow, 4))

While aRow <= eLastRow

    Cells(aRow, 35) = cBIN(aRow - 2) & " - " & dDealer(aRow - 2)

    aRow = aRow + 1

Wend

However, when I try to use the code in the While function, nothing is generated.

Edit: Any suggestions how I can get the values into an array without having to had each one individually to each array?

Upvotes: 2

Views: 70

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

use a for loop.

Also since you are already using With, include all the references to that sheet.

And arrays set by direct assignment from ranges are 1 based and 2 dimensional.

Also when assigning a range to an array the array must be declared as a variant

Dim cBIN as Variant, dDealer as Variant
Dim eLastRow as Long, arow as Long
With ActiveSheet
    eLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row    
    cBIN = .Range(.Cells(2, 26), .Cells(eLastRow, 26))
    dDealer = .Range(.Cells(2, 4), .Cells(eLastRow, 4))

    For arow = 2 To eLastRow 
        .Cells(arow, 35) = cBIN(arow - 1, 1) & " - " & dDealer(arow - 1, 1)
    Next arow
End With

Upvotes: 2

omegastripes
omegastripes

Reputation: 12612

You may simply use a formula since the source data located on the sheet, then replace formulas with values, that is much faster than processing within loop:

Sub Test()

    Dim i As Long

    With ActiveSheet
        i = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range(.Cells(2, 35), .Cells(i, 35))
            .FormulaR1C1 = "=RC[-9]&"" - ""&RC[-31]"
            .Value = .Value
        End With
    End With

End Sub

Upvotes: 2

R. Roe
R. Roe

Reputation: 609

To dump a range into an array make sure that your cBIN, and dDealer variables are of type variant you then need to use the Resize function. like this:

cBIN = Cells(2, 26).Resize(eLastRow, 26)
dDealer = Cells(2, 4).Resize(eLastRow, 4)

Then you have two arrays populated with the data from each range. What this does is take the first range then resizes it to the row and col specified (the last row) and column, then dumps that info into your variant array.

Upvotes: 1

Related Questions