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!