Posts by jjligman

I figured it out. I have no idea why I can't get it to run as a function but as a sub it works fine with this code:

Sub Josh()

Dim i As Integer
z = 0
N = Range("M7").Value2 - 1
Range("F17").Select
Range("F17").Activate

For i = 1 To N
x = ActiveCell.Value2
ActiveCell.Offset(1, 0).Activate
y = ActiveCell.Value2

w = Abs(y - x)

z = z + w


Next i

Range("m5") = z

Range("K14").Activate

End Sub

I am working with an Excel Workbook that is used to record measurements. Each Sheet is a copy of the master for a new lot to record measurements for that lot. I am trying to automatically calculate the Cpk the same way Minitab does and unfortunately Minitab doesn't use a normal standard deviation, it uses Stdev(within). I need help figuring out why my code isn't working becuase it appears so simple but it isn't summing the loop correctly.

The way this workbook is set-up, no matter how many results there are I can sum them simply with the code =Sum(Inspection_Record3[1]) and this will sum all the results under the 1 column. I could do the same thing for an average,a count, etc. In an outside cell I use the count function so then in my code I can refer to that count, subtract two and I have all the values I need to use to get my absolute values. The issue is to get the stdev(within) I need to find the moving range in the background. To do this you take the absolute value of the first result minus the second result, add that to the absolute value of the second minus the third, and so on for all the results and get the sum of all these numbers. I can't seem to make this work at all. I thought I knew how to get it and when I hit play in VBA it scrolls down to all the proper cells and stops where it is supposed to but when I try typing the function =josh() in a cell it says "Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command". Then it just puts 0 in the cell. I swear I have done loops like this before where the sum in the loop just keeps growing until the loop is over. I have no idea why this isn't working. Any help would be soooo greatly appreciated. I know it is something small I'm missing I just can't seem to see it. My code is below.

Thanks Everyone who took the time to read this and thanks in advance for any assistance you may be able to provide.

Regards,

Josh

1    Function josh()
2        Dim x As Integer
3        ' Set numrows = number of rows of data.
4        NumR = Range("M7").Value
5        NumRows = NumR - 2
6        ' Select cell F17.
7        Range("F17").Select
8        ' Establish "For" loop to loop "numrows" number of times.
9        For x = 1 To NumRows
10            Y = ActiveCell.Value
11            ActiveCell.Offset(1, 0).Select
12            Z = ActiveCell.Value
13            Q = Abs(Z - Y)
14            Sum = Sum + Q
15        Next
16            josh = Sum
17            
18    End Function

Thanks again everyone!

As I said, I haven't wrote the code for the loop to create the sum but what this is for is and excel spreadsheet in which measurements are recorded and each time a new measurement is recorded the worksheet automatically carries the conditional formatting for the tolerances to the next line so you can select all the data without knowing how much there is simply with (Inspection_Record3[1]) so I could write  =sum(Inspection_Record3[1]) a cell for the sum of these measurements. Unfortunately I need to calculate the CpK as it changes when more measurements are added and it has to match the way Minitab calculates CpK and Minitab uses the "standard deviation within" to do this rather than a normal standard deviation (which would make this very easy if they did). Do this in Excel I need to find the sum of the "moving range" behind the scenes. The moving range is simply the absolute value of the measurement minus the measurement before it. I need the sum of all those values again with it constantly changing as more measurements are added. I know how to do that if I can just get the first one to work in a way that refers only to the active cell and an offset one row below the active cell otherwise the loop wont work right if it is always starting at a specific referenced cell. That's why I was trying to activate the first measurement cell outside where the loop would be so within the loop it would then just keep shifting one cell down until there were no more values.

If anyone has any better ideas for a way to do this I'm open to any help or suggestions. I don't know why it seems like I'm just missing something easy to be able to get that first and next result without using a cell reference when assigning the values to variables. I thought activating or selecting the cell and then just trying to have the variable be equal to the active cell would work but with the code in my first post it isn't, it is still taking the value from the cell I put the function in and the value of the cell below that one. Activating or selecting a different cell to be the Active cell just doesn't seem to work the way I thought it would.

Thanks for your time and any insight, it's always appreciated.

-Josh

This isn't the finished code, I will need it to loop, but I can't even get it to return the first value!! I have a numerical value in F17 but I cant just say "w = Range("F17").Value" because I will need this formula to start at F17 and keep going until there are no more rows. Here's what I tried after Range("F17").Select and Range("F17").Activate didn't work:

Function josh()


Range("D17:H17").Select
Range("F17").Activate


w = ActiveCell.Value
y = ActiveCell.Offset(1, 0).Value
Z = Abs(y - w)
Sum = Sum + Z


josh = Sum


End Function

I hope you can see what I'm trying to do once it starts if I can get the first result. I will start with the first result in F17, take the absolute value of (the cell below it - active cell) and then when I put in the loop it should do the same but one row down so ABS(F19-F18) then next loop ABS(F20-F19) and so on summing the total. The problem in I cant use specific cell references in the loop because I need it to keep moving down an unknown amount of times (until there are no more values in the next cells). Any ideas???? I feel like I'm missing something so simple.

Thanks all for your time and any assistance you may be able to provide.

Regards,

Josh