如何找到表格列，然后向下移动并替换单元格的内容，如果它是“N/A" [英] How to find table column, then move down and replace the cell's content IF it is "N/A"

问题描述

我有将近 1,800 个 Word 文档，这些文档大约有 8 页，表格中有独特的数据.我们刚刚被告知，我们为其中一些表提供的数据不准确，需要从N/A"更改为不适用".到0.0%".如不适用"在文档中使用了很多，不幸的是，我无法找到/替换该文本.

I have almost 1,800 Word documents that have about 8 pages with unique data in tables. We were just informed that the data we were given for some of those tables is inaccurate and needs to be changed from "N/A" to "0.0%". As "N/A" is used a lot in the document, I unfortunately cannot just find/replace that text.

使用此线程(宏在 Word 表格中查找单元格中的特定字符串并向左移动 x 单元格，检查 isnumeric 然后在同一列中的下 x 单元格上设置排版) 我能够调整下面的代码查找列标题(按时完成率)并移动到相邻的单元格以更新它们.但是，由于此列用于百分比，IsNumeric 代码会更改它找到的由于百分比符号而导致的任何数据.

Using this thread (Macro to find in Word table for specific string in a cell and move x cell left, check isnumeric then set typography on down x cell in the same column) I was able to adjust the code below to find the column header (On-Time Completion Rate) and move to the adjacent cells to update them. However, since this column is for percentages, the IsNumeric code is changing any data it finds due to the percentage symbol.

有没有办法做同样的事情，而不是使用 IsNumeric(因为它不适用于百分比)检查单元格中的值，如果它找到N/A"将其更改为0.0%"?然后需要对另外两个表重复此操作，其中一个表有四行要查看.

Is there a way to do the same but instead of using IsNumeric (since it does not work for percentages) check the value in the cell and if it finds "N/A" change it to "0.0%"? This would then need to be repeated for two more tables, with one table have four rows to look through.

预先感谢您提供的任何帮助！

Thank you in advance for any help you can offer!

表格截图

Sub Demo()
Application.ScreenUpdating = False
Dim r As Long, c As Long
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "On-time Completion Rate" 'Column Header'
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchWildcards = True
    .Execute
  End With
  Do While .Find.Found
    If .Information(wdWithInTable) = True Then
      r = .Cells(1).RowIndex
      c = .Cells(1).ColumnIndex
      With .Tables(1)
             If Not IsNumeric(Split(.Cell(r + 1, c).Range.Text, vbCr)(0)) Then .Cell(r + 1, c).Range.Text = "0.0%"
        If Not IsNumeric(Split(.Cell(r + 2, c).Range.Text, vbCr)(0)) Then .Cell(r + 2, c).Range.Text = "0.0%"
      End With
    End If
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub

推荐答案

试试这个:

Sub Demo()
   Application.ScreenUpdating = False
   Dim r As Long, c As Long
   With ActiveDocument.Range
      With .Find
         .ClearFormatting
         .Replacement.ClearFormatting
         .Text = "On-time Completion Rate" 'Column Header'
         .Replacement.Text = ""
         .Forward = True
         .Wrap = wdFindStop
         .Format = False
         .MatchWildcards = True
         .Execute
      End With
      Do While .Find.Found
         If .Information(wdWithInTable) = True Then
            r = .Cells(1).RowIndex
            c = .Cells(1).ColumnIndex
            With .Tables(1)
               If Split(.Cell(r + 1, c).Range.Text, vbCr)(0) = "N/A" Then .Cell(r + 1, c).Range.Text = "0.0%"
               If Split(.Cell(r + 2, c).Range.Text, vbCr)(0) = "N/A" Then .Cell(r + 2, c).Range.Text = "0.0%"
            End With
         End If
         .Collapse wdCollapseEnd
         .Find.Execute
      Loop
   End With
   Application.ScreenUpdating = True
End Sub

这篇关于如何找到表格列，然后向下移动并替换单元格的内容，如果它是“N/A"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！