ss,

Give the code below a try.

HTH,
Bernie
MS Excel MVP

Sub test()
Dim myArr(1 To 10) As Integer
Dim i As Integer
For i = 1 To 10
myArr(i) = i
Next i

'How to replace both upper and lower
ArrReplace myArr, 7, 3

For i = 1 To 10
If myArr(i) < i Then
MsgBox "When changing both, " & i & " was changed to " & myArr(i)
End If
Next i

'Reset Array
For i = 1 To 10
myArr(i) = i
Next i

'Reset just upper
ArrReplace myArr, 7

For i = 1 To 10
If myArr(i) < i Then
MsgBox "When changing upper, " & i & " was changed to " & myArr(i)
End If
Next i

'Reset Array
For i = 1 To 10
myArr(i) = i
Next i

'Reset just lower
ArrReplace myArr, , 3

For i = 1 To 10
If myArr(i) < i Then
MsgBox "When changing lower, " & i & " was changed to " & myArr(i)
End If
Next i

End Sub

Function ArrReplace(ByRef myVArr As Variant, _
Optional op1 As Variant, _
Optional op2 As Variant) As Variant
Dim i As Integer
For i = LBound(myVArr) To UBound(myVArr)
If Not IsMissing(op1) Then If myVArr(i) op1 Then myVArr(i) = op1
If Not IsMissing(op2) Then If myVArr(i) < op2 Then myVArr(i) = op2
Next i
End Function


"Bhupinder Rayat" wrote in message
...
HI all,

I want to write a function that takes an array with 2 optional values (e.g
op1 and op2) and returns an array with each value less than op2 replaced by
the op2 value, and each value higher than the op1 replaced by the op1 value.

Can anyone help?

Thanks in advance.

ss