Posts by gc052

Re: Contiguous Weighted Average Excluding Zeroes

Sorry, I forgot to add in that the rows in between aren't actually blank, I meant to say that the rows with values will be subtotals. I've already got a method to filter out the intermediate rows (sumproduct, A:A, B:B, C:C)/(Sumproduct, B:B, C:C) where C has 1s and 0's that cancel out the 0 values in B

If I use that method again it will throw off the grand total, so I was wondering if there was a way to model the formula that I mentioned above.

Hi,

Is there a way to calculate a contiguous Weighted Average for rows, omitting/subtracting weights that have zero values? Something to the effect of

W=Weight, V=Value

[(W1*V1)+(W2*V2)+(W3*V3)+(W4*V4)]/(Wtotal-Wn) , where Wn corresponds to a Vn that = 0

The only method that's popping up in my head right now is a ton of IF's for each scenario where Vn=0, since its about 5 contiguous rows that I'm doing this for, and I've already implemented a 3 array sumproduct that uses 1's and 0's for significance in a third column. These 5 rows are subtotals of the 1's and 0's rows, so doing the same thing would include all the intermediate rows.

I'm attaching a scrap book for reference. Theforum.ozgrid.com/index.php?attachment/69597/

Please let me know if there is anything I need to elaborate on.

Thanks!

Re: COUNTIF for value in Data Validated Cells

Ah that's awkward, tried to donate, but I'm getting an error since I'm not located in Aussie. Any other charities that can take PayPal or a Canadian CC?

Re: COUNTIF for value in Data Validated Cells

Works perfectly!

Thanks a ton Skywriter, can I make a donation to a charity of your choice? Maybe Auscam freedom project for the DHF?

GC052

Re: COUNTIF for value in Data Validated Cells

Yes, there will be non-validated cells containing the word "yes'. Would you be able to return the count value in cell C1, (please) ?

Thanks Skywriter!

Re: COUNTIF for value in Data Validated Cells

I've attached a sample below, please let me know if there are any issues.

forum.ozgrid.com/index.php?attachment/68796/

Hi all,

How would I be able to restrict a countif function to data validated cells only?

Lets say I'm trying to get the count for the word "Yes" in all data validated cells.

Only thing I've got so far

ActiveCell.SpecialCells(xlCellTypeAllValidation).Select

Thanks!

Re: List Same Cell From Multiple Worksheets

The first one you posted worked great Skywriter - it even pasted as values only, cleaner than the original code, so thank you very much, I appreciate it. Thanks for your help as well pike!

Do I have to mark this thread as solved or do I just let it float to the next pages of the forum?

Re: List Same Cell From Multiple Worksheets

Quote from skywriter;766752

We would probably have to change this line.

Code

ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)

So the question is where do you want the first value to be pasted?

The code can be written to work from wherever you say we can start and will allow blank cells.

To answer your other question all sheets have an index number.

The index number is determined by the order of the tabs, so if you put the tabs in a certain order and let's say you have 8 sheets and the last sheet is the one you want the code to ignore, we write code to loop through sheets 1 to 7.

Display More

Hi Skywriter, thanks for responding

For where it should start - same as the original code: a2, b2 etc. to accommodate for headers

For part 2: Is there a way to set it to all sheets before the activesheet? Then if I have to add sheets afterward I won't have to change the loop sequence(?)

Hope this is enough

Thanks!

Hi all,

I was wondering if someone would be able to help modify the code that AAE posted for whitegoldf150's problem (http://www.ozgrid.com/forum/showthread.php?t=87516) - I'm trying to do the same thing, except I have blank cells that are being skipped when I run the script. I'd like to return even the blank cells so that every column is filled to the same last cell (as many as there are pages). (I've already replicated it to return other values in other columns so the spacing is off). Also is there is a way to fill values for a specific set of sheets (lets say all sheets before the summary sheet) instead of inserting excluded sheet names after the inequality "If ws.Name <> "Sheet1""?

Sub MakeSummaryTable()
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    Sheets("Sheet1").Activate
     


    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Range("C1").Copy
            ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

Thanks!

GC052