This is the full code below... it was my solution to my previous post that
nobody was able to help me with. In a nutshell, I define data arrays from
ranges on my excel sheet. Then I go about shifting the data 1 row down,
calculate this lambda value, and 1compare to the engine lambda by the
correlation function. I look for the shift that maximizes the correlation
function. The problem is, if I allow 10 or more shifts in data, it's
(10+1)^3 calculations that need to happen to fill my arrCorrel array. By far
the longest process is calculating the lambda value. Would pasting the
calculated lambda somewhere to do the calculations be faster?

Thanks,
Matt

Function DataAdj(LastRow As Double)

LR = LastRow - 12
Application.DisplayAlerts = True
NumShifts = 10

ReDim arrCorrel(1 To (NumShifts + 1) ^ 4)
ReDim arrCO(1 To LR)
ReDim arrNOx(1 To LR)
ReDim arrHC(1 To LR)
ReDim arrO2(1 To LR)
ReDim Lambda_calc(1 To LR)
ReDim Lambda_engine(1 To LR)
ReDim arrayA(1 To LR)
ReDim arrayB(1 To LR)
ReDim arrayC(1 To LR)
ReDim arrayD(1 To LR)
ReDim arrayE(1 To LR)
ReDim arrayF(1 To LR)
ReDim arrLambda(1 To LR)

arrCO = Application.Transpose(Range("G13:G" & LastRow))
arrHC = Application.Transpose(Range("F13:F" & LastRow))
arrNOx = Application.Transpose(Range("J13:J" & LastRow))
arrO2 = Application.Transpose(Range("K13:K" & LastRow))
Lambda_engine = Application.Transpose(Range("O13:O" & LastRow))

Count = 1
MaxValue = 0

For i = 0 To NumShifts
For j = 0 To NumShifts
For k = 0 To NumShifts
For l = 0 To NumShifts
For ii = 1 To LR - NumShifts

arrayA(ii + i) = arrCO(ii + i) / 10 ^ 4
arrayB(ii + j) = arrHC(ii + j) / 10 ^ 4
arrayC(ii + k) = arrNOx(ii + k) / 10 ^ 4
arrayD(ii + l) = arrO2(ii + l) / 10 ^ 4
arrayE(ii) = 4 / 3 * arrayA(ii + i) + 18 * arrayB(ii
+ j) - (2 * arrayD(ii + l) + arrayC(ii + k))
arrayF(ii) = 0.5 * arrayC(ii + k) + arrayD(ii + l) -
(2 / 3 * arrayA(ii + i) + 9 * arrayB(ii + j))

If arrayF(ii) = 0 Then
arrLambda(ii) = 1 + arrayF(ii) * 2 / (1 + 0.25 *
1.9 - 0.5 * 0.02) / (100 - 4.79 * arrayF(ii))
Else
arrLambda(ii) = 1 - arrayE(ii) * 2 / (1 + 0.25 *
1.9 - 0.5 * 0.02) / (200 + 3.79 * arrayE(ii))
End If

If arrLambda(ii) 1.524 Then
arrLambda(ii) = 1.524
Else: End If

Next ii

With Application.WorksheetFunction
arrCorrel(Count) = .Correl(.Index(arrLambda, 0),
..Index(Lambda_engine, 0))
End With

If arrCorrel(Count) MaxValue Then
index_i = i
index_j = j
index_k = k
index_l = l
MaxValue = arrCorrel(Count)
Else: End If

Count = Count + 1

Next l
Next k
Next j
Next i

If index_i 0 Then
For i = 1 To index_i
Range("G13").Delete Shift:=xlUp
Next i0
End If
Range("G7").Value = index_i

If index_j 0 Then
For i = 1 To index_j
Range("F13").Delete Shift:=xlUp
Next i
End If
Range("F7").Value = index_j

If index_k 0 Then
For i = 1 To index_k
Range("I13:J13").Delete Shift:=xlUp
Next i
End If
Range("I7").Value = index_k

If index_l 0 Then
For i = 1 To index_l
Range("K13").Delete Shift:=xlUp
Next i
Range("K7").Value = index_l
End If

Range("J1").Value = MaxValue
MaxIndex = Application.WorksheetFunction.Max(index_i, index_j, index_k,
index_l)
LastRow = LastRow - MaxIndex


End Function



"Rick Rothstein" wrote:

The answer is "no" since you are changing the value of the elements.
However, if this is some intermediate step where the values have come from
or are going to be placed on a worksheet, you can eliminate the loop in
favor of a simple PasteSpecial operation; but you will need to tell us more
about what you are doing before we can offer code to you for one of those
situations.

--
Rick (MVP - Excel)


"Matt S" wrote in message
...
I have this code right now:

For ii = 1 to 100
arrayA(ii) = arrayB(ii) / 10^4
Next ii

Is there a way to do this calculation faster? Maybe without cycling thru
all elements of arrayA?

Thanks,
Matt

.