Using VBA to Append Names to an Array (Excel)

[georgedixon]

Hi,
I am attempting to manipulate data that is stored in a pivot table. The data in the pivot table is a quote system, showing each quote that has been processed and which employee has processed it. I am simply trying to loop through the list to store each employees name in the same array, and then print these names onto the page (it should only add each name to the array once, there are no duplicates). I have coded it into a test workbook and attached a test workbook, but I am having trouble understanding how arrays work on VBA, as my loop is clearly not working.

Any help is appreciated

[romperstomper]

For a unique list, I'd suggest a dictionary (or collection) rather than an array. Your code is then much simpler:

Please Login or Register  to view this content.

for example.

[georgedixon]

Thank you very much. Would there be a way to assign a value to each of the names in the dictionary? for example the total revenue bought in by "James".

[romperstomper]

Yes, although you appear to be beasically replicating the functions of a pivot table.

Please Login or Register  to view this content.

[Norie]

Would there be a way to assign a value to each of the names in the dictionary? for example the total revenue bought in by "James".

To expand on Rory's code.

Please Login or Register  to view this content.

[georgedixon]

Yes haha, I have never used pivot tables before but am reasonably familiar with VBA hence was attempting to do it this way. Thanks again, that works perfectly.

[georgedixon]

To expand on Rory's code.

Please Login or Register  to view this content.

Hello again, I was just transferring the code to my other sheet and realised that when it stores and sums the sales values, it stores them as strings (with the £ sign), I was just wondering if there was a way to purely store the numerical value? as the it seems to convert them to text otherwise and I can not manipulate them further.

[georgedixon]

Ignore my previous comment, I found a way round it, Thanks again

[Norie]

That's totally bizarre, nowhere in the code is the pound sign being added and if the pound sign was being included when adding the values in the dictionary I would expect to get type mismatch errors.

Also, when I check in the immediate window none of the items in the dictionary seem to have a pound sign.

Even weirder if I change the code to this,

Please Login or Register  to view this content.

you still get the pound signs but the values are numeric.

[georgedixon]

I think that originally because the code was retrieving values that had been formatted as currency it was taking them to be part of the value for some reason and then storing it as a string. My way around it was just to add in another macro after the lists had been generated that removes them

[Norie]

As far as I could see when debugging the values being retrieved weren't formatted as currency.

If they were then the addition in the code would have failed with a type mismatch.

I'm totally mystified as to where the £s are coming from in the final result.

Perhaps we should get somebody outwith the UK to give it a try and see what happens.

[nigelog]

Hi Norie
in Ireland but I get no £ or currency at all from the data or code

[romperstomper]

The cells are formatted with a currency format, so the .Value property returns a Currency data type. That's why you get the formatting when it's assigned to a cell (and also rounding to 2DP). Using .Value2 instead would remove that behaviour.