excel - VBA Excel 在字符串的第二个字符后插入符号

Question

我有一组坐标，我想对其进行划分。我想有正确的小数数字，这在我的工作表中不会发生，因为我得到了困惑的数据。


第一个图像显示了初始坐标标签。第二个显示分割后的坐标。
我这里需要带小数的数字。
我试图将它们除以数字，但它没有用。

    Sub Coordinatesfinal()
    Columns("F:G").Insert Shift:=xlToRight

    ActiveSheet.Range("E1").Value = "Latitude"
    ActiveSheet.Range("F1").Value = "Longitude"

    Dim rang As Range, cell As Range, rg As Range, element As Range, rg2 As Range
    Dim r1 As Range, r2 As Range
    Dim wors As Worksheet
    Set wors = ActiveSheet
    Dim myString As String
    myString = "."

    Dim LastRow As Long, i As Long, SecondLastRow As Long

    LastRow = wors.Range("E" & wors.Rows.Count).End(xlUp).Row
    Set rang = wors.Range("E2:E" & LastRow)
    For Each cell In rang
    cell = WorksheetFunction.Substitute(cell, ",", " ")
    cell = WorksheetFunction.Substitute(cell, "  ", " ")
    cell = WorksheetFunction.Substitute(cell, ",,", " ")
    Next

    Set rg = [E2]
    Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))


     rg.TextToColumns Destination:=rg, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
    
    If InStr(myString, ".") > 0 Then
    Exit Sub
    End If

    With words
    LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
    SecondLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
    End With

    For Each element In wors.Range("E2:E" & LastRow)
    cell.Value = cell.Value / 1000000
   Next

    For i = 2 To LastRow
    Set r1 = Range("E" & i)
   Set r2 = Range("F" & i)
    If r1.Value > 54.5 Or r1.Value < 50 Then r1.Interior.Color = vbYellow
    If r2.Value > 2 Or r2.Value < -7 Then r2.Interior.Color = vbCyan
   'If r1.Value = 3 Then r2.Interior.Color = vbYellow
    Next i

     rg.TextToColumns Destination:=rg, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

    MsgBox ("Coordinates prepared successfully")

    End Sub

这

    For Each element In wors.Range("E2:E" & LastRow)
     cell.Value = cell.Value / 1000000
     Next

VBA: Macro to divide range by a million
根本不起作用，就像:

   For Each element In wors.Range("F2:F" & SecondLastRow)
    If IsNumeric(element.Value) Then
    If Len(element.Value) > 7 And Len(element.Value) < 9 Then
    element.Value = element.Value / 1000000
    ElseIf Len(element.Value) < 8 Then
    element.Value = element.Value / 100000
    Else
    element.Value = element.Value / 10000000
    End If
    End If
  Next

我不知道问题可能出在哪里。由于根据我的总字符串长度我可能有几种情况，我想问一下插入“。”的一些可能性。我的字符串中第二个字符之后的符号。
我测试了这个功能:
Excel/VBA - How to insert a character in a string every N characters
但没有任何结果。
有什么方法可以划分这些数字或简单地插入“。”第二个数字后的符号？
这是我想要的输出

Answer 1

像这样的东西应该工作:

Sub Coordinatesfinal()
    
    Dim ws As Worksheet, rngData As Range, arrIn, arrOut, r As Long, d, arr
    
    Set ws = ActiveSheet
    Set rngData = ws.Range("E2", ws.Cells(Rows.Count, "E").End(xlUp))
    arrIn = rngData.Value  'get input data as array
    
    ReDim arrOut(1 To UBound(arrIn, 1), 1 To 2) 'size array for output data
    
    'clean raw value
    For r = 1 To UBound(arrIn, 1)
        d = Trim(Replace(arrIn(r, 1), ",", " ")) 'remove commas
        Do While InStr(d, "  ") > 0
            d = Replace(d, "  ", " ") 'remove any double spaces
        Loop
        
        arr = Split(d, " ") 'split on space
        arrOut(r, 1) = FormatValue(arr(0))                            'Lat
        If UBound(arr) > 0 Then arrOut(r, 2) = FormatValue(arr(1))    'Long
    Next r
    
    ws.Columns("F:G").Insert Shift:=xlToRight
    ws.Range("F1:G1").Value = Array("Latitude", "Longitude")
    With ws.Range("F2").Resize(UBound(arrIn, 1), 2)
        .NumberFormat = "General"
        .Value = arrOut
    End With

End Sub

'convert to decimal if numeric, according to length
Function FormatValue(ByVal v)
    If IsNumeric(v) And InStr(v, ".") = 0 Then
        v = CLng(v)
        Select Case Len(v)
            Case 8: FormatValue = v / 1000000
            Case Is < 8: FormatValue = v / 100000
            Case Else: FormatValue = v / 10000000
        End Select
    Else
        FormatValue = v
    End If
End Function