[Nigel Brown[_3_]]

Hi Leo,
In answer to:
Would
Range("p2").Value = temp2 - temp
by any chance be
Range("p2").Value = temp - temp2

Yes sorry that was a typo.

As for your second query, I need to explain that the sequence of numbers
should never increase so I will change the sequence you wrote for one
actually in my data set and the required result
For the numbers:

J2: 1050

M2:Q2 = 700,700,350,350,0
The resulting sequence in M2:Q2 would be
350,0,350,0,350

I thinking that I can use a test for zero to stop the loop.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Leo Heuser[_2_]]

Hi Nigel

It looks as if the system is:
For the first cell: New value = J2-FirstCell.Value
For all other cells. New value = Value in cell to the left - cellValue.

If this is correct here's one way to do it:

The ranges are put into two arrays (FixedRangeValue and CheckRangeValue)
and the calculation is performed inside these arrays.
At last the array CheckRangeValue is returned to the original range.

Putting ranges into arrays like this is a very efficient and
very fast way of solving this kind of problems.

I don't use any temp variables. This isn't necessary,
because I make the alterations from right to left (AN to M),
instead of from left to right. (Step -1)

On error catches the situation, where there is no left cell,
i.e. the cells in column M. In this situation the value from
FixedRangeValue is picked instead.



Sub NewSequence()
'Leo Heuser, 16-7-2003
Dim CheckRange As Range
Dim CheckRangeValue As Variant
Dim FixedRange As Range
Dim FixedRangeValue As Variant
Dim lColumn As Long
Dim lRow As Long

Set FixedRange = Range("J2:J3000")
FixedRangeValue = FixedRange.Value

Set CheckRange = Range("M2:AN3000")
CheckRangeValue = CheckRange.Value

For lRow = 1 To UBound(CheckRangeValue, 1)
On Error Resume Next

For lColumn = UBound(CheckRangeValue, 2) To 1 Step -1
CheckRangeValue(lRow, lColumn) = _
CheckRangeValue(lRow, lColumn - 1) - _
CheckRangeValue(lRow, lColumn)
If Err.Number 0 Then
CheckRangeValue(lRow, lColumn) = _
FixedRangeValue(lRow, 1) - _
CheckRangeValue(lRow, lColumn)
On Error GoTo 0
End If
Next lColumn
Next lRow

Range("M2:AN3000").Value = CheckRangeValue

End Sub


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.

"Nigel Brown" skrev i en meddelelse
...
Hi Leo,
In answer to:
Would
Range("p2").Value = temp2 - temp
by any chance be
Range("p2").Value = temp - temp2

Yes sorry that was a typo.

As for your second query, I need to explain that the sequence of numbers
should never increase so I will change the sequence you wrote for one
actually in my data set and the required result
For the numbers:

J2: 1050

M2:Q2 = 700,700,350,350,0
The resulting sequence in M2:Q2 would be
350,0,350,0,350

I thinking that I can use a test for zero to stop the loop.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!