Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
number as string and adding spaces to it
i need length of data input into cells to be a mulitple of 78. i have worked
that out except when the input is a number only. if you input 12 i want to add 76 spaces after the "12". My code recognizes that its length is 2 and that it needs to add 76 spaces but it doesn't {code} Dim blank As Variant Dim length As Integer Dim f_fix2 As String For Each text_cell In text blank = CStr(text_cell.Value) length = Len(text_cell) Dim f_fix2 As String remnder = length Mod 78 .... check for lenth=0 or mulitple of 78 if not f_fix2 = Space(78 - remnder) text_cell.Value = blank + f_fix2 Thank you for taking a look Alfred |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
number as string and adding spaces to it
Hello
try dim the string Ex: dim str as String * 78 str= cells(rng).value if str=12 you get str with 12 and 76 spaces "A.New" wrote in message ... i need length of data input into cells to be a mulitple of 78. i have worked that out except when the input is a number only. if you input 12 i want to add 76 spaces after the "12". My code recognizes that its length is 2 and that it needs to add 76 spaces but it doesn't {code} Dim blank As Variant Dim length As Integer Dim f_fix2 As String For Each text_cell In text blank = CStr(text_cell.Value) length = Len(text_cell) Dim f_fix2 As String remnder = length Mod 78 ... check for lenth=0 or mulitple of 78 if not f_fix2 = Space(78 - remnder) text_cell.Value = blank + f_fix2 Thank you for taking a look Alfred |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
number as string and adding spaces to it
I think the key is to have an apostrophe before your value.
That is a necessity to enter a number as a string. Also, I think & is preferred over + for strings. Sub sub1() Dim CellVal$, Padding$, Text_Cell As Range For Each Text_Cell In Selection CellVal = Text_Cell.Value Padding = Space$(77 - ((Len(CellVal) + 77) Mod 78)) Text_Cell.Value = "'" & CellVal & Padding Next Text_Cell End Sub A.New wrote: i need length of data input into cells to be a mulitple of 78. i have worked that out except when the input is a number only. if you input 12 i want to add 76 spaces after the "12". My code recognizes that its length is 2 and that it needs to add 76 spaces but it doesn't {code} Dim blank As Variant Dim length As Integer Dim f_fix2 As String For Each text_cell In text blank = CStr(text_cell.Value) length = Len(text_cell) Dim f_fix2 As String remnder = length Mod 78 ... check for lenth=0 or mulitple of 78 if not f_fix2 = Space(78 - remnder) text_cell.Value = blank + f_fix2 Thank you for taking a look Alfred ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
Removing Spaces from string | Excel Worksheet Functions | |||
counting spaces in a string | Excel Programming | |||
counting spaces in a string | Excel Programming | |||
Count Spaces In A String | Excel Programming |