formula in VBA

[James]

I have this code that is written that will put my leading 0's back in when
necessary, but I am having a hard time implementing it in my code. below is
the following code:

Sub Convert_LineName

Dim LineName_Row As Range
Dim LineName_final_row As Range
Dim LineName As Range
Dim Converted_LineName As Range
Dim myLineName As Integer


Set LineName = Application.InputBox("Please select the first cell in" & _
" the column with LineName", Type:=8)
Set Converted_LineName = Application.InputBox("Please select the first
cell " & _
" where you want your converted LineName to start ", Type:=8)

myLineName = LineName.Columns.Count

On Error Resume Next
Converted_LineName.EntireColumn.Insert Shift:=xlToRight
Set Converted_LineName = Converted_LineName.Offset(, -1)

FirstRow = LineName.Row
FinalRow = Cells(65536, LineName.Column).End(xlUp).Row

'I think this is my problem
Range(Converted_LineName,
Converted_LineName.Offset(LineName_final_row - LineName_Row)).Formula = _
ActiveCell.FormulaR1C1 = "=IF(LEN(RC[-1])=3,""0""&RC[-1],RC[-1])"

If MsgBox("Do you want to convert to values?", vbYesNo) = vbNo Then Exit Sub

Columns(Converted_LineName.Column).Copy
Columns(Converted_LineName.Column).PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

The macro is allowing me to select the cell where my "LineName" is and than
it allows me to select where I want my results to go to, but having to put in
the formula is a bit more difficult for me to do. The formula that I am
inputting is as follows:

=IF(LEN(C1)=3,"0"&C1,C1)

However, I want the C1 to be what I selected as my LineName.

Thanks

 

[macropod]

Hi James,

Why don't you simply define a cutom format for the cells, with three 0s (ie Format|Cells|Number > Custom > '000')? That will
automatically pad the displayed number out with as many 0s as might be required.

Trying to add in a leading 0 via vba is futile unless you convert the number to text. Plus, your code only inserts one '0', where
two 0s might be required.