Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula in VBA
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula in VBA
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. -- Cheers macropod [MVP - Microsoft Word] "James" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|