Macro Help

[tmill29]

I have one workbook that contains two sheets. Sheet one has 17 columns and 100k rows. An example of what a row and columns looks like from sheet one:

provcpt1cpt2cpt3cpt4cpt5cpt6cpt7cpt8cpt9cpt10cpt11cpt12cpt13cpt14john doe167L8641101672193301933115172216715000232t84.5536380


Sheet two contains 1 column with 226 various numbers. Example:

PROC_CD0010 0048T 0051T 0099T 0154T 0285T 0694 0695 0745 1192 11980 11981 11982 11983 138


I want the my macro to take the 226 proc cds and look for them on sheet 1 and if they find a proc cd within any column and row, highlight the entire row. The code I have is:

Sub HighlightRowsWithMyNumbers()
Dim R As Range, vA As Variant, i As Long, j As Long, k As Long, mynums As Variant
mynums = Sheets("sheet2").Range("a2:a226").Value
Set R = ActiveSheet.UsedRange
vA = R.Value
Application.ScreenUpdating = False
For i = LBound(mynums, 1) To UBound(mynums, 1)
For j = LBound(vA, 1) To UBound(vA, 1)
For k = LBound(vA, 2) To UBound(vA, 2)
If vA(j, k) = mynums(i) Then
R.Cells(j, k).EntireRow.Interior.Color = vbYellow
Exit For
End If
Next k


Next j
Next i
End Sub


When I run it, the response is subscript our of range error.

[BobBridges]

I hate the way this forum loses indentation. Well, let's take a look at this. va=R.Value creates a two-dimensional array and places all the UsedRange values into it, right? Then you start up a three-level loop and...

Wait, maybe I see the problem. Sheet2 contains, you said, one column and multiple rows, right? The statement "mynums = Sheets("sheet2").Range("a2:a226").Value" creates a two-dimensional array, but later on you're referring to "mynums(i)"; don't you have to refer to "mynums(i,1)"?

Also I think you're going to have to move the "End If" statement up one line, to be inside the For...Next group.

Oh, by the way, another time this question should probably be posted to the Excel Programming area, rather than this one.

[tmill29]

This did not work. The macro did not error but it did not do what it was inteneded to do. It is supposed to look at the value on sheet2 and see if any of those appear in sheet1 and then highlight the entire row on sheet1 if the values appear

[BobBridges]

You're probably new to this, so I won't give you a hard time about it, but you're going to have to do a lot better than that when reporting problems. "It didn't work" isn't nearly enough information for others to figure out what's going wrong. To start with, what did it do (and what did you do first)—and I mean exactly what did it do? Details, man, details!

But one of the nice things about VBA in Excel is that you can step through the program and watch what it does one statement at a time. Try this: In the VBA Editor (where you're looking at your program, not at the Excel worksheet), look at the menu bar and select Debug. In the drop-down list notice some of your options: You can hit <F8> ("Step Into") to execute just the next statement in your program. You can hit <Shift-F8> to do the same thing, only if it's a call to a subroutine it'll execute the whole subroutine and stop at the next statement. (Try it and you'll see what I mean.) You can hit <F5> just to run the program in the normal way. And so on.

Now exit the drop-down menu, and while looking at your program hit <F8>. The first line of the program will light up in yellow; hit <F8> again and it'll move to the next statement, and so on one statement at a time. At any point you can mouseover a variable and see what the value is, if it's scalar; if it's an object you can "Add Watch" for that object to see all its properties displayed in a special Watch window. This is invaluable in seeing exactly what your program is doing at each point in the program.

If you've never done this before, feel free to ask questions to help you understand what it's doing and how you can use it.

[Catalin.B]

tmill29, please upload a sample file with your data structure, in those 2 sheets, it will increase your chances to receive an answer...
The following code might do what you need, but is not tested on your data:

Sub HighlightRowsWithMyNumbers()
Dim cell As Object
Dim i As Integer

Application.ScreenUpdating = False
    On Error Resume Next
  
For Each cell In Sheets("sheet1").UsedRange
    i = Application.Match(cell, Sheets("sheet2").Range("a2:a226"), 0)
        If Not Err.Number <> 0 Then
             Sheets("Sheet1").Rows(cell.Row).EntireRow.Interior.Color = vbYellow
        End If
    Err.Clear
Next cell
    On Error GoTo 0
Application.ScreenUpdating = True
End Sub