Special cell formatting problem!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mardo@jippii.fi - 20 Oct 2006 10:20 GMT

Hi!

I have this quite annoying problem whít copy-paste special.

I've made a macro that copies range of cells to another worksheet.
Everything goes fine, but I need to add ' ', to every cell where I
paste cells. Cells containing only numbers are fine, with custom cell
formatting. I just can't get those characters added to cells containing
only text. Text and numbers should go after first ' .

I would really appreciate any help and I have this feeling its really
easy trick and I'm just looking an answer from wrong place.

Heres an example to clear out what I mean:

copied cells After paste, it should look like this...
12345 '12345', | '00015', | 'Ice-Cream', | 'each', |
'100', |
00015
Ice-Cream
each
100
...but text cells are just ... | Ice-Cream

| each | ... ' ... ', are missing!!

Thanks already!!

Mardo

Reply to this Message

mardo@jippii.fi - 20 Oct 2006 10:32 GMT

Ooops... Of course it was ment to look like this:

copied cells:

| 12345 |
| 00015 |
| Ice-Cream |
| each |
| 100 |

After paste, it should look like this...

|12345', | '00015', | 'Ice-Cream', | 'each', | '100', |

but text cells are just | Ice-Cream | each | ' ... ', are missing!!

Sorry,
Mardo

Reply to this Message

Sandy - 20 Oct 2006 15:19 GMT

If your doing this through VBA code you might have better luck posting
this question into microsoft.public.excel.programming and post your
code along with your question. Then everyone will be able to better
understand what you are trying to do....if you are just doing a custom
format then try using '@' as a custom number format

Sandy

> Ooops... Of course it was ment to look like this:
>
[quoted text clipped - 12 lines]
> Sorry,
> Mardo

Reply to this Message

jbutler - 20 Oct 2006 23:05 GMT

As a short solution, would this work?
1) Add a column on your 1st worksheet that adds '', by using a formula (for example, the formula in cell B2 would be ="'"&A2&"'".
2) Then have your macro copy and Paste Special Values from the new column.

Otherwise, I think you'd have to modify your macro to loop through each cell in the range and add the '', which would be a little more work.

Let me know if this helps.
Joe

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Reply to this Message