I am writing a macro in Excel2003 to find all cells with formulas in a workbook and outputting their address and formula in a couple of columns on a different sheet.
我在Excel2003中编写一个宏来查找工作簿中包含公式的所有单元格,并在不同工作表的几列中输出它们的地址和公式。
I know I can show the formula for an individual cell using
我知道我可以使用单个单元格显示公式
Public Function ShowFormula(cell As Range) As String
ShowFormula = cell.Formula
End Function
which works just fine, but since I didn't want to have to find all the cells by hand, I wrote the following macro to find them all for me
工作得很好,但由于我不想手工找到所有的单元格,我写了下面的宏来找到它们对我来说
Sub Macro2()
Dim i As Integer
Dim targetCells As Range
Dim cell As Range
Dim referenceRange As Range
Dim thisSheet As Worksheet
Set referenceRange = ActiveSheet.Range("CA1")
With referenceRange
For Each thisSheet In ThisWorkbook.Sheets
If thisSheet.Index >= referenceRange.Parent.Index Then
Set targetCells = thisSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
For Each cell In targetCells
If cell.HasFormula Then
.Offset(i, 0).Value = thisSheet.Name
.Offset(i, 1).Value = cell.Address
.Offset(i, 2).Value = CStr(cell.Formula)
i = i + 1
End If
Next
End If
Next
End With
End Sub
It finds all the cells just fine, but instead of displaying the formula as text, the list displays the formula results.
它可以很好地找到所有单元格,但不会将公式显示为文本,列表会显示公式结果。
What am I missing to output the formulas as text instead of formulas?
将公式输出为文本而不是公式,我缺少什么?
1 个解决方案
#1
5
Try this:
尝试这个:
.Offset(i, 2).Value = "'" & CStr(cell.Formula)
Also, this will make things a bit quicker. Instead of
此外,这将使事情变得更快。代替
For Each thisSheet In ThisWorkbook.Sheets
If thisSheet.Index >= referenceRange.Parent.Index Then
try
尝试
For j = referenceRange.Parent.Index to Sheets.Count
Set thisSheet = Sheets(j)
#1
5
Try this:
尝试这个:
.Offset(i, 2).Value = "'" & CStr(cell.Formula)
Also, this will make things a bit quicker. Instead of
此外,这将使事情变得更快。代替
For Each thisSheet In ThisWorkbook.Sheets
If thisSheet.Index >= referenceRange.Parent.Index Then
try
尝试
For j = referenceRange.Parent.Index to Sheets.Count
Set thisSheet = Sheets(j)