Conditional replacing a certain value in a certain columns

[sprimax]

Hi folks,

Let's say I have a bunch of data with header from row #1-5, and the data to process starting from column E to AZ. How to replace whatever value with "YES", if the the character within each cell is more than 2 digit using VBA?

I am using MS Excel 2007.

Thanks in advance

 

[gsbelbin]

Try this

    Dim MyRange As Range
    Dim Cell As Range
 
    Set MyRange = Range("E1:AZ5")
    For Each Cell In MyRange
        If Len(Cell) > 2 Then
            Cell.Value = "YES"
        End If
    Next Cell

 

[sprimax]

Dear Gsbelbin,

Thanks for the code, it works. But it takes 2 minutes just to manage E6:AZ1000. Is there any possibilities to make it faster ?

Thank you

 

[gsbelbin]

Try using Application.ScreenUpdating = False/True like this

Dim MyRange As Range
    Dim Cell As Range
    
    Application.ScreenUpdating = False
    Set MyRange = Range("E1:AZ5")
    For Each Cell In MyRange
        If Len(Cell) > 2 Then
            Cell.Value = "YES"
        End If
    Next Cell
    Application.ScreenUpdating = True

 

[sprimax]

2 minutes, even though I added
Application.ScreenUpdating = False
" code "
Application.ScreenUpdating = True

 

[gsbelbin]

I've tried filling all those cells with data and run the code and it only takes a couple of seconds, and that's on a 9 year old PC What exactly is in the cells that are being updated? Do you have other formulas that use the contents of those cells? My guess is that there is something else going on that is causing the delay.

Edit
You could maybe try

Application.Calculation = xlCalculationManual

and

Application.Calculation = xlCalculationAutomatic

In place of the ScreenUpdating lines

 

[sprimax]

Hi Gsbelbin,

Thanks for your effort, it worked !. Problem solved.

Thank you very much