Find and replace every next match +1 in excel 2003/2007

ralfs_k · 03-11-2009, 06:54 AM

I have several very huge excel files and was wondering is there a way to find something for example number 200 and every next match will be replaced like:

find 200 replace with 300
200 > 301
200 > 302
200 > 304

etc...

data sorting is not an option because of marged cells, inserted pictures and other crap...

David M58 · 03-11-2009, 04:15 PM

One way to do it is with a VBA macro.

Code:

Sub replace()
    Dim search_range As Range
    Dim search_cell As Range
    Dim replace_value As Long
    Set search_range = Range("A1:A30")
    replace_value = 299
    For Each search_cell In search_range
        If search_cell.Value = 200 Then
            replace_value = replace_value + 1
            search_cell.Value = replace_value
        End If
    Next search_cell
    Set search_range = Nothing
End Sub

The above code searches the cells in the range A1:A30 for a cell containing the value 200, and replaces the first matching cell with 300, the next matching cell with 301 and so forth.

The above example searches only one column (column A in this case). If you want the searched range to include more than one column, it will be necessary to modify the code.

ralfs_k · 03-12-2009, 07:58 AM

tnx you helped me alot =]

03-11-2009, 06:54 AM	#1 (permalink)
ralfs_k Registered User Join Date: Dec 2007 Location: Latvia Posts: 49 OS: WinXpSp2 / Win2k3 / RHEL5	Find and replace every next match +1 in excel 2003/2007 I have several very huge excel files and was wondering is there a way to find something for example number 200 and every next match will be replaced like: find 200 replace with 300 200 > 301 200 > 302 200 > 304 etc... data sorting is not an option because of marged cells, inserted pictures and other crap...

03-11-2009, 04:15 PM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Find and replace every next match +1 in excel 2003/2007 One way to do it is with a VBA macro. Code: Sub replace() Dim search_range As Range Dim search_cell As Range Dim replace_value As Long Set search_range = Range("A1:A30") replace_value = 299 For Each search_cell In search_range If search_cell.Value = 200 Then replace_value = replace_value + 1 search_cell.Value = replace_value End If Next search_cell Set search_range = Nothing End Sub The above code searches the cells in the range A1:A30 for a cell containing the value 200, and replaces the first matching cell with 300, the next matching cell with 301 and so forth. The above example searches only one column (column A in this case). If you want the searched range to include more than one column, it will be necessary to modify the code.

03-12-2009, 07:58 AM	#3 (permalink)
ralfs_k Registered User Join Date: Dec 2007 Location: Latvia Posts: 49 OS: WinXpSp2 / Win2k3 / RHEL5	Re: Find and replace every next match +1 in excel 2003/2007 tnx you helped me alot =]