Write from array to cells using Excel VBA

Question

I need to write from an array to Excel cells down.

I have three values in the array and I would like to write it in cells that it will look like that:

EUR
GBP
USD

Code:

On Error Resume Next
For Each element In CurrencyArray
    myCollection.Add Item:=element, Key:=element
Next element
On Error GoTo 0

ReDim CurrencyArray(1 To myCollection.Count)
For element = 1 To myCollection.Count
   CurrencyArray(element) = myCollection(element)
Next element


Set Summary = Range("M1")
Summary.Value = "Sum Currency"

At this point I would like to write the values to the cells.

ReDim CurrencyArray(0 To myCollection.Count, 1 To 1)
For element = 1 To myCollection.Count
CurrencyArray(element, 1) = myCollection(element)
Next element


CurrencyArray(0, 1) = "Sum Currency"

Set Summary = Range("M1").Resize(UBound(CurrencyArray, 1) + 1)
'Sum currency values
Dim Count As Integer
Dim SumArray As Variant

For Each element In CurrencyArray
 For Count = 2 To lRow
    If ws.Cells(Count, 5) = element Then
      SumArray = SumArray + ws.Cells(Count, 6)
    End If
 Next Count
Next element


Summary.Value = CurrencyArray

It should look like:

EUR 1000
GBP 500
YEN 100

The problem is that I have a spreadsheet with different values, but I'm not allowed to edit the spreadsheet except with VBA.

Currency Amount  
EUR      1000  
EUR      100  
EUR      12  
EUR      70  
GBP      40  
GBP      20

I have to filter the different currencys and sum up the amounts of the currencys. Then I would like to set it to the right site of the excel sheet.

EUR      1182  
GBP      60

but I don't know in advance how many currencies will be there or how big the amount is. At first I would like to filter the duplicates and then sum up the amounts of money depending on the currency.

With regard to your additional problem, you have not provided sufficient information to reproduce your problem, or even be sure what it is. Please read How to create a minimal, complete and verifiable example. You should also look at Chip Pearson's web site so you understand about writing from arrays to a range. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jun 30, 2015 at 12:18
To solve your total problem, I would suggest, as I did below, a Pivot table. Just create a table on the same worksheet; use the macro recorder; then clean it up. It will give you the list of Currencies, and the Sum total for each one. When the list changes, just Refresh it. You may need to edit so that the range for the Pivot updates when you expand the list. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jun 30, 2015 at 13:59

Ron Rosenfeld · Accepted Answer · 2015-06-30 09:47:31Z

1

To write vertically, you need to have a 2D array, and a properly sized target. So (completely UNtested):

ReDim CurrencyArray(0 To myCollection.Count, 1 to 1)
For element =  1 To myCollection.Count
   CurrencyArray(element, 1) = myCollection(element)
Next element

'You can put the header in the first cell of an array where the first dimension is zero-based
CurrencyArray(0,1) = "Sum Currency"

Set Summary = Range("M1").Resize(ubound(currencyarray,1)+1)
 Summary.Value = CurrencyArray

The other option, would be to TRANSPOSE your single dimensioned array. But there are limits as to how large an array you can transpose; and, doing it the above way, when you have to write the elements to the array from the collection individually anyway, saves that step.

edited Jun 30, 2015 at 9:47

answered Jun 30, 2015 at 9:38

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

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

7 Comments

L42 Over a year ago

Haha I posted the transpose version LOL :)

Ron Rosenfeld Over a year ago

@L42 I used to use it a lot, until I ran into the limit of "number of columns in the worksheet", at least in 2003 and 2007. Don't know about later versions. And I still use it for small arrays built using the Array method. But for constructing arrays from collections, I find it simpler to just write to a 2D array.

user24555 Over a year ago

That is exactly what I'm lloking for. Now I need to calculate with these values. e.g. if the value is EUR then it should sum up all values from the table and for GBP then same. This should be written on the left side the EUR 1000 GBP 500 YEN 400

Ron Rosenfeld Over a year ago

Post back if you run into problems. You could probably use SUMIF formulas, although, depending on your data arrangement, I'd wonder if a pivot table might be all you need.

user24555 Over a year ago

I have some problems again. I thought I could save the values in an array, but this won't work. I've added the code which I've written so far. I hope you can correct them.

|

L42 · Accepted Answer · 2015-06-30 09:49:53Z

1

I'm not sure if I get your question correctly but try this:

Dim CurrencyArray As Variant
CurrencyArray = Array("EUR", "GBP", "USD")
Range("A1:A3") = Application.Transpose(CurrencyArray)

answered Jun 30, 2015 at 9:49

L42

19.8k11 gold badges47 silver badges70 bronze badges

1 Comment

user24555 Over a year ago

Not exactly...I've filtered then values before. There were multiple values (EUR,EUR,EUR,GBP,YEN) I had to reduce them to 3 values EUR,GBP,USD and I don't know in advance how many different currency will be used in that sheet.

Collectives™ on Stack Overflow

Write from array to cells using Excel VBA

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related