Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
Thanks a ton. Works great.
Cheers, Jack On Feb 14, 12:08 pm, "Bob Phillips" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
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)). On Feb 14, 12:20 pm, "Don Guillett" wrote: I don't understand what you are trying to do? -- Don Guillett SalesAid Software "JohnJack" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
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. -- Regards, Tom Ogilvy "JohnJack" wrote: Thanks a ton. Works great. Cheers, Jack On Feb 14, 12:08 pm, "Bob Phillips" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
=sumif(Range,"0",Range)-Sumif(Range,"<0",Range)
doesn't need to be array entered. -- Regards, Tom Ogilvy "JohnJack" wrote: 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)). On Feb 14, 12:20 pm, "Don Guillett" wrote: I don't understand what you are trying to do? -- Don Guillett SalesAid Software "JohnJack" wrote in message oups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range - how to determine
look at the forecast function
FORECAST(x,known_y's,known_x's) -- Regards, Tom Ogilvy "JohnJack" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range | Excel Programming | |||
Subscript Out of Range | Excel Programming | |||
Subscript Out of Range | Excel Programming | |||
subscript out of range | Excel Programming | |||
Subscript out of range | Excel Programming |