• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with workbooks and worksheets objects

Vazgen

New Member
Hello everyone,
Can someone please help me with this problem?

Write a general purpose sub that opens a particular workbook, such as C:\ MyFiles\Company Data.xlsx, adds a new worksheet named Formula List after the original worksheets, and then goes through all of the original worksheets hunting for cells with formulas. Each time it finds a formula, it records information about it in a new row of the Formula List worksheet. Specifically, it records the worksheet’s name in column A, it records the formula as a string in column B, and it records the formula’s value in column C. (Hint: To check whether a cell contains a formula, use VBA’s HasFormula property of a range.)
 
Check this..

Code:
Const myPath As String = "C:\ MyFiles\Company Data.xlsx"
Sub find_f()
Dim ws As Worksheet, rng As Range, mywb As Workbook, myws As Worksheet

Set mywb = Workbooks.Open(myPath)

With mywb
    .Sheets.Add().Name = "Formula"

Set myws = .Sheets("Formula")

lrow = 1
For Each ws In .Worksheets
    For Each rng In ws.UsedRange
        If rng.HasFormula Then
            myws.Cells(lrow, 1).Value = ws.Name
            'myws.Cells(lrow, 2).Value = Application.FORMULATEXT(rng) 'for excel 2013
            myws.Cells(lrow, 2).Value = " " & CStr(rng.Formula)
            myws.Cells(lrow, 3).Value = rng.Value
            lrow = lrow + 1
        End If
    Next
Next
.Close True
End With

End Sub
 
Back
Top