CODEWITHSUNDEEP
arraysexcelvbamemoryformula
Benjamin
Benjamin

Reputation: 561

Reading information from Memory array into excel sheet formula

I'd like to use data from an array that I've got stored in my VBA memory directly into a formula in my sheet. As an example, I'd like to avoid using Application.vlookup() to print to each cell individually as this is slow. And instead do something like the following

Sub MySub()
    Dim MyArrayStoredInVBaMemory() As Variant
    MyArrayStoredInVBaMemory = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
    Cells(1, 1).Value = 1
    Cells(1, 2).FormulaR1C1 = "=vlookup(RC1,MyArrayStoredInVBaMemory,1,0)"
    Cells(1, 2).Copy
    Cells(1, 2).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Your help is appreciated.

Upvotes: 0

Views: 225

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

One way is to make a String from the Array

Sub qwerty()
    ary = Array(1, 2, 3)
    Dim st As String
    st = ary(0) & "," & ary(1) & "," & ary(2)
    Range("A1").Formula = "=SUM(" & st & ")"
End Sub

Upvotes: 2

Related Questions