时间戳

首页 > 解决方案 > 如何使用 VBA 仅对包含数据的某些单元格进行数字格式化

vba - 如何使用 VBA 仅对包含数据的某些单元格进行数字格式化

问题描述

以下代码从 csv 文件中读取行并重新格式化行标题。我希望我能清楚地表达这一点，但我想在vct_FEMAP_Results()函数返回值的标题下方的行中将单元格格式化为两位小数。

例子： 

   ID    "CSys ID"   "Set ID"     Plate Top VM Stress     Plate Bot VM Stress  
 ------ ----------- ---------- ----------------------- ---------------------- 
  4591         0         20              229.9488               244.8103  
  4592         0         20              323.5026               315.1129

我正在尝试格式化包含小数的单元格而不影响列标题 、 或 中ID的CSys ID数据Set ID。下面的代码将所有列格式化为2 位小数。不知道为什么。

Sub cmdOpen_Click()
    Dim wrdArray() As String, txtstrm As TextStream, line As String
    Dim wrd As Variant, myWrd As String
    Dim col As Long, colCount As Long
    Dim count As Long
    Dim row As Long, temp As Long

    Dim str As String, regex As RegExp
    Dim matches As MatchCollection, lineMatch As match, wrdMatch As match
    Dim i As Long, j As Long, x As Long
    Dim strPath As String, strLine As String

    Set regex = New RegExp
    regex.Pattern = "\d+"
    regex.Global = True

    'Remove Filters and Add Custom Filters
    Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
    Call Application.FileDialog(msoFileDialogOpen).Filters.Add("Text Files", "*.txt")
    Call Application.FileDialog(msoFileDialogOpen).Filters.Add("Dat Files", "*.dat")
    Call Application.FileDialog(msoFileDialogOpen).Filters.Add("Comma Delimited Files", "*.csv")

    'only allow the user to select one file
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False

    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show

    'determine what choice the user made
    If intChoice <> 0 Then

    'get the file path selected by the user
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

    End If
'------------------------------------------------------------

     If strPath <> "" Then
        Set txtstrm = FSO.OpenTextFile(strPath)
    Else
        Exit Sub
    End If

    row = 1
    Do Until txtstrm.AtEndOfStream
      line = txtstrm.ReadLine

      x = 1
      col = 1
      count = 0

      wrdArray() = Split(line, ",")

      For Each wrd In wrdArray()
        count = count + 1
        myWrd = wrd

        ActiveSheet.Cells(row, col) = wrd
        col = col + 1
      Next wrd

      If (row = 1) Then
        For i = 0 To count - 1
            Set matches = regex.Execute(wrdArray(i))
            For Each wrdMatch In matches
                If wrdMatch Then
                    ActiveSheet.Cells(1, i + 1) = vct_FEMAP_Results(wrdMatch.Value)
                    x = x + 1
                End If
            Next
        Next i
      End If

    row = row + 1
    Loop

    txtstrm.Close

    For i = 1 To row - 1
        For j = x To col - 1
            ActiveSheet.Cells(i, j).NumberFormat = "0.00"
        Next j
    Next i

End Sub

标签： vbaexcel

解决方案

您的代码正在格式化所有列，因为您正在使用以下位循环列：

For i = 1 To row - 1
    For j = x To col - 1
        ActiveSheet.Cells(i, j).NumberFormat = "0.00"
    Next j
Next i

如果您已经知道需要格式化哪些列，只需这样做：

ActiveSheet.Range("d:d, e:e").EntireColumn.NumberFormat = "0.00"

根据您的示例数据，这将仅重新格式化 D 列和 E 列。如果您需要其他列，请更改 d 和 e。

我实际上更喜欢避免使用“ActiveSheet”并始终明确引用特定的工作表，因此我始终确定我的代码所针对的工作表。然而，当您使用 ActiveSheet（或单元格或工作簿）时，活动工作表可能会发生变化，有时会以意想不到的方式发生变化。

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("d:d, e:e").EntireColumn.NumberFormat = "0.00"

我希望这会有所帮助！

推荐阅读