Extract formulas of a workbook

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
347
Office Version
  1. 2003 or older
Platform
  1. Windows
I have a reference code that extracts formulas. The issue is that it cannot handle merged cells with formulas.

If in a workbook if I have A1:B1, A2:B2, A3:B3 merged and A5, A6, A7 as-is, and if each of these cells have the formula: =RANDBETWEEN(0,9) then the output I get is:

VBA Express Forum - Re: List Formulas
FormulaSheet NameCell Address
RANDBETWEEN(0,9)Sheet1A1
RANDBETWEEN(0,9)Sheet1B1
RANDBETWEEN(0,9)Sheet1A2
RANDBETWEEN(0,9)Sheet1B2
RANDBETWEEN(0,9)Sheet1A3
RANDBETWEEN(0,9)Sheet1B3
Sheet1A5
Sheet1A6
Sheet1A7

Desired is:

VBA Express Forum - Re: List Formulas
FormulaSheet NameCell Address
RANDBETWEEN(0,9)Sheet1A1
RANDBETWEEN(0,9)Sheet1A2
RANDBETWEEN(0,9)Sheet1A3
RANDBETWEEN(0,9)Sheet1A5
RANDBETWEEN(0,9)Sheet1A6
RANDBETWEEN(0,9)Sheet1A7

how to make the required modification?

VBA Code:
Sub ListAllFormulas()

    Dim sht As Worksheet
    Dim shtName
    Dim myRng As Range
    Dim newRng As Range
    Dim c As Range

ReTry:
    shtName = Application.InputBox("Choose a name for the new sheet to list all formulas.", "New Sheet Name")    'the user decides the new sheet name
    If shtName = False Then Exit Sub                    'exit if user clicks Cancel

    On Error Resume Next
    Set sht = Sheets(shtName)                               'check if the sheet exists
    If Not sht Is Nothing Then                              'if so, send message and return to input box
        MsgBox "This sheet already exists"
        Err.Clear                                           'clear error
        Set sht = Nothing                                   'reset sht for next test
        GoTo ReTry                                          'loop to input box
    End If

    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)    'adds a new sheet at the end
    Application.ScreenUpdating = False
    With ActiveSheet                                        'the new sheet is automatically the activesheet
        .Range("A1").Value = "Formula"                      'puts a heading in cell A1
        .Range("B1").Value = "Sheet Name"                   'puts a heading in cell B1
        .Range("C1").Value = "Cell Address"                 'puts a heading in cell C1
        .Name = shtName                                     'names the new sheet from InputBox
    End With

    For Each sht In ActiveWorkbook.Worksheets               'loop through the sheets in the workbook
        If sht.Name <> shtName Then                         'exclude the sheet just created
            Set myRng = sht.UsedRange                           'limit the search to the UsedRange
            On Error Resume Next                                'in case there are no formulas
            Set newRng = myRng.SpecialCells(xlCellTypeFormulas)    'use SpecialCells to reduce looping further
            For Each c In newRng                        'loop through the SpecialCells only
                Sheets(shtName).Range("A65536").End(xlUp).Offset(1, 0).Value = Mid(c.Formula, 2, (Len(c.Formula)))
                'places the formula minus the '=' sign in column A
                Sheets(shtName).Range("B65536").End(xlUp).Offset(1, 0).Value = sht.Name
                'places the sheet name containing the formula in column B
                Sheets(shtName).Range("C65536").End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.Substitute(c.Address, "$", "")
                'places the cell address, minus the "$" signs, containing the formula in column C
            Next c
        End If
    Next sht
    Sheets(shtName).Activate                                'make the new sheet the activesheet
    ActiveSheet.Columns("A:C").AutoFit                      'autofit the data
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Merged cells are a real problem when using Vba. They are often used just for display purposes; to get text centred across a number of columns. You can get exactly the same display effect by using the format "Center across selection" If you are able to change all your merged cells to this format it could solve your problem. I have never needed to use merge cells I have always managed to do it by formatting.
If you do need to run it on a worksheet that has merged cells why not just unmerge the used range before you run the macro??
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top