VBA Iteration not calling different values?

[pficky]

I am writing a function to calculate the standard error of the estimate of a linear regression (I understand excel already has this), but I am doing it so that I can give it the linear equation that I want to be considered, rather than the function calculating it itself as the STEYX built-in function does. I cannot seem to get VBA to calculate my y-predicted values though. The For loop I have written just populates my array with the same value, which is my y-predicted at my first x-value, and then it writes this to every slot in my array. Can someone please tell me what is wrong with my code? If I can't get beyond this I won't be able to calculate the rest of what I need to for the statistic.

Function CustomSyx(yvalues As Range, xvalues As Range, slope As Variant, intercept As Variant)
'Define variables
    Dim n As Integer
    Dim i As Integer
    Dim Syx As Variant
    Dim ypred() As Variant
    Dim resid() As Variant
    Dim m As Variant
    Dim b As Variant
    Dim residsum


'Set Values
    x = xvalues.Value
    y = yvalues.Value
    n = WorksheetFunction.Count(x)
    m = slope
    b = intercept
    
    ReDim ypred(1 To n)
    ReDim resid(1 To n)
    
    For i = 1 To n
       ypred(i) = m * x(i, 1) + b
    Next i
    
    CustomSyx = x


End Function

 

[gallen]

I have no idea what you just said

However if that code is putting the same value into your array it's because

[COLOR=#333333]= m * x(i, 1) + b[/COLOR]

is producing the same answer.

Are you familiar with debugging? You need to step through the code and every time it hits this line you want to see what values all the variables contain.

You have a variable 'x' that isn't declared when all the others are. Is this declared at module level?

Either way your start point is seeing what each variable holds every time the loop occurs.

 

[steve the fish]

Your function isn't really doing anything. The answer will always be the value of the first cell in the xvalues range.

 

[pficky]

I'm not sure what changed but it is now doing everything that I wanted it to do. When I add a print command to the loop it would give me each value that I wanted, but when I was putting the array out on the spreadsheet it was just showing one value. I don't really know why but I got what I wanted and it is now working consistently. Thanks for the tips!

Also Steve, I had x as the answer because I had been checking to make sure that the loop wasn't failing for any reason, if it does it will return a #VALUE error even if you aren't calling anything to do with the loop. Forgot to change it before posting!