Find a large a code

[Andresuru]

Hi Everyone,

Please note I have been using a VBA code to find small codes (between 8 and 25 characters) in order to find them and paint them in a specific color.
But I have a situation I had to concatenate some cells and now my code to find can have more than 70 characters as one value, vba code is taking the whole life to do it.

Does anybody knows a code that can find large values fast ? In a table with more than 50 thousand lines and more than 50 columns populated. Usually I need to find 300 values into the big table (50.000 records)

Example:

12GALAllAllAllAllDISCARDS CCAllAllAllAllAllAllAllAllAll999912

Reference: I was trying to do the same code but the thing is the value to find is triple larger and it takes a lot of time

Sub ADDSCRA()

Application.ScreenUpdating = False

Sheets("Master Hierarchy").Select
 'Logica de buscar duplicados

    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "@"
    
    Dim W As Integer
    Dim ID
    Dim X
    Dim i As Integer
    
    Dim Last_row As Long
        Last_row = Cells(Rows.Count, 1).End(xlUp).Row + 1
   

    For i = 2 To Last_row

    On Error Resume Next

        
        If Sheets("Master Hierarchy").Range("A" & i).Value = "ADD" Then

        Application.Wait (Now + TimeValue("0:00:01") / 10)

        W = i


        ID = Sheets("Master Hierarchy").Range("E" & W).Value

        X = Empty

        X = Worksheets("System").Columns(3).Find((ID), LookIn:=xlValues, LookAt:=xlWhole).Row

        If X <> Empty Then

 
            If Sheets("Master Hierarchy").Range("E" & W).Value = Sheets("System").Range("C" & X).Value Then
   
                Sheets("Master Hierarchy").Select
                Range("E" & W).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
  
            End If
        End If
        End If
 
    Next i

End Sub

Appreciate your help

 

[kevin9999]

You might find this article interesting - Excel VBA - Efficient way to colorize a large number of cells. Seems to be a similar issue to what you're facing?

 

[Peter_SSs]

Wondering if Conditional Formatting would be a feasible option for you?
You could try this with a copy of your workbook. (I have had to guess a bit about some of the formats & data)

Sub ADDSCRA_2()
  Application.ScreenUpdating = False
  With Sheets("Master Hierarchy").Columns("E")
    .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(0, 2)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(A1=""ADD"",ISNUMBER(MATCH(E1,System!C:C,0)))"
    .FormatConditions(1).Interior.Color = 255
  End With
  Application.ScreenUpdating = True
End Sub

 

[kevin9999]

As an alternative, and in answer to your original question: "Does anybody knows a code that can find large values fast ?" I'll offer the following demonstration that does something similar to what your doing. My test data was 50 columns x 50K rows (2.5M cells) looking for the value: 12GALAllAllAllAllDISCARDS CCAllAllAllAllAllAllAllAllAll999912 and wherever found - make that cells interior colour 255. I used 100K instances of the value in the data. It took ~ 2.75 seconds to run.

Option Explicit
Sub abTest()
    Dim t As Double: t = Timer
    Dim val As String
    val = "12GALAllAllAllAllDISCARDS CCAllAllAllAllAllAllAllAllAll999912"
    Application.ScreenUpdating = False
   
    With Application.ReplaceFormat
        .Interior.Color = 255
    End With
   
    Sheet1.Range("A2:AX50001").Replace what:=val, replacement:=val, ReplaceFormat:=True
    Application.ScreenUpdating = True
    MsgBox Timer - t
End Sub