CODEWITHSUNDEEP
arraysperformanceexcelvbasearch
Ryski
Ryski

Reputation: 21

Excel VBA Search w/ Large Array

So I have found and modified a macro that fits my needs, however there is one limitation. I am building a macro to search medical payment data for specific diagnosis codes and procedure codes. In the project I am currently working on there are only 14 diagnosis codes, so I was able to put this directly into the VBA. However, there are over 800 procedure codes which I cannot fit into the VBA. I was able to do a seperate VBA step to bring in a table with this data, but I cant seem to get it set up to search against the table. But that being said, what is the best way to run this VBA search for such a large number of items?

Sub PROCEDURE_1_search()
Dim FirstAddress As String
Dim MySearch As Variant
Dim myColor As Variant
Dim Rng As range
Dim I As Long

MySearch = Array("412", "4100", "4101", "4102", "4103",...) <-- have over 800

  With Sheets("All Claims by Date of Service").range("G5:G55000")
    For I = LBound(MySearch) To UBound(MySearch)
       Set Rng = .Find(What:=MySearch(I), _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            FirstAddress = Rng.Address
            Do
                With ActiveSheet.range("B" & Rng.Row & ":O" & Rng.Row)
                    .Font.ColorIndex = 1
                    .Interior.ColorIndex = 4
                End With
                Set Rng = .FindNext(Rng)
            Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
        End If
    Next I
End With
End Sub

I might be coming up with an answer and not asking the right questions. Please let me know if there is anything I can clarify and thank you in advance for any assistance.

-Ryan

Upvotes: 2

Views: 3105

Answers (2)

brettdj
brettdj

Reputation: 55682

In your example you could use AutoFilter like this to highlight rows from columns B to O where G falls between 4101-4103 in a single shot (ie four criteria match a single conditon). A minor adaption would be to call this code block for different criteria such as a standaline 412 etc.

Sub Smaller()
Dim rng1 As Range
Set rng1 = Sheets("All Claims by Date of Service").Range("$G$5:$G$55000")
With rng1
   .AutoFilter Field:=1, Criteria1:=">=4100", Operator:=xlAnd, Criteria2:="<=4103"
       .Offset(0, -6).Resize(rng1.Rows.Count, 14).Font.ColorIndex = 1
       .Offset(0, -6).Resize(rng1.Rows.Count, 14).Interior.ColorIndex = 4
End With
Sheets(rng1.Parent.Name).AutoFilterMode = False
End Sub

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

For searching an array, I would recommend you to dump the data into a variant Array instead of iterating through ranges. That way it reduces the traffic of going back on forth on the code and sheet - specially formatting. Formatting is anyway expensive, in your case it seems to cost you a moon..

So here is how it goes by steps: (not the code - if you need a code take a look at these samples.).

  1. Transpose the data into a variant array
  2. Search as you desire in VBA code
  3. Dump the databack in the location (range)
  4. Format (range)

Upvotes: 2

Related Questions