LinkBack URL
About LinkBacks
Hi
How can i fix the character lengths for following columns. I am not sure if there is a way to fix the length or will it be a case of vb edit deleting characters.
COLUMN CHARACTERS
A 2
B 16
C 6
D 6
E 42
F 14
G 9
H 14
I 6
J 1
K 2
Thanks
regards
Hi
How can i fix the character lengths for following columns. I am not sure if there is a way to fix the length or will it be a case of vb edit deleting characters.
COLUMN CHARACTERS
A 2
B 16
C 6
D 6
E 42
F 14
G 9
H 14
I 6
J 1
K 2
Thanks
regards
Last edited by tek9step; 04-19-2010 at 09:52 AM.
Hi
What do you mean by fixing the character length? Could you elaborate and show what it would look like after fixing the character length
Abousetta
What i mean is when information entered on the spread sheet it should be of a fix length. for example. Column A cell A2 one should only be able to enter 2 characters or numbers.
Hope this make more sense.. if not jus give a shout back
regards
You could apply data validation to each column specifying max len.
If the cells are already populated then code or formula/copy/paste would be required.
Hi Andy tried Data Validation it make sense to use for future thanks.. As i have some historical files which already has data i think a code would be needed ??
COLUMN CHARACTERS Type
A 2 Text
B 16 General
C 6 General
D 6 (ddmmyy)
E 42 General
F 14 Number
G 9 Number
H 14 Number
I 6 Number
J 1 General
K 2 Text
regards
Something like this to simply truncate the cells contents.
It would need to be a lot more complex if checking content and type.
Please Login or Register to view this content.
And what do you do with the data if the test fails?
Hi Andy If the test fail will have to go to those particular highlighted cells and manually change them.
Not really that robust but you should get an idea.
Not sure what you mean by General and Text.Please Login or Register to view this content.
Also a Number with a length of 14 is vauge. Does that include a decimal place? What about negative values, does the minus sign get included in length?
Sorry Lengths for numbers is inclusive of 2 decimal negative & point.
By Text i mean general
Thanks
With numbers is the value in the cell or what is displayed.
So treat General as Text which means you only need to check length. But again you need to decide whether it's cell value or displayed value.
What about formula?
See how complex just defining the problem is.
So why the need to restrict the data on the sheet? You passing the information to another system.
Hi Andy yess i can see it getting complicated... but im learning the thought process... the information is passed on to me.. so making mine n others life easy before the file comes to me tangled.. which is then handed to IT untangled for test uploading...........
With numbers there can be formulas in the cells but only want displayed values (so something like paste special value)
On general it is the displayed value which again im more interested in (so something like paste special value only)
thanks
Last edited by tek9step; 04-19-2010 at 09:24 AM.
Then use the .Text property of the cell and simply check for length.
Please Login or Register to view this content.
Hi Andy slight problemo on the 4th column some dates are shown as 19/04/2010 and 19-04-2010 (result wanted 190410)Please Login or Register to view this content.
thanks for teaching
If they are true dates then you can change the NumberFormat for that column
Please Login or Register to view this content.
Brilliant works like a jet !!! Thanks Andy
Hi Andy slight issue what if i want to run the macro for rows 4 and below as there were some headers... ???
thanks
regards
Please Login or Register to view this content.
oops got an error trying to follow this logic below
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by tek9step; 04-19-2010 at 11:35 AM.
Please Login or Register to view this content.
hi andy where are we specifying the column in the code below as done in previous examples [ UsedRange.Columns(1). ]
Please Login or Register to view this content.
4 = Column D
The cells property has 2 arguments, .Cells( Rownumber , columnnumber )
Re: Fix Column Character Length
got it
Hi Andy Getting Slight problem
Getting bad cell in column F values even though they are within the range and column G format(4 decimal points) is changing not sure why.??Please Login or Register to view this content.
regards
Last edited by tek9step; 04-22-2010 at 09:04 AM. Reason: Found the mistake i had made
currently the test for column F is numeric with a length of 14.
looks like the column contains these values, none of which is 14 characters in size.Please Login or Register to view this content.
10.90
10.90
13.63
21.80
Hi Andy it should not be more than 14 but can be less (<=14).. so is for rest the columns that is the maximum limit stated in the comments..
How about G Column it changed its format not sure why.. it was 1.0000
Thanks
Last edited by tek9step; 04-22-2010 at 11:39 AM.
Then test for a length greater than 14 only
The code changes column G, which is a date to numeric formatPlease Login or Register to view this content.
maybe you meantPlease Login or Register to view this content.
Please Login or Register to view this content.
Tested it again rest i think is fixed apart from G column which is numeric number with 4 decimals..(not date that is column D).. I have attached updated file to show the problem with G column.
Thanks..Please Login or Register to view this content.
then comment out the code which changes the number format of column G
D is column 4Please Login or Register to view this content.
G is column 7
ahhh sugr i see the problem i didnt change the column.. thanks a ton
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks