Pfthb

OK, So I have a very specific problem that I hope to make not-a-problem, but I need some help. I am a complete neophyte when it comes to making Macros.

I’ve got a customer that has put long lists of references in a single column in Excel (normally, the references are just in one cell). I’ve figured out how to consolidate a selected portion of a column of references into a single cell (see my procedure below), but what I’d really like to do is to turn this into a Macro that does it instantly.

The problem is that I’d like to be able to run a Macro on a selection, that is, I’d like to select the cells and have the macro run on ONLY those cells. Then I could select the cells, run the macro, make another selection, run the macro, etc. This would speed up my work tremendously.

My problem: I can’t figure out how to make the Macro run on just a selection. The Macros I’ve recorded only run on the selections I’ve made while recording the Macro. Is there any way to make the Macro generic, so I can select cells, and then run the Macro on them?

Combining References in Excel

1. Let’s say the references you want to combine are in E18:E27


2. Make a new column next to the reference designator column, for consolidated references


3. In a blank cell in the new column, where you want to combine all the values, type:


4. =CONCATENATE(TRANSPOSE(


5. Then select the cells that need to be combined


6. The formula will change to =CONCATENATE(TRANSPOSE(E18:E27


7. Don’t press enter yet.


8. Click after the last cell reference and hit spacebar


9. Type the & operator


10. Type ”, ” (double quote, comma, space, double quote)


11. This will add a comma and a space after every reference


12. Select TRANSPOSE(E18:E27 &", " and press the F9 key (F9 replaces formulas with values)


13. This replaces TRANSPOSE(E18:E27 &" " with its result, "E18, ","E19, ","E20, ","E21, ","E22, ","E23, ","E24, ","E25, ","E26, ","E27, "


14. Now remove the curly brackets and


15. The formula will now look like =CONCATENATE("E18, ","E19, ","E20, ","E21, ","E22, ","E23, ","E24, ","E25, ","E26, ","E27, "


16. Select the entire formula, including the = sign, and hit F9


17. Hit Enter


18. Done!

Note: this only works for up to 256 references. Any more and the formula will thrown an error. If there's any way to change it so it will work on more than that, please let me know.

Here's the code I get just by Record Macro-ing the procedure above, with "relative references" turned on. I don't know if that will help me or not.

Sub ConsolidateReferencesMacro2()
'
' ConsolidateReferencesMacro2 Macro
'

'
 ActiveCell.Offset(-1, 3).Range("A1").Select
 ActiveCell.FormulaR1C1 = _
 "U102, U103, U104, U105, U199, U200, U201, U202, U204, U205, U206, 
U207, U232, U233, U234, U235, U245, U246, U44, U45, U65, U66, "
With ActiveCell.Characters(Start:=1, Length:=128).Font
 .Name = "Calibri"
 .FontStyle = "Regular"
 .Size = 11
 .Strikethrough = False
 .Superscript = False
 .Subscript = False
 .OutlineFont = False
 .Shadow = False
 .Underline = xlUnderlineStyleNone
 .ThemeColor = xlThemeColorLight1
 .TintAndShade = 0
 .ThemeFont = xlThemeFontMinor
End With
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I don't see "Concactencate" and "Transpose" so I don't know what is actually happening in this bit of code.

Paste following code in a module:

Option Explicit

Sub concat_result()
Selection(Selection.Count, 1).Offset(0, 1) = concat_select(Selection)
End Sub

Function concat_select(R1 As Range) As String
concat_select = Join(Application.Transpose(R1.Value2), ",")
End Function

Then you can either call the function concat_select as a standard worksheet function or use the concat_result sub after selecting your range via a button or shortcut.

I’m not 100% sure if I understand everything correctly, but as mentioned in my comments, it seems as if you could take advantage of using loops.

As I can see you’re new to the VBA-game.
I therefore explain some things to you, dom’t know if you already know some terms..

Looping in Excel can be used for ranges, for great results. This will let you be able to look in each cell, and determine a value, format or whatever you want, based on this cell.

You told me your range was in a selection, and that you want to make your code beside it.

Your code could therefore be something like:

Sub ConsolidateValues()
Dim c As Range

MyRange = Application.Selection.Address(False, False, xlA1)
FrstC = Left(MyRange, Application.WorksheetFunction.Find(":", MyRange) - 1)
CVal = ""

For Each c In Selection

CValue = c
CVal = CVal & CValue & ", "

Next c

Range(FrstC).Offset(0, 1).EntireColumn.Insert
FrstCOff = Range(FrstC).Offset(0, 1).Address(False, False, xlA1)
Range(FrstCOff).Value = CVal

End Sub

This should consolidate your Range into one cell to the right of “E17”.