[Solved] VBA Excel Problems and Questions [closed]

by


You say you want to ” look for exactly “5000” ” and then you look at xlPart in your code. If you want to match the value exactly you need to use xlWhole. More to your question, store the cell you find into a range variable and then reference off of that to replace.

Dim rng As Range
...
Set rng = WS.Columns(1).Find(what:=Search, Lookat:=xlWhole, MatchCase:=False)
rng.Offset(0, 4).Value = Replacement     ' Offsets 4 columns from found cell and sets value
...

If you need to find all instances, you could loop through continually shrinking your search range to one row below last find or just loop over the cells in that column without bothering with any Find functions.

Edit:
I added parentheses around the Find parameters. That should fix your compile error and make the above code work. Fixes it in Excel for me. As far as a loop, the following code steps through all used rows in Excel, checking the first column.

Dim i As Integer
...
For i = 1 To WS.UsedRange.Rows.count
    If WS.Cells(i, 1).Value = Search Then
        WS.Cells(i, 5).Value = Replacement
    End If
Next i

2

solved VBA Excel Problems and Questions [closed]