social security number problem

[John]

a cell is dim as social security. I want to copy that to a variable.
What do I dim the variable as?

If I use string, then when I copy it to a different cell dim as social
security it shows up as #######.

If I use some number like integer or double it doesn't get the whole number.

Thanks

john

 

[Rick Rothstein \(MVP - VB\)]

What do you mean when you say "a cell dim as social security"? Do you mean
it is formatted on the worksheet as...

Format Cells/Special/Social Security Number

If so, then in your code, Dim your variable as String, but don't assign the
Value property of the Cell to it; rather, assign the Cell's Text property to
it. For one example...

YourStringVariable = ActiveSheet.Cells(3,4).Text

Rick

 

[Charles Williams]

You need to be careful using .Text because that gets the formatted property
at the last calculation and reformat, which may have inadvertently been
changed to ###### without you noticing because of formatting or zoom changes
etc.

Its usually safer to assign the .Value to a string variable and then return
it to a cell which is formatted the same way as the origin.
(You can copy the formatting using .Copy and .PasteSpecial xlPasteFormats )

regards
Charles
____________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[John]

Yes... I mean Format Cells/Special/Social Security Number. In code I've
just been using Variable = Activesheet.Cells(x,y) with variable dimmed
as a string. When - use Cells(a,b) = Variable I get the ###### things.
Cells(a,b) is dimmed as social security too.

John

 

[John]

That's what I'm doing that doesn't work.

Variable = cells9(x,y) where variable is string and cells is social
security. When I do cells(a,b) = variable I get the #####. Cells(a,b) is
dimmed as social security.

John

 

[Rick Rothstein \(MVP - VB\)]

I think the problem you are having is the column width of your destination
cell is too narrow to contain the full number you are printing into it.
Either widen the column manually or, if you want to do this in code only,
set ColumnWidth property for the destination cell to the ColumnWidth
property of the source cell.

Rick

 

[John]

that isn't it. Widening the column doesn't help. I finally just formated
the receiving cell as general. That fixed the problem. Not perfect bu
good enough.

john