Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: number to string

    Thanks in advance!
    I am attempting to write a VBA routine that takes the Left and Right from and in between insert a value that happens to be numeric. It needs to be 3 characters long with leading 0 (zero's); one or two depending on the length of the number (001, 002, 010, 011, etc.). I had hoped to have the user insert the numeric value in a prompt and then have the job loop through the column and complete the insert. The issue I am having is that no matter what I have done to try to convert the variable to string, the leading 0's are dropped. I have tried

    Dim LineNum as string

    LineNum = Str$(Right("00000" & CStr(LineNum), 3)) (type mismatch
    LineNum = Str$(Right(00000 & CStr(LineNum), 3)) outputs the interger value with no leading 0's.

    I have tried inputting 001 in the like so
    LineNum = Str$(InputBox("Enter line number"))
    then looping with
    Range("H" & rowHdr).Formula = "=Left(B" & rowHdr & ",13)& " & CStr(LineNum) & "& Right(B" & rowHdr & ",4)"

    Is there a way to get the leading 0's in the formula?
    Thanks,
    Lee

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by clinel
    Thanks in advance!
    I am attempting to write a VBA routine that takes the Left and Right from and in between insert a value that happens to be numeric. It needs to be 3 characters long with leading 0 (zero's); one or two depending on the length of the number (001, 002, 010, 011, etc.). I had hoped to have the user insert the numeric value in a prompt and then have the job loop through the column and complete the insert. The issue I am having is that no matter what I have done to try to convert the variable to string, the leading 0's are dropped. I have tried

    Dim LineNum as string

    LineNum = Str$(Right("00000" & CStr(LineNum), 3)) (type mismatch
    LineNum = Str$(Right(00000 & CStr(LineNum), 3)) outputs the interger value with no leading 0's.

    I have tried inputting 001 in the like so
    LineNum = Str$(InputBox("Enter line number"))
    then looping with
    Range("H" & rowHdr).Formula = "=Left(B" & rowHdr & ",13) & " & CStr(LineNum) & "& Right(B" & rowHdr & ",4)"

    Is there a way to get the leading 0's in the formula?
    Thanks,
    Lee
    Hi

    I am quite confused as to what you are trying to do, but have tried this

    Range("H" & rowHdr).Formula = "=Left(B" & rowHdr & ",13)& " & Format(LineNum,000) & "& Right(B" & rowHdr & ",4)"

    Just a thought!


    MTB

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Thanks for the reply!

    I have a column that represents pieces of equiment with a standard format that is structured like ABCD-ABC-DEF-001-XYZ. Only the first 4 characters are constant. Characters 5 - 10 are specfic types of equipment and the numberic value represents a specfic manchine number. I have a core group for line 1. I had hoped to create vba that allowed me to give a beginning and ending machine number and have it copy and insert the correct line number. I think I have the loop created, I just need to insert the leading 0's for the other machines (-002-, -003-, -010, etc). I did try you suggestion and got the same results -2-, -3-, -4-, etc. I tried it 2 ways Format(lineNum, 000) and Format(lineNum,"000").

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by clinel
    Thanks for the reply!

    I have a column that represents pieces of equiment with a standard format that is structured like ABCD-ABC-DEF-001-XYZ. Only the first 4 characters are constant. Characters 5 - 10 are specfic types of equipment and the numberic value represents a specfic manchine number. I have a core group for line 1. I had hoped to create vba that allowed me to give a beginning and ending machine number and have it copy and insert the correct line number. I think I have the loop created, I just need to insert the leading 0's for the other machines (-002-, -003-, -010, etc). I did try you suggestion and got the same results -2-, -3-, -4-, etc. I tried it 2 ways Format(lineNum, 000) and Format(lineNum,"000").
    Hi again

    Not sure why it dosn't work but this code
    Code:
    Sub test()
        Dim variable As String
        variable = "2"
        Cells(2, 2) = "Prefix" & Format(variable, " -000- ") & "Sufix"
    End Sub
    produces this in Cell B2

    Prefix -002- Sufix

    Try it and see if does the same on your PC

    Maybe I'm missing something !!

    MTB

  5. #5
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Thanks so much! I am not sure why it did not work the first time either, but it is now and that is all that counts! Thannks again!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •