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

 

[Marc Wylie]

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?

Hi,

Its a Formula =SUM(H2:H24)/23 giving the average of the values in the cells

for those figures I put in there is no extra decimal places

if there were other figure the decimal places could be huge of course

Sorry for being a pain

Thanks

Marc

 

[FormR]

Sorry for being a pain

Your not being a pain, I'm sure we can figure this out!

In the example when the H25 cell = 14.8 -what are the list of values H2:H24? Are these the result of a formula or hard coded?

What does this formula return when placed into a spare cell in the Factor sheets?

=H25=14.8

p.s. why not use =AVERAGE(H2:H24) in the H25 cell?

 

[Marc Wylie]

The answer to your AVERAGE formula question is because I'm pretty new to all this, I have taught myself VBA over the last two weeks as I need to make this sheet work for my MSc dissertation.

On the =H25=14.8 front I get -14.8 (that's with 0 in H25)

The cells H2: H24 are hard coded on a code that is called from another code ( been doing a lot of work)

Thanks

Marc

 

[FormR]

On the =H25=14.8 front I get -14.8 (that's with 0 in H25)

You need to do the test when you believe the value of cell H25 to be 14.8 like in the example you gave.

 

[Marc Wylie]

You need to do the test when you believe the value of cell H25 to be 14.8 like in the example you gave.

I will do the test tomorrow night and post the results. Sorry if I don't go now the wife will kill me.

Thank you again for all your help

Marc

 

[Marc Wylie]

Hi,

So test with 14.8 in the cell on two sheets both come back with true

Thanks

Marc

 

[FormR]

Marc,

I can't replicate the results that you are seeing, unless anyone else here has any bright ideas then I think we will need to see the workbook.

Can you remove all data and sheets that are not related to the problem, make sure that it still consistently misbehaves, and upload to a free file sharing site (like dropbox or one-drive) and share the link here?

Note: it's unlikely that I will have a chance to look at the file until tomorrow. Hopefully someone else will jump in before that though.

 

[Marc Wylie]

Hi,

I'm trying changing my code up a little for the entry into H25 and will let you know by tomorrow if it starts behaving.

If not I will drop the file as you suggest.

Thank you very much for all your help

Marc

 

[Marc Wylie]

So I have changed the code to this to see whats going on:

Option Explicit
Dim ws As Worksheet, i As Variant, j As Variant, Dash As Integer, Div As Integer


Sub Calc()

For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "Factors Sheet (*" Then
        i = i + ws.Range("B3")
        j = j + ws.Range("A3")
    End If
Next ws

Dash = Sheets("Dashboard").Range("M18").Value
Div = Sheets("Dashboard").Range("M19").Value
If j > 0 Then
    Sheets("Dashboard").Range("M18").Value = i
    Sheets("Dashboard").Range("M19").Value = j

End If

End Sub

Basically what is happening is the values from the first use of the code are retained after first use, and adds the values from next use to the total

Any Ideas

 

[Marc Wylie]

Sorted
I = 0
j = 0

After sub declaration

Thanks for all yur time and help