title: VBA Decimal issue after replacing a hyphen with dot
body: <pre><code>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" &amp; i).Value
      ArrBatchNo = Split(BatchNo, "-")
      If Len(ArrBatchNo(1)) = 1 Then
      ArrBatchNo = "0" &amp; ArrBatchNo
      End If
      Worksheets("DataFile").Range("Q" &amp; i).Value = ArrBatchNo(0) &amp; "." &amp; ArrBatchNo(1)
      i = i + 1
      Loop While Worksheets("DataFile").Range("Q" &amp; i).Value &lt;&gt; ""
  
      Range("Q2").Activate
  
  End Sub
  </code></pre>
  
  <p>There is a problem with decimals after replacing hyphens with dots.</p>
  
  <p>Originally the Batch numbers are issued with hyphens. I need to use them as values as I have to distinguish between "OLD" Batch numbers (sent previously) and "NEW" (larger than the last Batch number).</p>
  
  <p>Hyphen replacement (my code):</p>
  
  <pre><code>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 &gt;= 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
  </code></pre>