subscript out of range - how to determine

[JohnJack]

Hello everyone,

Below is my function I am trying to write. The problem I am running
into is that I cannot figure out what line of code will allow me to
exit the loop once y0(1,A) = <subscript out of range>, ie there are no
more columns in the range. Can anyone point out how to trap/exit when
this error occures so that I can exit and the A value is the number of
columns in the range?

Can anyone help me out here?

Cheers,

Jack


Function SumAbs(y0 As Range)
'this function will return the sum absolute value from all the cells
in the range provided

Dim A, B, Counter,Counter2 As Integer

A = 1
Do While y0(1, A) <> ""
A = A + 1
Loop
A = A - 1

B=Application.Count(y0)/A ' this part determines how many rows are
in the y0 range by dividing the total count by the amount of columns
(A)

SumAbs=0

For Counter2= 1 to A
For Counter = 1 To B

SumAbs = SumAbs + Abs(y0(Counter, Counter2))

Next Counter
Next Counter2

End Function

 

[Bob Phillips]

Function SumAbs(y0 As Range)
'this function will return the sum absolute value from all the cells in the
range provided

Dim A, B As Double
Dim Counter As Long, Counter2 As Long
Dim oCol As Range

For Each oCol In y0.Columns
If oCol.Cells(1, 1) = "" Then
Exit For
End If
A = A + 1
Next oCol

B = Application.Count(y0) / A

SumAbs = 0

For Counter2 = 1 To A
For Counter = 1 To B

SumAbs = SumAbs + Abs(y0(Counter, Counter2))

Next Counter
Next Counter2

End Function

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JohnJack]

Thanks a ton. Works great.
Cheers,

Jack

 

[Don Guillett]

I don't understand what you are trying to do?

 

[JohnJack]

In quattro pro you can have a formula @sum(@abs(Range)) that will give
you the sum of the absolute values of every cell in that range. In
excel, the only way to do this is to do sum(abs(range) and use the
ctrl-shift-enter when applying the formula, which turns into
{=sum(abs(range)}. The problem is is that a user will often click in
the formula bar to check what range is being selected, and if they are
unaware that they have to hit ctrl-shift-enter the output from that
formula is now incorrect. To avoid having to have a 2nd column with
the individual absolute values and suming that column or running the
risk of a user messing up the formula (protection of the cell isn't a
viable solution in this case), I'm writing this function. If there is
a easier way, I'm all ears, but everywhere I've looked I haven't found
a function that is as good as the quattros @sum(@abs(range)).

 

[Don Guillett]

From what you said originally, I thought to suggest the array formula you
cite. But, your point about end users is well taken, unless you protect or,
as we do in TEXAS, just shoot em.

 

[Guest]

Another way would be

Function SumAbs(y0 As Range)
Dim cell as Variant, v as Variant
v = y0.value
for each cell in v
if isnumeric(cell) then
SumAbs = SumAbs + Abs(cell)
end if
Next
End function

v is a variant array holding the values in your range and can be traversed
much faster than a range of cells, even if you cut down on the number of
columns examined.


Also, I am sure Bob edited your code and overlooked correcting you, but when
the code says

Dim A, B As Double (assumes you want A and B to be doubles)

this actually is the equivalent of
Dim A as Variant, B as Double

You have to type each variable individually.

 

[Guest]

=sumif(Range,">0",Range)-Sumif(Range,"<0",Range)

doesn't need to be array entered.

 

[JohnJack]

Thanks Tom,

That will speed up the functions as well (I've learned a lot today).
The reason why I need these functions is that the company I work for
is now converting a lot of spreadsheets to excel, as quattro can't
handle (they crash a lot) the massive sized ones we require. The
other function we are missing and severely need is a linear
interpolation function (which again I can't find and no one seems to
be able to point me to one).

I have written the following function. It linearly interpolates a
value in the 2nd range (y1), by finding the the position of the two
values in the first range (y0) that surround (above and below) the "x"
value which can be a double. The problem is is that this function
really slows down the spreadsheet (it does work), but I was wonder if
anyone could point out places where I could possibly speed it up.

Function LinTerp(y0 As Range, y1 As Range, x As Double)
'this function will return the linear interpulated value corresponding
to the x value found in the y0 range (y0 range has to be in ascending
order)
'the range y0 has cannot be sinusoidal or parabolic as it will find
the first instance in the range
'that surrounds the value being searched for.

Dim VL As Double
Dim y0Num, y1Num, vlpos As Integer
Dim y0Below, y0Above, y1Below, y1Above, A, B, C As Double

y0Num = Application.Count(y0)
y1Num = Application.Count(y1)

If y0Num <> y1Num Then 'if the two ranges are not of the same
length, this function should stop as it will calculate the wrong
number
LinTerp = "Ranges Inconsistant"
Else
VL = Application.VLookup(x, y0, 1, True)

'binary search to find the position of VL (Vlpos) in y0 range
Dim foundFlag As Boolean
Dim first, middle, last As Integer
foundFlag = False

first = 1
last = y0Num

Do While (first <= last) And (Not foundFlag)
vlpos = Int((first + last) / 2)
Select Case y0(vlpos, 1)
Case VL
foundFlag = True
Case Is > VL
last = vlpos - 1
Case Is < VL
first = vlpos + 1
End Select
Loop

'end of binary search

y0Below = y0(vlpos, 1)
y0Above = y0(vlpos + 1, 1)
y1Below = y1(vlpos, 1)
y1Above = y1(vlpos + 1, 1)

A = y0Above - y0Below
B = x - y0Below
C = B / A

LinTerp = y1Below + (y1Above - y1Below) * C
End If

End Function

 

[Guest]

look at the forecast function

FORECAST(x,known_y's,known_x's)