Is it possible to combine values in an array using VBA?

Question

I'm new to VBA and I'm stuck.. I have a table with workers and the amount of time they have been working. I want to insert all the workers into an array with the total time they have been working and then print out them into another sheet.

Example of table:

The problem is that I don't know if it's even possible to insert these values together into an array.. Right now I have hard-coded the names into my code and use If-statements to add hours to the right name but that will be a problem if I add more workers to the table (because then I need to add those names into the code too). I don't want the code to know the names or how many workers there are before it runs.

I want the print-out to be like:

Steve | 13
Emma  | 2
Andy  | 3
Jeff  | 12

Appreciate any help!

one cannot "print out them into another sheet"; please clarify this: just "print out"? paste? another work(sheet)?... — Gene Skuratovsky
– Gene Skuratovsky, Commented Feb 17, 2015 at 15:02
Well I'm sorry about that.. but as I stated in the text I was only able to write the names as integers and then make If-statements to collect the data. If I understood how arrays worked in vba I would try my best to do it by myself.. But thanks anyway! — elefantsko
– elefantsko, Commented Feb 17, 2015 at 16:28

Vasily · Accepted Answer · 2015-02-17 13:21:01Z

2

use this or something like this:

Sub test()
    Dim Dic As Object, oCell As Range, i&, y%, key As Variant
    Set Dic = CreateObject("Scripting.Dictionary")
    y = 1: i = Cells(Rows.Count, "A").End(xlUp).Row
    For Each oCell In Range("A2:A" & i)
        If Not Dic.exists(oCell.Value) Then
            Dic.Add oCell.Value, WorksheetFunction.SumIf(Range("A2:A" & i), oCell.Value, Range("B2:B" & i))
        End If
    Next
    For Each key In Dic
        Debug.Print key, Dic(key)
    Next
End Sub

here the screenshot

enter image description here

answered Feb 17, 2015 at 13:21

Vasily

5,7923 gold badges22 silver badges36 bronze badges

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

2 Comments

elefantsko Over a year ago

Thank you so much! I was actually able to solve the problem with your help. As a java/C# programmer I can't believe how hard it is to do something like this in vba..

Amen Jlili Over a year ago

Nice dictionary approach up one :)

Amen Jlili · Accepted Answer · 2015-02-17 12:55:56Z

1

Very simple way:

Function GetTable(r As Range) As Variant
GetTable = r.Value
End Function

Sub main()
Dim Table As Variant
Table = GetTable(ActiveSheet.Range("A2:B6")) 'Supply the range of your workers.
For i = 1 To UBound(Table, 1)
Sum = Sum + Table(i, 2)
Next
MsgBox (Sum)
End Sub

ouput: enter image description here

answered Feb 17, 2015 at 12:55

Amen Jlili

1,9444 gold badges28 silver badges52 bronze badges

1 Comment

SierraOscar Over a year ago

Is there any need for the GetTable() UDF? Just assign the value directly to get the array...

pnuts · Accepted Answer · 2015-02-17 13:30:20Z

0

Just use a PivotTable and if necessary have the PivotTable automatically refresh when the sheet is clicked.

edited Feb 17, 2015 at 13:30

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

answered Feb 17, 2015 at 13:24

smackenzie

3,0929 gold badges61 silver badges114 bronze badges

Collectives™ on Stack Overflow

Is it possible to combine values in an array using VBA?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related