Destination formatting 2000?

[Guest]

Excel 2003 has the function to match destination formatting when pasting in
to a sheet.
How can I do this in 2000?

 

[Nick Hodge]

Mex

Smarttags where introduced in XP (2002), after 2000, so you could use the
format painter to imitate. The little icon with the paintbrush on

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Guest]

Unfortunatly I am pasting in from internet explorer.
I'll explain my problem further.

I am pasting in numbers with 0's at the beginning. Therefore I loose the
0's. I need to be able to paste in to the document without loosing the 0's
and without going back after the paste to add the 0's.

Thanks

 

[Nick Hodge]

Mex

Try pre-formatting the cells as text

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Guest]

Unfortunatly when you format the cells, then paste in to them the formatting
changes.

If you do this from within excel it will work fine, but I am pasting from
internet explorer.

I wrote a small macro to go back and change the formatting once I have
pasted, but this still does not have the desired affect.

Say I had a number "0044" upon pasting it would change to 44, then I would
run the macro which would change the field in to "special" which was set to
0000. The number will appear to be 0044, but if you look at the formula bar,
you will see that it is actuall still only 44!!!!

Still trying..

 

[JMay]

In a standard module enter:

Sub FillDigits()
'This macro converts cell values from numbers (as General format) to
Text.
'It Converts only a single column of cells at a time
'Before Running this macro make sure you have selected
'the first (top-most) cell in the column to be converted.
'the macro will convert each cell (downward) until it reaches
'a blank cell and will stop
'Current setting below allows for 5 full digits (including your number)

r = ActiveCell.Row
c = ActiveCell.Column
While Not IsEmpty(Cells(r, c))
celVal = Trim(Cells(r, c))
Cells(r, c).NumberFormat = "@"
Cells(r, c) = Application.Rept("0", 5 - Len(celVal)) & celVal
r = r + 1
Wend

End Sub

Hope this helps,

Jim May

 

[Guest]

Thank you JMay, I think we may be getting somewhere, however my inserted text
is horizontal ie. a1, b1, c1 etc.. and the macro didnt seem to work as I
think it decends down a column?!?

Any Ideas?