VBA to sum cell H25 from all worksheets named Factors Shet () and divide by the total number of sheets name Factors Sheet ()

[Marc Wylie]

Hi All,

I'm Trying to write a VBA Code to sum all values from Cell H25 in sheets named Factors Sheet () and divide by the total number of sheets by that name. and place the result on a cell within a sheet called dashboard.

I know how to do it for all active sheets but my issue is that the number of Sheets could be from Factors Sheet (1) through to Factors Sheet (200) or an indefinite number, and as the intent of the formula is to average the total from only sheets named Factors Sheet () I cant just use all active sheets as it will skew the result

Thanks for your time (again)

Marc

 

[FormR]

Something like this maybe:

Sub m()
Dim ws As Worksheet, i As Double, j As Long
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "Factors Sheet (*" Then
        i = i + ws.Range("H25")
        j = j + 1
    End If
Next ws
If j > 0 Then
    Sheets("Dashboard").Range("A1").Value = i / j
End If
End Sub

 

[Marc Wylie]

Something like this maybe:

Sub m()
Dim ws As Worksheet, i As Double, j As Long
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "Factors Sheet (*" Then
        i = i + ws.Range("H25")
        j = j + 1
    End If
Next ws
If j > 0 Then
    Sheets("Dashboard").Range("A1").Value = i / j
End If
End Sub

If I could buy you a beer or ten to say thank you, you would be rather drunk this afternoon.

Thank you so much for your help. Exactly what I needed.

Marc

 

[FormR]

Glad it helped!

 

[Marc Wylie]

Glad it helped!

Hi again,

Quick question, any idea how to get the code to rest itself after use?

I was doing some validation testing today, and on first use the code works fine and delivers the figure expected, but if changes are made to the factors sheet and the code is run again, the figures returned don't add up ( ts not out bu a lot but enough to skew the result). If I manualy reset the code in the vba editor all is well again.

Once again all help gratefully appreciated.

Marc

 

[FormR]

Hi Marc,

The code will re-calculate the value in the dashboard sheet anew every time it is executed. If this is not what you are expecting could you describe what you would like to happen.

If I manualy reset the code in the vba editor all is well again.

I'm not sure what you mean here? What does "manually reset the code" mean?

 

[Marc Wylie]

Hi,

On the first attempt the code works as it should and returns the value expected. But I subsequently change the values in H25 on the sheets and run the code again it does not return the value expected. Its never out by a lot but its nt right.

If I the go into the VBA code window and reset the code using the reset button on the tool bar (this is what I referred to as manually reset), the code works fine again the first time, but again if I change the figures it becomes erroneous again.

Thanks

Marc

 

[FormR]

You are aware that the Macro needs to be manually executed each time you change one of the values in the "Factors Sheet(s)" right?

Assuming you do, can you post a list of the values that appear in H25 of the Factors sheet(s) and state the expected result and erroneous result that you end up with?

 

[Marc Wylie]

Hi,

Yes I am executing the code using a button.

So for example on the first attempt (and to keep it easy) f I have
FS (1) H25 = 14.1
FS (2) H25 = 14.1

Then f I run the code returns as expected I get: 14.1

If I change the two values to

FS (1) H25 = 14.8
FS (2) H25 = 14.8

and run the code again I get 14.47 when I'm expecting 14.8

and if I run it again not changing anything I get 14.58

and again I get

14.64

and if I keep running it, the value keeps changing

 

[FormR]

How are the values getting into the H25 cells? Is there a formula generating them?

Are you 100% sure that the values are exactly as you say, i.e. there are not extra hidden decimal places?