number to string

clinel · #1 (**permalink**) 10-13-09, 15:11

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

MikeTheBike · #2 (**permalink**) 10-14-09, 09:22

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

clinel · #3 (**permalink**) 10-14-09, 18:04

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").

MikeTheBike · #4 (**permalink**) 10-15-09, 04:35

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

clinel · #5 (**permalink**) 10-15-09, 08:01

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!!

Internet Services

Web Development

Digital Marketing

Consumer Tech