VBA Multidimensional Array Question Help

[jaylotheman]

For whatever reason I've been assigned to this task for taking an Excel Spreadsheet with two columns of values (please see attached file). I only have a very basic understanding of VBA but need to do the following which I believe to be done best via a dynamic multidimensional array:

Create an application routine that allows a user to merge the asset data the separate restaurant companies, rename the newly formed company and add it to the list (via a userform).

In addition, the merged restaurant companies must be removed from the list.

Ideally the resulting list will be sorted alphabetically once the merger is completed.

So basically, For e.g. (not in spreadsheet)

Column A Column B
Harveys 400
BK 500
Wendy's 600

If I want to create a dynamic multi-dimensional array that merged all of A so that I could create a "super-entity" so combine A1-A3 and be able to create a new name for it so "Super Wendy-BK-Harvey's" and at the same time it auto-sums values B1-B3 so it creates


Column A Column B
Super Wendy-BK-Harvey's 1500

Make sense?

Can someone help me with the code to do this? Any help would be appreciated thanks.

[mdmackillop]

Welcome to VBAX

Select the restaurants to be combined then run the macro
[VBA]Option Explicit

Sub MakeSuper()
Dim Super As String
Dim Cel As Range
Dim Rng As Range
Dim Asset As Long

Super = "Super "
For Each Cel In Selection
Super = Super & Cel & "-"
Asset = Asset + Cel.Offset(, 1)
Next

Selection.EntireRow.Delete

Set Rng = Cells(Rows.Count, 1).End(xlUp)(2)

Rng = Left(Super, Len(Super) - 1)
Rng.Offset(, 1) = Asset

Range("A:B").Sort Range("A2"), xlAscending, , , , , , xlYes

End Sub[/VBA]

[jaylotheman]

Okay wow, that actually helps a fair bit.

But now, how would I be able to take those restaurants the users select and allow them to rename them into a completely new restaurant name at the time of combining?

Do you know what I mean?

Thanks.

[mdmackillop]

Use an InputBox which allows you to edit/change the name

[VBA]

Rng = InputBox("Enter new name", "Renaming", Left(Super, Len(Super) - 1))

[/VBA]

[Tinbendr]

Wow, my effort isn't nearly as elegant as Mdmackillop, but it seems to work.

You didn't state which version. This was written in 2010, but should be good for 2007.

HTH
David

[Zack Barresse]

http://www.tek-tips.com/viewthread.c...1625167&page=1
http://www.xtremevbtalk.com/showthread.php?t=318708
http://www.ozgrid.com/forum/showthre...=147343&page=1
http://www.excelforum.com/excel-prog...tion-help.html

All of the above links are cross-posts. Please do NOT cross-post. Thanks.