Not to take away from Claus' offering!
In my usual approach to avoid read/write directly from/to worksheets...
Sub FindReplace()
Dim vVals, vRng, sMsg$, n&, lLastRow&, lOffset&
Dim rngSource As Range
sMsg = "Please enter the label of column to search on, " _
& "the substring to search for, " _
& "the label of column to amend, " _
& "the lower value, " _
& "the upper value the value to amend semocolon-separated"
vVals = _
Split(Application.InputBox(sMsg, "Enter Conditions", Type:=2), ";")
'Validate input
' If user cancels OR returns an empty string
' OR If missing args
If UBound(vVals) < 5 Then Exit Sub
On Error GoTo ErrExit
lLastRow = Cells(Rows.Count, vVals(0)).End(xlUp).Row
Set rngSource = Range(Cells(1, vVals(0)), Cells(lLastRow, vVals(2)))
vRng = rngSource: lOffset = UBound(vRng, 2)
For n = LBound(vRng) To UBound(vRng)
If InStr(vRng(n, 1), vVals(1)) 0 Then
If vRng(n, lOffset) CDbl(vVals(3)) _
And vRng(n, lOffset) < CDbl(vVals(4)) _
Then vRng(n, lOffset) = CDbl(vVals(5))
End If
Next 'n
rngSource = vRng
ErrExit:
Set rngSource = Nothing
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion