Stats in a variable location

[pingwin77]

I have a workbook with a varying amount of parts in it. Each part takes up 2 columns. I attached a sample sheet showing a report with 3 parts and one with 5 parts. It can have up to 118 parts in it. The parts are added via a macro that asks how many parts are needed.

I need to have some statistics pasted at the end of the report (in this case that would be at the far RIGHT side of the report.) The big problem is that I can only use the data that is in every other column. The second column calculates my deviation from nominal.

The attached sheet has the formulas in it. Some of the cells referenced do not move and some will be dependant on how many parts are there. Can this be done? If you need a row to count from to find the end, use row 11 starting in call J11. These are guaranteed to have data in them all the way to the end.

Please let me know if you have any further questions.

[nst1107]

Just to clarify a few things: Your question is how to place the statistics at the far right side of the report (adjacent to the last used column, not ridiculously to the far right just in case there are 118 parts, I assume), and you'd like to know how to use in your statistics only the data from the first column in each set of two columns in each part?

[pingwin77]

Yes, that is what I am looking for. I actually need the stats to start 3 columns after the data. We used the columns at the end of the report to make notes.

The use of the first column of the two per part is correct as well.

[nst1107]

While I'm working on this, I understand the columns may expand all the way to 118 parts. May there also be more than 2 rows?

[pingwin77]

Yes. Here is how the report works:

We add the total number of features per part. (rows, could be any number)

then we copy cell range J10:K? (depends on how many features there are) and paste them with an offset of 2 across the columns generating our total parts.

If there is a way to search the number of parts to determine where to start and the number of features to determine how many rows of stats I would need that would work perfect. I can just attach that to a button on my report.

[nst1107]

That should be easy. I assume you have to know the number of parts to paste somehow, and likewise there must be a way you know how many features to include. Create two public variables, one for the number of parts, and one for the number of features. (At module level, above all procedures in any module, insert something like this:[vba]Public numParts As Integer
Public numFeatures As Long[/vba]Then, whenever you determine the number of parts (from a userform, perhaps?), assign that number to the "Parts" variable. Likewise for the number of features. These public variables will store these numbers and you can use them again to calculate the number of columns and rows.

Does that help?

I'm looking through your macros to find the one that does the pasting you mentioned in order to give you a more precise example.

[nst1107]

I found this in Module 20:
[VBA]Sub AddParts()
'
' AddParts Macro
' Macro recorded 5/7/2008 by AIS
Application.ScreenUpdating = False
Dim Check, Counter, Features As Range, Message, Title, Default, PartsValue
' Display message, title, and default value.
PartsValue = InputBox("Enter number of Parts", "Add Parts Columns", 0)
[/VBA]
I also found this in Module3:[VBA]Set Parts = Worksheets("Controls").Range("O22")
[/VBA]
So, if you are storing the number of parts in the worksheet, you can use that number, or instead of making "PartsValue" a procedure-level variable, place it at the top of Module20 like so:[VBA]Pubic PartsValue As Integer[/VBA]
The same deal with the number of features. This is in Module3:[VBA]Set Features = Worksheets("Controls").Range("G11")
[/VBA]
Either get the number of rows from the worksheet, or declare "Features" a public variable at module level.

Does that solve anything?

[pingwin77]

Here is the code for adding rows: (I'm still struggling with this one a little but it seems to work so far)

' Add_Rows_Devaiation Macro
' Macro recorded 9/9/2002 by Gary Kapsner
' Macro edited 10/24/2007 by Mike Hemm
' Macro edited 01/06/2009 by Mike Shadick
Application.ScreenUpdating = False
Dim Check, Counter, Features As Range, Message, Title, Default, FeaturesValue
Dim MLRValue

TOTAL = TextBox1
MMC = (TextBox2 * 5)
RFS = (TextBox3 * 3)
PROFILE = (TextBox4 * 2)
ADDITIONAL = TextBox5
FeaturesValue = (TOTAL + MMC + RFS + PROFILE + ADDITIONAL)
FeaturesValue = FeaturesValue - 2

If FeaturesValue < 1 Then
    GoTo line3
    Else
        If FeaturesValue = 1 Then
            GoTo line1
            Else
                GoTo line2

line1:
ActiveSheet.Rows(13).Select
    Selection.Delete
Range("B12").Select
Unload Me
    
line2:
FeaturesValue = FeaturesValue - 2
Check = True: Counter = FeaturesValue     ' Add number of features
ActiveSheet.Rows(13).Select
    Selection.Copy
Do                               ' Outer loop.
    Do While Counter > 0         ' Inner loop.
        Counter = Counter - 1    ' Increment Counter.
        
        ActiveCell.Offset(1, 0).Activate
        ActiveSheet.Paste
        
        If Counter = 0 Then     ' If condition is True.
            Check = False       ' Set value of flag to False.
            Exit Do             ' Exit inner loop.
        End If
    Loop
Loop Until Check = False    ' Exit outer loop immediately.
    
Application.CutCopyMode = False
Range("B12").Select
Unload Me

line3:
    MsgBox "You can not have 0 features in this report. Please try again.", vbOKOnly
Unload Me

Line4:
Unload Me
End Sub

Here is the code for adding parts:

' AddParts Macro
' Macro recorded 5/7/2008 by AIS
Application.ScreenUpdating = False
Dim Check, Counter, Features As Range, Message, Title, Default, PartsValue

' Display message, title, and default value.
PartsValue = InputBox("Enter number of Parts", "Add Parts Columns", 0)
PartsValue = PartsValue - 1
If PartsValue = 0 Then GoTo line2 Else GoTo line1

line1:
Check = True: Counter = PartsValue     ' Add number of parts
Range("J10").Select
Range("J10:K550").Select
    Selection.Copy
Do                               ' Outer loop.
    Do While Counter > 0         ' Inner loop.
        Counter = Counter - 1    ' Increment Counter.
        
        ActiveCell.Offset(0, 2).Activate
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Activate
        ActiveCell.Value = ActiveCell.Offset(0, -2) + 1
        ActiveCell.Offset(0, -1).Activate
        
        If Counter = 0 Then     ' If condition is True.
            Check = False       ' Set value of flag to False.
            Exit Do             ' Exit inner loop.
        End If
    Loop
Loop Until Check = False    ' Exit outer loop immediately.



Application.CutCopyMode = False
Range("B12").Select

line2:

End Sub

[pingwin77]

[/vba] I also found this in Module3:[vba]Set Parts = Worksheets("Controls").Range("O22")
[/vba]

Either get the number of rows from the worksheet, or declare "Features" a public variable at module level.

Does that solve anything?

The part in Module 3 is left over from the previous coder and is not being used. I am trying to go through and get rid of all the junk not being used.

So by making the Number of parts a public value at the top of Module20, I can then recall that from any other module in this workbook. So I could write another code that recalls this value and creates the stats at the end of the report. Similarly, I can then do the same for the number of features by making that value public as well and use that in a loop for copying and pasting down the right side of the report for all the features?

[nst1107]

Exactly. So long as you don't stop execution of the code between giving the public variable a value and recalling it, the value will not be erased as execution passes from one procedure to another, and can be used by any procedure in any module in that workbook.

[pingwin77]

My codes are run separately.

I run the code to populates the rows (features for each part). The code ends.

Then I make some changes to the cells in the first part such a formulas and formatting.

Then I run the code to add parts. It copies everything in columns J10:K? (again depending on number of features) and pastes then with an offset of 2 across the columns.

These are done with 2 seperate buttons. Is this a problem for the Public command? If I understad your post correctly, they need to run back to back with out stopping?

[nst1107]

Yes, in this case you will have to make a more permanent storage, such as in a worksheet. Instead of publicly declaring the variables, assign a worksheet range to each.

[pingwin77]

So I could use a count function to figure out how many row I have and how many column I have. That should be easy enough.

I hope my last question will be how to set up a formula to calculate vairible amounts of data. Such as a formula that will calculate the average value over 10 parts as well as 118, skipping every other row? Can that be done with a formula directly in excel or would it be better to calculate this in VBA and then just past the results into my woorkbook? Either way, I will need a bit of guidance.

[nst1107]

Dynamic ranges are best handled through VBA. Just a few thoughts, you will probably want to use a counter and a loop to cycle through all the rows/columns and either Evaluate or Application.WorksheetFunction. I'm not very familiar with Evaluate. I'll see what I can cook up.

[nst1107]

I haven't tested this; I don't know if it works. But perhaps it will help get you on the right track.
[vba]Option Explicit
Sub RunIt()
Dim Sheet As Worksheet, firstCol As Integer, lastRow As Long, c As Range
For Each Sheet In ThisWorkbook.Sheets
For Each c In .Range(.Cells(11, "J"), .Cells(11, .Columns.Count)) 'Finds last used column in row 11.
firstCol = firstCol + 1
If c = vbNullString Then
firstCol = firstCol + 3 + 9 '+3 for the spacing, +9 for the columns not counted.
Exit For
End If
Next
For Each c In .Range(.Cells(11, "J"), .Cells(.Rows.Count, "J")) 'Finds last used row in column "J".
lastRow = lastRow + 1
If c = vbNullString Then
lastRow = lastRow + 10 '+10 for the rows not counted.
Exit For
End If
Next
FillStats Sheet, firstCol, lastRow
Next
End Sub
Sub FillStats(Sheet As Worksheet, firstCol As Integer, lastRow As Long)
Dim cellsToUse As String, i As Long, j As Integer
With Sheet
For i = 12 To lastRow 'Loop to the end of the rows.
For j = 10 To firstCol - 3 Step 2 'Loop through the parts.
If .Cells(i, j) = vbNullString Then Exit For
cellsToUse = cellsToUse & "," & .Cells(i, j).Address 'Fill string of cell addresses.
Next
.Cells(i, firstCol).Formula = "=AVERAGE(" & cellsToUse & ")" 'AVERAGE, MAX, MIN, and STDEV can handle up to 255 numbers, or cell addresses.
.Cells(i, firstCol + 1).Formula = "=MAX(" & cellsToUse & ")"
.Cells(i, firstCol + 2).Formula = "=MIN(" & cellsToUse & ")"
.Cells(i, firstCol + 3) = .Cells(i, firstCol + 1) - .Cells(i, firstCol + 2)
.Cells(i, firstCol + 4).Formula = "=STDEV(" & cellsToUse & ")"
.Cells(i, firstCol + 5).Formula = "=(($G" & i & "+$H" & i & ")-($G" & i & "-$I" & i & "))/(6*AA" & i & ")"
.Cells(i, firstCol + 6).Formula = "=MIN(((($G" & i & "+$H" & i & ")-W" & i & ")/3*AA" & i & "),((W" & i & "-($G" & i & "-$I" & i & "))/3*AA" & i & "))"
Next
End With
End Sub
[/vba]

[pingwin77]

Would this be setup in 2 different sub routines or one single one?

[nst1107]

Right now it's set up as two subs, but by moving a few things around you could make it one.