如何计算单元格中的整数并在VBA中进行排序？

Question

我有一个列有不同的数值。一些具有格式：

0.56 
1.52 

，部分格式，如：

6,352.00 

要做到在Excel计算我想替代"," with ""当数格式是这样6,352.00和所有其他替代"." with ",".

所以结果我应该得到：

0,56 
1,52 
6352,00 

然后将它们从最大值分类到最小值。

我试图写一个VBA代码（这不是真的正确），也许有人可以帮助吗？

Sub ChangeFormat1() 
    Dim lngNumberOfCharacters As Long 
    Set ws4 = ActiveWorkbook.Sheets("atm_hh") 

    ws4.Select 
    Columns("C:C").Select 

lngNumberOfCharacters = Len("C:C") 

    If lngNumberOfCharacters > 8 
    Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _ 
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
     ReplaceFormat:=False 
     Else 
      Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _ 
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
     ReplaceFormat:=False 
     End If 

    ActiveWorkbook.Worksheets("atm_hh").Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("atm_hh").Sort.SortFields.Add Key:=Range("C1"), _ 
     SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal 
    With ActiveWorkbook.Worksheets("atm_hh").Sort 
     .SetRange Range("A2:D66842") 
     .Header = xlNo 
     .MatchCase = False 
     .Orientation = xlTopToBottom 
     .SortMethod = xlPinYin 
     .Apply 
    End With 

     End Sub 

Answer 1

如果你真的想用VBA工作

Sub ChangeFormat1() 

Application.ScreenUpdating = False 


Dim ws4 As Worksheet 
Set ws4 = Sheets("atm_hh") 

' If you only want to replace in column A for example 
With ws4.Columns("A:A") 

    .Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _ 
      MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 

    .Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlByRows, _ 
      MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 

End With 

Application.ScreenUpdating = True 

End Sub 

[Excel公式中删除从字符串中的字符，然后替换相同的字符被删除另一个字符]的