Add spaces to a string
-
-
-
Re: Add spaces to a string
incalthis,
If you want to make an existing string have a length of 450 characters then use this loop:
-
Re: Add spaces to a string
Thanks for the advice. When I run it I get a 'subscript out of range' error, have I done it wrong.
-
Re: Add spaces to a string
incalthis,
When you get the error and you select "DEBUG" which line of code is highlighted.
Is the worksheet name correct. Typically unless you've renamed the sheets "Shee 1" would normally be "Sheet1"
-
Re: Add spaces to a string
can u please tell me how to start a new thread
-
-
-
[Solved] Add spaces to a string
Barry , Norie
Thank you to both of you for your advice, the problem is now solved.
Barry your code worked and you where exactly right, I had made a spelling mistake on the Sheet 1 Tab. Good Call.
For anyone else reading this: A good way to add spaces to a string to make it a desired lenght is as follows:
-
Re: [Solved] Add spaces to a string
Hi incalthis,
Alternatively, assuming that the required length of your string doesn't change, you can just declare the string as
Any values entered into that variable will be automatically padded with spaces up to the declared length of the string.
Hope this helps.
Regards,
Batman. -
Re: Add spaces to a string
Thanks Batman,
Would the spaces be placed at the end of the characters already contained in the string? Thats what I would need to happpen
Incalthis
-
-
-
Re: Add spaces to a string
Thanks WillR,
Works well.
I am using it on a text file I have dropped into XL. Can you tell me why the code would stop working when the first two characters change from 12 - 19 in the last row.
Incalthis
-
Re: Add spaces to a string
What's your current code.. without seeing it i can't really answer your question....
-
Re: Add spaces to a string
There are 772 rows that start with 12 , row 773 starts with 19.
CodeDim TheExtract As String * 552 Dim cell As Range For Each cell In Worksheets("Sheet 1").Range("A11", Worksheets("Sheet 1").Range("A65536").End(xlUp)) TheExtract = cell.Value cell = TheExtract
Thanks
Incalthis
-
Re: Add spaces to a string
This works fine on a test dataset
CodeSub Stuff() Dim strExtract As String * 552 Dim rngCell As Range For Each rngCell In Worksheets("Sheet 1").Range("A11", _ Worksheets("Sheet 1").Range("A65536").End(xlUp)).Cells strExtract = rngCell.Value rngCell = strExtract Next End Sub
i.e. I had rows of both 12 and 19 chars, & all ended up as 552 chars in length
-
Re: Add spaces to a string
Sorry to be a pest Willr but I just cant seem to get this right.
This is the entire sub, everything works except for the llenght of the last row, it has a character length of 8 and must be made up to 552. If it was always the same cell each time I run this then I could define an exact range for it, much like I did with 'OwnerOfExtract', but its not. It will change each time due to the length of the text file inserted.Code
Display MoreDim OwnerOfExtract As String * 552 Dim strExtract As String * 552 Dim rngCell As Range 'prevent screen updates Application.ScreenUpdating = False 'Make the header the correct length OwnerOfExtract = Worksheets("Sheet 1").Range("A10").Value Worksheets("Sheet 1").Range("A10") = OwnerOfExtract 'declare range for the extract For Each rngCell In Worksheets("Sheet 1").Range("A11", _ Worksheets("Sheet 1").Range("A65536").End(xlUp)).Cells strExtract = rngCell.Value rngCell = strExtract rngCell.Offset(0, 1) = Mid(strExtract, 10, 10) rngCell.Offset(0, 5) = Right(strExtract, 3) rngCell.Offset(0, 3) = " " If Mid(strExtract, 386, 1) = "6" And Mid(strExtract, 401, 7) <> "0000000" Then rngCell.Offset(0, 3) = "Yes" End If Next rngCell
-
-
Re: Add spaces to a string
Your posts become more confusing each time...
I now have no idea of :-
what is wrong?
what you are trying to do?Maybe you need to supply us with some sample data of how it is when you start & how you want it to be when you finish... as currently the goalposts seem to keep moving
-
Re: Add spaces to a string
Sorry Willr, I will try to explain it better.
I place a text file into excel each week that has a top row of 71 characters and a bottom row of 8 characters. Most of the rows in between have 552 characters, your code now ensures all the 'between' rows are 552 long.
Top row contains the name of the file owner so I called it OwnerOfExtract, as it is always in Cell A10, I called it a seperate string and your code also makes ithis 552 long.
The bottom row contains only 8 characters and could be in any cell from A750:A1000 depending on how many rows where in the text file.
My problem is that I cant get the bottom row to register a length of anything other than 8 characters.
The rngCell(Offset) statements extract data and place them in the same row but a few cells to the right. They all work okay and can be ignored for this problem.
I hope I have explained it clearly enough, if not then dont worry about it as I dont want to bog you down with this.
Thanks
Incalthis -
Re: Add spaces to a string
Hi incalthis,
I can't see from what you have posted how is it that your final row has a string length of 8 and not 552.
Your program converts the value of cell A10 to a string length of 552, then moves on to do the same for every cell from A11 down to the last cell in column A, which presumably includes the footer record.
The code you have posted doesn't include the Sub .. End Sub statements; is there any other code you haven't posted?
Alternatively, is it possible that the footer record isn't actually in column A, so it isn't being looked at by the program?
Regards,
Batman. -
Re: Add spaces to a string
Batman,
Your assesment is correct, all cells in column A from A10 to the last one have a string length of 552 once the code is activated, as mentioned the last one only has 8.
The footer recored is in column A and there is no more code to post.
I have no idea why it doesnt work.
-
Re: Add spaces to a string
I have copied your code and run it through a program of my own and I can see no reason why it wouldn't work.
Can you try putting the following statement immediately before Next rngCell and check the results in the Immediate Pane after you have run the program:
That should tell you which cells have been actioned and what the length of each is.
Regards,
Batman. -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!