• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to find and replace data

Eloise T

Active Member
Please see the attached sanitized spreadsheet.

I need a macro that will scan down Column J looking for the word “Canceled” and replace (in the same row) whatever may be in Columns E and F with “N/A”

If "N/A" is already there, skip to the next occurrence of "Canceled" until all rows have been examined.

The actual spreadsheet is over 50,000 rows and gets deeper each week.

Thank you in advance!
 

Attachments

  • Chandoo - Demo.xlsm
    38.5 KB · Views: 24
According to your attachment an Excel basics formula demonstration as a starter :​
Code:
Sub Demo1()
    R = Cells(Rows.Count, 10).End(xlUp).Row
    If R > 4 Then Range("E5:F" & R).Value2 = Evaluate("IF(J5:J" & R & "=""Canceled"",""N/A"",E5:F" & R & ")")
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
My revamped demonstration reducing the range between the first and last "Canceled"​
to paste to the worksheet module :​
Code:
Sub Demo1r()
        Const C = "Canceled", F = "IF(J#:J¤=""" & C & """,""N/A"",E#:F¤)"
    With Application
        V = .Match(C, Me.UsedRange.Columns(10), 0):  If IsError(V) Then .Speech.Speak C & " not found!": Exit Sub
        R = Me.UsedRange.Columns(10).Find(C, , xlValues, xlWhole, , xlPrevious).Row
        [E:F].Rows(V & ":" & R).Value2 = Evaluate(Replace(Replace(F, "#", V), "¤", R))
       .Speech.Speak "Done", True
    End With
End Sub
You may Like it !​
 
Code:
Sub blah() 'works on the active sheet:
Range("E5").CurrentRegion.Offset(, 2).AutoFilter Field:=8, Criteria1:="Canceled"
Set myrng = ActiveSheet.AutoFilter.Range
Intersect(myrng, myrng.Offset(1), Range("E:F")).SpecialCells(xlCellTypeVisible).Value = "N/A"
myrng.AutoFilter
End Sub
 
What I dislike with SpecialCells is it crashes when nothin' matches so better is to check first with Match :​
Code:
Sub Demo2()
        Const C = "Canceled"
    With Application
        If IsNumeric(.Match(C, ActiveSheet.UsedRange.Columns(10), 0)) Then
            .ScreenUpdating = False
        With Range("E4", Cells(Rows.Count, 10).End(xlUp)).Rows
            .AutoFilter 6, C
            .Item("2:" & .Count).Columns("A:B").SpecialCells(xlCellTypeVisible).Value2 = "N/A"
            .AutoFilter
        End With
            .ScreenUpdating = True
        End If
    End With
End Sub
 
Another dislike with SpecialCells is it is size limited so not always the way to go with big data …​
 
Yes, it's limited to 2,147,483,648 cells (I think areas actually) so if it's cells, that's 1073741824 rows of @Eloise T 's spreadsheet which seems to be more than 1000 times the number of rows on a spreadsheet - I suspect it will cope with 50,000 or 500,000 rows…
And it's true that it it finds nothing it could crash, so:
Code:
Sub blah() 'works on the active sheet:
Dim rngNA As Range
Range("E5").CurrentRegion.Offset(, 2).AutoFilter Field:=8, Criteria1:="Canceled"
Set myrng = ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rngNA = Intersect(myrng, myrng.Offset(1), Range("E:F")).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rngNA Is Nothing Then rngNA.Value = "N/A"
myrng.AutoFilter
End Sub
 
Last edited:
So it seems the limit was raised as I remember fewer cells (I met the issue with older Excel versions) …​
 
Demo2 revamped still using a filter but without SpecialCells
(as far I can remember the issue came from the number of areas returned ) :​
Code:
Sub Demo2r()
        Const C = "Canceled"
    With Application
        If IsNumeric(.Match(C, ActiveSheet.UsedRange.Columns(10), 0)) Then
            .ScreenUpdating = False
             [E5].Copy [J1]
             [J1].Value2 = "N/A"
        With Range("E4", Cells(Rows.Count, 10).End(xlUp)).Rows
            .AutoFilter 6, C
             [J1].Copy .Item("2:" & .Count).Columns("A:B")
            .AutoFilter
        End With
             [J1].Clear
            .ScreenUpdating = True
        End If
    End With
End Sub
You should Like it !​
 
Demo2 revamped still using a filter but without SpecialCells
(as far I can remember the issue came from the number of areas returned ) :​
Code:
Sub Demo2r()
        Const C = "Canceled"
    With Application
        If IsNumeric(.Match(C, ActiveSheet.UsedRange.Columns(10), 0)) Then
            .ScreenUpdating = False
             [E5].Copy [J1]
             [J1].Value2 = "N/A"
        With Range("E4", Cells(Rows.Count, 10).End(xlUp)).Rows
            .AutoFilter 6, C
             [J1].Copy .Item("2:" & .Count).Columns("A:B")
            .AutoFilter
        End With
             [J1].Clear
            .ScreenUpdating = True
        End If
    End With
End Sub
You should Like it !​
There is one minor problem with this version. It picks up the "background highlight color" of cell E5 and propagates that across the "matches" as seen below. ...First picture below is cell E5, the next picture shows cells >36,000 rows deep with same background highlight color as cell E5.
What needs to be done to turn that off so that it assumes the background highlight color of the cell(s) it's putting "N/A" in? e.g. if it's white, keep it white. If it's yellow, keep it yellow, etc.

Thanks.

74360


1620050688195.png

Thanks.
 
Last edited:
I'd go back to using Specialcells, then you can just assign the value you want to the visible cells.
 
There is one minor problem with this version. It picks up the "background highlight color" of cell E5 and propagates that across the "matches" as seen below
This minor problem does not exist in your attachment so difficult to foresee !​
Ok for SpecialCells if not using Excel 365 version as since last month I have seen some issues on some forums …​
Or attach a 'color' workbook for further tests …​
 
Some Excel 365 versions have issues with SpecialCells, like the old issue is back !​
As the same workbook executing the same VBA procedure well works on different Excel 'classic' versions but not on some 365 version​
and was solved for a particular thread just removing SpecialCells, the reason of my warning …​
Some other specific glitches under some 365 version which can't be reproduced on 'classic' versions.​
 
Some Excel 365 versions have issues with SpecialCells, like the old issue is back !
Some other specific glitches under some 365 version which can't be reproduced on 'classic' versions.
Be specific!
Links?
Examples?
solved for a particular thread just removing SpecialCells
What thread?
Without these, I'd recommend @Eloise T follow @Debaser 's advice in msg#12, and in the event problems arise we'll sort them.

@Eloise T , did you have a problem with the likes of the code in msg#7
 
I did not save any link but since April I have seen some threads about issues with SpecialCells​
and like in the old time just with missing data in the result and​
I just noticed it was only under 365 Excel version with big data, certainly with more than 8 192 areas in the result …​
 
Be specific!
Links?
Examples?
What thread?
Without these, I'd recommend @Eloise T follow @Debaser 's advice in msg#12, and in the event problems arise we'll sort them.

@Eloise T , did you have a problem with the likes of the code in msg#7
p45cal, et. al.
The following version written by Marc L, works the fastest; however, it intermittently leaves a string of "zeros" past the data in Columns E and F for about 3200 rows [see picture below]. Sometimes it does it, sometimes not.

>>> use code - tags <<<
Code:
Sub Marc_L()    'Written by Marc L
'This sub looks for "Canceled" in Column J,  If so, then checks for missing "N/A" in Columns E & F.  If missing, replaces whatever is there with N/A.

    R = Cells(Rows.Count, 10).End(xlUp).Row      ' I believe "10" could also have been a "J" for Column J.
    If R > 4 Then Range("E5:F" & R).Value2 = Evaluate("IF(J5:J" & R & "=""Canceled"",""N/A"",E5:F" & R & ")")  

' IF(J5:J varR = Canceled then replace with N/A in E5:F varR)

End Sub
Any way those zeros can be prevented?

74390
 
Last edited by a moderator:
Any way those zeros can be prevented?
Ensure there's nothing in column J below the bottom of your table.

If you can't lose what's in column J below the bottom of the table, choose a different column to set R, eg.:
R = Cells(Rows.Count, "B").End(xlUp).Row

or if there's likely to be something below the table, perhaps determine R differently, eg.:
Code:
With Range("A5").CurrentRegion
  R = .Cells(.Cells.Count).Row
End With
but make sure there's at least one completely clear row below the table.
 
Last edited:
Back
Top