Finding input cells in a large excel model

[bjorn007]

Hello,

I am trying to create a macro that searches through an excel file and returns input cells (cells that do not depend on other cells - i.e. the last cells when using the Precendent function). My excel file has several excel sheets.

This is way above my Excel and VBA knowlegde, so do hope someone have a formula or have some advice for solving this. Been searching the internet for an good answer, but haven't managed to find anything that is working.

Have a good day.

Bjørn

 

[DonkeyOte]

Bjorn, depending on how many non-contiguous ranges you have on each sheet being referenced you might get away with something along the lines of the below ?

Sub Example()
Dim ws As Worksheet
If Not Evaluate("ISREF(PRIMEMOVERS!A1)") Then Sheets.Add(Before:=Sheets(1)).Name = "PRIMEMOVERS"
With Sheets("PRIMEMOVERS")
    .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp).Offset(, .Columns.Count - 1)).Clear
    .Cells(1).Resize(, 2).Value = Array("Sheet", "Address")
    For Each ws In ThisWorkbook.Worksheets
        If UCase(ws.Name) <> "PRIMEMOVERS" Then
            With .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2)
                .Cells(1).Value = ws.Name
                .Cells(2).Value = ws.Cells.SpecialCells(xlCellTypeConstants).Address
            End With
        End If
    Next ws
End With
End Sub

The above would add a sheet (if it didn't exist) which would list by sheet all constants in use.
Of course this would not pick up blanks if they were "prime movers" (ie first precedent) - unclear if this is an issue or not.