Populate field with formula results

[RedWizard75]

Hi,

I'm using the following 'stolen' code. To get the name of the sheets (which are the names of servers), which include patch information.
The 'server sheets' have varing rows which relate to the number of patches that are required to patch the server.

Initial VBA instructions are:

Sub IDX()
Dim ws As Worksheet, i As Integer, NumPatch As String, CountRange As String
Worksheets.Add(before:=Worksheets(1)).Name = "Summary"
i = 0
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        i = i + 1
        Sheets("Summary").Range("A" & i).Value = ws.Name
        Sheets("Summary").Hyperlinks.Add Anchor:=Range("A" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
    End If
Next ws
Sheets("Summary").Columns("A").AutoFit
End Sub

This creates a 'Summary' sheet at the front of all the worksheets. I places the name of the server in the 'A' rows as it works through the name of the sheets.
It then adds a Hyperlink to that sheet in the 'Summary' sheet.

I am trying to expand the code as such:

Sub IDX()
Dim ws As Worksheet, i As Integer, NumPatch As String, CountRange As String
Worksheets.Add(before:=Worksheets(1)).Name = "Summary"
Sheets("Summary").Range("A1").Value = "Server Name"
Sheets("Summary").Range("B1").Value = "Patches to Apply"
i = 1
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        i = i + 1
        Sheets("Summary").Range("A" & i).Value = ws.Name
        Sheets("Summary").Hyperlinks.Add Anchor:=Range("A" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
        Sheets("Summary").Range("B" & i).Value = "=COUNTIF(INDIRECT(""'""&A &i &""'""!A:A"")," <> ")"
    End If
Next ws
Sheets("Summary").Columns("A").AutoFit
Sheets("Summary").Columns("B").AutoFit
End Sub

The 'A' range gets the correct name of the 'sheet' and creates the Hyperlink correctly.
However, I'm trying to get the number of rows in the 'sheet' and display that in the 'B' range.

With this code, the 'B' range returns 'TRUE', rather than a number!!!
I can manually put the following formula into the 'B' range

 =COUNTIF(INDIRECT("'"&A2&"'!A:A"),"<>")

NOTE: A2, increases by 1 as I paste down the page, that is, A3, A4, etc...

And I get the number of records in the sheet of 'A#'.

I did see something that says 'INDIRECT' is unrecognisable to VBA. However, If I could get the string populating the 'B' range. I can remove the 'string' quote and get the answer ...

So in short, how can I place this

 =COUNTIF(INDIRECT("'"& A & i &"'!A:A"),"<>")

into a string?

Thank you in advance.

 

[VoG]

Perhaps

Sub IDX()
Dim ws As Worksheet, i As Integer, NumPatch As String, CountRange As String
Worksheets.Add(before:=Worksheets(1)).Name = "Summary"
Sheets("Summary").Range("A1").Value = "Server Name"
Sheets("Summary").Range("B1").Value = "Patches to Apply"
i = 1
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        i = i + 1
        Sheets("Summary").Range("A" & i).Value = ws.Name
        Sheets("Summary").Hyperlinks.Add Anchor:=Range("A" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
        Sheets("Summary").Range("B" & i).Value = ws.UsedRange.Rows.Count
    End If
Next ws
Sheets("Summary").Columns("A:B").AutoFit
End Sub

 

[RedWizard75]

Perhaps

Sub IDX()
Dim ws As Worksheet, i As Integer, NumPatch As String, CountRange As String
Worksheets.Add(before:=Worksheets(1)).Name = "Summary"
Sheets("Summary").Range("A1").Value = "Server Name"
Sheets("Summary").Range("B1").Value = "Patches to Apply"
i = 1
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        i = i + 1
        Sheets("Summary").Range("A" & i).Value = ws.Name
        Sheets("Summary").Hyperlinks.Add Anchor:=Range("A" & i), Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
        Sheets("Summary").Range("B" & i).Value = ws.UsedRange.Rows.Count
    End If
Next ws
Sheets("Summary").Columns("A:B").AutoFit
End Sub

Worked an absolute treat!!!

Thank you VERY VERY much.

Cheers.