VBA用点替换连字符后的十进制问题

Question

Sub BatchCopyTextNewColumn()
    Dim LastRow As Integer
'Variables declared as per Solution from http://stackoverflow.com/questions/24967189    /vba-'decimal-issue-after-replacing-a-hyphen-with-dot
'by Jagadish Dabbiru (July 25, 2014)
    Dim i As Integer
    Dim BatchNo() As String
    Dim ArrBatchNo As Long
    Worksheets("DataFile").Activate
    Cells(1, 17).Select
'Rename Columns
    Cells(1, 17).Value = "BatchNumeric"
    Cells(1, 18).Select
'Rename Columns
    Cells(1, 18).Value = "BatchCheck_Old_New"
'Ask user to provide you with the Batch# from the last data set e.g. which was received a week ago.
'Last Row is defined by the Column 1 "AccountString"
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("O2", Range("O1").End(xlDown))
        .Copy
    End With
'Use the PasteSpecial method:
    Range("Q2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
'Solution from http://stackoverflow.com/questions/24967189/vba-decimal-issue-after-'replacing-a-hyphen-with-dot
'by Jagadish Dabbiru (July 25, 2014)
'Take each value from column q and Split the batch code with delimiter hyphen(-).
'Then check the length of the value of 2nd array element.
'If length is 1 digit then add 0 as prefix else keep as it is.
    i = 1
    Do
    BatchNo = Worksheets("DataFile").Range("Q" & i).Value
    ArrBatchNo = Split(BatchNo, "-")
    If Len(ArrBatchNo(1)) = 1 Then
    ArrBatchNo = "0" & ArrBatchNo
    End If
    Worksheets("DataFile").Range("Q" & i).Value = ArrBatchNo(0) & "." & ArrBatchNo(1)
    i = i + 1
    Loop While Worksheets("DataFile").Range("Q" & i).Value <> ""
    Range("Q2").Activate
End Sub

用点替换连字符后出现小数问题。

最初批号是用连字符发出的。我需要使用它们作为值，因为我必须区分"旧"批号(以前发送的)和"新"(大于上一个批号)。

连字符替换(我的代码):

Sub ReplaceBatchCharacter()
Range("Q2").Activate
Worksheets("DataFile").Columns("Q").Replace _
'I tried to replace it with "." and there is a problem with decimals as well (e.g. batch # 4230-1 and 4230-10 are both shown as 4210.10 after replacement
    What:="-", Replacement:=".0", _
'by replacing with ".0" I have solution for batch #'s xxxx-1/2/3/4/5/6/7/8/9 but when it starts with double digits xxxx-10 etc I have similar problem as they become xxxx.010 etc.
So the batch number 4230-1 and 4230-10 are both shown as 4210.010 after replacement.
    SearchOrder:=xlByColumns, MatchCase:=True
End Sub
Check Old and New Batch # (my code):
Sub BatchNumberCheckNewOld()
Dim BatchNumberPrevious As Single
Dim BatchNumberCurrentCell As Range
Dim BatchNumberCurrentRange As Range
Dim BatchNumberCurrentResult As String
Dim LastRow As Long
'Dim ChangedRange As Range
Worksheets("DataFile").Activate
Range("Q2").Activate
LastRow = Cells(Rows.Count, 17).End(xlUp).Row
'Ask user to provide us with the Batch# from the last data set e.g. which was received a week ago.
'Last Row is defined by the Column 1 "AccountString"
'BatchNumberPrevious = 4250.1
BatchNumberPrevious = InputBox("Please enter the last Batch # from previous period. Don't forget to reconcile TB's (prior and current months). Ocassionally OLD batches could be NEW.")
Set BatchNumberCurrentRange = Range(Cells(2, 17), Cells(LastRow, 17))
For Each BatchNumberCurrentCell In BatchNumberCurrentRange
If BatchNumberPrevious >= BatchNumberCurrentCell.Value Then
    BatchNumberCurrentResult = "Old"
Else
    BatchNumberCurrentResult = "New"
End If
'BatchNumberCurrentResult column should be populated by offsetting BatchNumberCurrentCell variable
BatchNumberCurrentCell.Offset(0, 1).Value = BatchNumberCurrentResult
Next BatchNumberCurrentCell
'Autofit width of columns
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Answer 1

取列q中的每个值并用分隔符连字符(-)分隔批处理代码。然后检查第二个数组元素值的长度。如果长度为1位，则添加0作为前缀，否则保持不变。

i =1
Do 
BatchNo = Worksheets("DataFile").Range("Q"& i).Value 
ArrBatchNo = Split(BatchNo,"-")
If Len(ArrBatchNo(1)) = 1 Then
ArrBatchNo(1) = "0" & ArrBatchNo (1)
End If
Worksheets("DataFile").Range("Q"& i).Value = ArrBatchNo(0) & "." & ArrBatchNo(1)
i = i+1
Loop While Worksheets("DataFile").Range("Q"& i).Value <> ""