PASTE.BEFORE [or AFTER] or otherwise concatenating

[riwiseuse]

I'm sure I've done this before. I thought maybe it was a PASTE.SPECIAL option but I can't seem to conjure it up.

I'm trying to paste the contents of a cell in front of the contents of another cell with a comma and space separating them.

I have code that can paste the cells together, but I can't get the syntax right to insert the comma and space.

tried it inside and outside quotes with and without additional ampersands.

as an aside, the data used to be in only two columns. I added a third because I also couldn't figure out code that could get around the self reference, e.g. "RC=RC&RC[1]" kind of thing which is why I was also asking about a PASTE.BEFORE or AFTER approach.

Please Login or Register  to view this content.

any ideas appreciated.

Brian

[watersev]

hi, Brian, asuming:
A1 value: "test A1"
A2 value: "test A2"
the following code results in
A1 value = "test A2, test A1"
the code:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[riwiseuse]

Thanks for he quick code.

I'm looking at it sideways since i write in Excel 4.0 language. How would you make this code able to query the existing selection in order to decide which cells to select.

If you know your target columns but you are picking the row by the row that is selected when you run the macro?

The thing that makes the syntax so touchy is that the only way I have found in this older language to 'push' a reference to the current cell is to use the SELECTION function as a reference with the ROW function.

This returns the Row number. But the only way to call the row number I have found is an RC style reference which has to be surrounded by quotes and then you have to drop out of quotes and make an unquoted reference to the cell in the macro that contains the result of the ROW function and then open quotes again.

This works just fine with the language I have to actually concatenante two cells, but when I start throwing ampersands and quoted text in the middle it starts throwing errors.

Maybe there is a better way to 'push' and 'pop' cells and cell references in VBA in which case I should learn to write in it, although I can't believe it is so hard in XLM. There probably is a better way there too and I just don't know it.

As to the actual operation and the self reference problem, if a formula is a macro operation, so setting a1 = a1 & a2 just does that probably works, but putting the right hand side of the formula in a1 obviously throws a reference error.

It occurs to me that I might rather use the XLM function SET.VALUE rather than FORMULA because I think SET.VALUE can pick up the existing value of A1 and concatenate it with A2 and some text and then put it back in A1 as a value.

Thanks again for any thoughts. Unfortunately I learned what I know (what little that is) while the dinosaurs were walking the keyboard, so I have to translate approaches back and forth.

Brian