Pass cell contentent into VBA array?

[MikeJP]

Is there a way tp *** the contents of a column of cells into a VBA array.

Thanks
Mike Piles

 

[Juan Pablo González]

Sub TryThis()
    Dim Ar As Variant
    Ar = Range("A1:C5").Value
End Sub

 

[MikeJP]

Hi Juan,

Thanks for the help. Evidently I don't understand the macro example you gave me. Below is the code I am working with:

Sub AcctNumConvert()

    Dim LNL(4) As String
    Dim dt(4) As String
    Dim cell As Range


    LNL(1) = "EV"
    LNL(2) = "DD"
    LNL(3) = "LEV"
    LNL(4) = "10"



    dt(1) = "1"
    dt(2) = "2"
    dt(3) = "3"
    dt(4) = "4"

    For Each cell In Range("A1:A" & Range("A50").End(xlUp).Row)
        If Left(cell, 3) = LNL(1) Then cell.Value = dt(1)
        If Left(cell, 3) = LNL(2) Then cell.Value = dt(2)
        If Left(cell, 3) = LNL(3) Then cell.Value = dt(3)
        If Left(cell, 3) = LNL(4) Then cell.Value = dt(4)

    Next cell


End Sub

The values in the LNL array are constant, but the values in thr DT array change from project to project. Right now I have cells that I use to concatonate the DT array. I then copy the array and paste values to another column of cells. Then I copy the cells containing the array and paste it into the macro. It would be nice if I could just change the cell values and have the automaticaly pass to the array in the macro.

Thanks Again
Mike Piles

 

[Richie(UK)]

Hi Mike,

Not entirely sure that I follow what you want to do. Anyway, hopefully the following info will help.

As JP indicated, you can pass the data in a range to a variant array. This will be a 2 dimensional array (rows and columns). You can then manipulate the array according to your wishes and then pass the array details back to a range. Note that the range size that you are passing back to must be the same as the size of the array.

For example:

Sub Test()
    Dim dt(1 To 4) As String
    Dim rngData As Range, vArray As Variant
    Dim l As Long
    
    dt(1) = "1"
    dt(2) = "2"
    dt(3) = "3"
    dt(4) = "4"

    Set rngData = Sheet1.Range("A1:A" & Range("A50").End(xlUp).Row)
    'the range to consider
    vArray = rngData.Value
    'pass range values to 2D variant array
    
    For l = LBound(vArray, 1) To UBound(vArray, 1)
        Select Case Left(vArray(l, 1), 3)
            Case Is = "EV ": vArray(l, 1) = dt(1)
            Case Is = "DD ": vArray(l, 1) = dt(2)
            Case Is = "LEV": vArray(l, 1) = dt(3)
            Case Is = "10 ": vArray(l, 1) = dt(4)
        End Select
    Next l
    'do stuff with array
    
    rngData.Value = vArray
    'pass array back to range
    
End Sub

HTH

 

[MikeJP]

Hi Ritchie,

Thanks for the help. The Macro is used to convert acccount types from an Investment company LNL to the account types used in our pension system DT. This information is in column A. For example: From the investment company Salary Deferal money is typed as "EV" but in our pension system the money needs to be put into account "1" the macro will convert all the EV's in column A to 1. The account numbers vary from plan to plan in the pension system, so thr DT array needs to be modified to fit each plan. Once I have the account numbers converted I can save as a .csv file and import the information dirrectly into our pension system.

Thanks
Mike

 

[mikey1987]

Hello guys,

Hope you don't mind me bringing back this topic.
I don't seem to find an answer to this question really.

What I want to do is actually quite simple.

I want to do a "For Each ... in array" loop. The array itself is variant.

What I got so far (based on this discussion) is:

Sub TryingWhatYouSaid()


Dim MyName As Variant
Dim MyArray As Variant


MyArray = Range("H2:H14")


For Each MyName In Array(MyArray)

...

Next MyName

So I would assume it would first loop with the value in H2, then H3 and so on. However... it doesn't...

Any help on this?

Regards,
Mikey