Search named range by row matching to a value in a column

[DougiePh]

I have a named range "TAB" (B8:K26) in several worksheets of a work book. I am a retired want IT professional, new to VBA, and view the range like a file with the rows as records and columns as fields. I want to loop through the rows examining column D checking for an exact match to a value I supply. I then want to pass back the value in column J of the row to the worksheet. I have looked at many sites for the answer but none seem to have what I need. I know this can be done with VLOOKUP, however, I am currently doing this with the entire range and don't want to define another range that is a portion of the larger range. I have tried several FOR EACH scenarios but always get a VALUE error. I'm not sure what I am doing wrong or missing.

For Each n In WorkSheet(SheetName).Range("Tab")
if Worksheet(SheetName).Range("Tab").ColumnD = MyValue
Then
GetValue = WorkSheet(SheetName).Range("Tab").CilumnJ
END IF
NEXT n

I know the above doesn't work but shows what I am trying to do.

I am passing the SheetName and MyValue to the function GetValue.

 

[steve the fish]

You wouldnt mix up using column D and a named range. I take it you want to search in the 3rd column of the named range for MyValue? Try this:

Function GetValue(myValue As String, sheetname As String) As String

Dim c As Range

For Each c In Application.Index(Sheets(sheetname).Range("Tab"), 0, 3)
    If LCase(c.Value) = LCase(myValue) Then
        GetValue = c.Offset(0, 6)
        Exit For
    End If
Next

End Function

 

[DougiePh]

Thanks Steve

That worked. Didn't think of using the INDEX function. Shows what a newbie I am at this. In my past life I would use FOR EACH to loop through records in a file and select them based on some field criteria. I made a couple of changes to make the 3 and 6 variable names so I could add comments explaining what was being selected. Must be the old programmer still in me. I also created another function to select another field in the table.

Again, many many thanks.
Doug