Excel VBA Search w/ Large Array

Question

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

So right now what is the problem you are having? Does your code work but it is slower than you want or what specific errors are you getting? — Dan Metheus
– Dan Metheus, Commented Feb 27, 2013 at 0:28
Your problem isn't the size of the array, 800 is fine, it's the massive amount of rows (55000) that you're performing a single search against 800 times. — StoriKnow
– StoriKnow, Commented Feb 27, 2013 at 3:21
Actually it's more likley to be the total amount of results that is the issue rather than than the area. You are formatting each match for 800 codes on each individual match - so for say 10 finds per code you are indivdually formatting 8000 rows. And some rows probably will overlap. I would use either a) Union and do a single format at the end b) Use autofilter on your range — brettdj
– brettdj, Commented Feb 27, 2013 at 3:46
@Ryski What's the meaning of this? However, there are over 800 procedure codes which I cannot fit into the VBA. — bonCodigo
– bonCodigo, Commented Feb 27, 2013 at 3:48
@bonCodigo I'm relatively new to VBA and I have 800 procedure codes to search for in a given sheet. Origionally i was trying to figure out how to put line breaks into the VBA and hard code all 800 procedure codes into the macro itself. — Ryski
– Ryski, Commented Feb 27, 2013 at 16:17

Community · Accepted Answer · 2017-05-23 11:56:49Z

2

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.).

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

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Feb 27, 2013 at 3:57

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brettdj · Accepted Answer · 2013-02-27 04:13:08Z

1

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

answered Feb 27, 2013 at 4:13

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Collectives™ on Stack Overflow

Excel VBA Search w/ Large Array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related