End-populate Selected Range

[mlexcelhelpforum]

Hello, it's me again!

I'm sure somebody out there must know how to do this. I'd like to be able to select any range of 4rows X 2columns (4 rows by 2 columns) and systematically tag some text onto the end of the text in those cells.

So it would look like this:

Selected cells:
pancake banana
potato squash
apple spaghetti
coffee cookie

Selected cells after macro is run:
pancake1a banana1b
potato2a squash2b
apple3a spaghetti3b
coffee4a cookie4b

Thanks a bunch!

ML

[stanleydgromjr]

mlexcelhelpforum,

If you are staring with this selection:
pancake banana
potato squash
apple spaghetti
coffee cookie

And, you end up with this:
pancake1a banana1b
potato2a squash2b
apple3a spaghetti3b
coffee4a cookie4b


What does the 1a,1b,... represent?

[snb]

Maybe you are looking for:

Sub snb()
   [A1:B10] = [if(A1:B10="","",A1:B10 & row(A1:B10) & char(column(A1:B10)+96))]
End Sub

if you want to adapt it to A1:E35:

Sub snb()
   [A1:E35] = [if(A1:E35="","",A1:E35 & row(A1:E35) & char(column(A1:E35)+96))]
End Sub

as equivalent of

sub snb_003()
  for each cl in selection
    cl=cl & cl.row & split(cl.address,"$")(1)
  next
End Sub

next

[mlexcelhelpforum]

AHAHAHAHAHA, Stanley,

It does look silly, doesn't it? The numbers represent the cells' positions within the selection.

Hi snb :-)

I'm looking for something really simple, but which I'm incapable of writing, that goes something like:

In the selected area of 4 rows by 2 columns,

the cell which occupies the relative location within the selected area of (1,1) shall have the text: original text + 1a
the cell which occupies the relative location within the selected area of (1,2) shall have the text: original text + 1b
the cell which occupies the relative location within the selected area of (2,1) shall have the text: original text + 2a
the cell which occupies the relative location within the selected area of (2,2) shall have the text: original text + 2b
the cell which occupies the relative location within the selected area of (3,1) shall have the text: original text + 3a
the cell which occupies the relative location within the selected area of (3,2) shall have the text: original text + 3b
the cell which occupies the relative location within the selected area of (4,1) shall have the text: original text + 4a
the cell which occupies the relative location within the selected area of (1,1) shall have the text: original text + 4b

But translated into VBA

[stanleydgromjr]

mlexcelhelpforum,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub EndPopulate()
' stanleydgromjr, 06/18/2011
' http://www.excelforum.com/excel-programming/780650-end-populate-selected-range.html
Dim r As Long, c As Long, H As String
Dim EP() As Variant
EP = Selection.Value
For r = 1 To Selection.Rows.Count
  For c = 1 To Selection.Columns.Count
    EP(r, c) = EP(r, c) & r & Chr(c + 96)
  Next c
Next r
Selection.Value = EP
End Sub

Then run the EndPopulate macro.

[snb]

That's exactly what my suggestions do...