| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
social security number problem
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
social security number problem
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 "John" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
social security number problem
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 "John" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
social security number problem
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 Rick Rothstein (MVP - VB) wrote: 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 "John" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
social security number problem
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 Charles Williams wrote: 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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 "John" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
social security number problem
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" wrote in message ... 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 Rick Rothstein (MVP - VB) wrote: 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 "John" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
social security number problem
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 Rick Rothstein (MVP - VB) wrote: 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" wrote in message ... 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 Rick Rothstein (MVP - VB) wrote: 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 "John" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Social Security Number formatting | Excel Discussion (Misc queries) | |||
| last four digits of a social security number | Excel Worksheet Functions | |||
| format social security number | Excel Worksheet Functions | |||
| Social Security Number | Excel Discussion (Misc queries) | |||
| Social Security Number format | Excel Worksheet Functions | |||
![John[_19_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
