Formules zichtbaar maken.

[jpvs]

Een macro gevonden om alle formules onder elkaar te zetten in een nieuw blad samen met de cel nummer.

Sub ListFormulas()
    
     'ChemistB
   [B] 'And here's one that Inserts a worksheet and lists all the formulas
    'off the active sheet and what cells they are in[/B]
    
    Dim FormulaCells As Range, Cell As Range
    Dim FormulaSheet As Worksheet
    Dim Row As Integer
    
'   Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
    
'   Exit if no formulas are found
    If FormulaCells Is Nothing Then
        MsgBox "No Formulas."
        Exit Sub
    End If
    
'   Add a new worksheet
    Application.ScreenUpdating = False
    Set FormulaSheet = ActiveWorkbook.Worksheets.Add
    FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name
    
'   Set up the column headings
    With FormulaSheet
        Range("A1") = "Address"
        Range("B1") = "Formula"
        Range("A1:B1").Font.Bold = True
    End With
    
'   Process each formula
    Row = 2
    For Each Cell In FormulaCells
        Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
        With FormulaSheet
            Cells(Row, 1) = Cell.Address _
                (RowAbsolute:=False, ColumnAbsolute:=False)
            Cells(Row, 2) = " " & Cell.Formula
            Row = Row + 1
        End With
    Next Cell
    
'   Adjust column widths
    FormulaSheet.Columns("A:B").AutoFit
    Application.StatusBar = False
End Sub

Maar de formules worden weergegeven in het Engels?

Bv. in cel D14 staat:
=ALS(ISFOUT(VERT.ZOEKEN(D13;Klanten1;2;ONWAAR));"";VERT.ZOEKEN(D13;Klanten1;2;ONWAAR))

de macro geeft dit weer:
=IF(ISERROR(VLOOKUP(D13,Klanten1,2,FALSE)),"",VLOOKUP(D13,Klanten1,2,FALSE))

Is dit te veranderen in het Nederlands ?

Pierre

 

[Wigi]

Cells(Row, 2) = " " & Cell.FormulaLocal

ipv.

Cells(Row, 2) = " " & Cell.Formula

 

[jpvs]

Bedankt voor het juiste antwoord.

Pierre
De juiste macro:

Sub ListFormulas()
    
     'ChemistB
    'And here's one that Inserts a worksheet and lists all the formulas
    'off the active sheet and what cells they are in
    
    Dim FormulaCells As Range, Cell As Range
    Dim FormulaSheet As Worksheet
    Dim Row As Integer
    
'   Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
    
'   Exit if no formulas are found
    If FormulaCells Is Nothing Then
        MsgBox "No Formulas."
        Exit Sub
    End If
    
'   Add a new worksheet
    Application.ScreenUpdating = False
    Set FormulaSheet = ActiveWorkbook.Worksheets.Add
    FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name
    
'   Set up the column headings
    With FormulaSheet
        Range("A1") = "Address"
        Range("B1") = "Formula"
        Range("A1:B1").Font.Bold = True
    End With
    
'   Process each formula
    Row = 2
    For Each Cell In FormulaCells
        Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
        With FormulaSheet
            Cells(Row, 1) = Cell.Address _
                (RowAbsolute:=False, ColumnAbsolute:=False)
            Cells(Row, 2) = " " & Cell.FormulaLocal
            Row = Row + 1
        End With
    Next Cell
    
'   Adjust column widths
    FormulaSheet.Columns("A:B").AutoFit
    Application.StatusBar = False
End Sub