Extract formulas of a workbook

[Juggler_IN]

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

Formula	Sheet Name	Cell Address
RANDBETWEEN(0,9)	Sheet1	A1
RANDBETWEEN(0,9)	Sheet1	B1
RANDBETWEEN(0,9)	Sheet1	A2
RANDBETWEEN(0,9)	Sheet1	B2
RANDBETWEEN(0,9)	Sheet1	A3
RANDBETWEEN(0,9)	Sheet1	B3
	Sheet1	A5
	Sheet1	A6
	Sheet1	A7

Desired is:

VBA Express Forum - Re: List Formulas

Formula	Sheet Name	Cell Address
RANDBETWEEN(0,9)	Sheet1	A1
RANDBETWEEN(0,9)	Sheet1	A2
RANDBETWEEN(0,9)	Sheet1	A3
RANDBETWEEN(0,9)	Sheet1	A5
RANDBETWEEN(0,9)	Sheet1	A6
RANDBETWEEN(0,9)	Sheet1	A7

how to make the required modification?

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

 

[offthelip]

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??