Setting range in vba based on vlookup criteria

Hi,

Consider the following Table;

----- A------------B
1---Blue ----------23
2---Black----------45
3---Grey ---------32
4---Pink ----------60

I set range as follows;

Set rngData = .Range("B3").Resize(1, Sheet1.Range("E5"))

I am just concerned with red part of above code. Istead of manually setting it as B3 I want it to be a cell in column B in front of "Grey" in column A, thus it would automatically become B3. It is something like vlookup.

I hope I explained my problem for your understanding, but if not, kindly let me know so I may redraft my problem.

Thank you all

Try something like this...

Set rngData = .Range("A:A").Find(What:="Grey", LookIn:=xlValues, LookAt:=xlWhole, _
               SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rngData Is Nothing Then
    Set rngData = rngData.Offset(, 1).Resize(1, Sheet1.Range("E5"))
Else
    MsgBox "No match for 'Grey' found in column A. ", , "No Match Found"
End If

Originally Posted by caabdul

AlphaFrog

Thank you for help, but I am a bit confused. Is MsgBox necessary? because there will be no chance that "Grey" is not in column A.
Can you please trim this code.

Set rngData = .Range("A:A").Find(What:="Grey", LookIn:=xlValues, LookAt:=xlWhole, _
               SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
               .Offset(, 1).Resize(1, Sheet1.Range("E5"))

I face this error:

Run_time Error '91':
Object variable or with block variable not set

That's what the If statement was for; test for no match.

Does the cell with Blue have a trailing space; e.g. "Blue "? If yes, you could change LookAt:=xlPart, but that would match any cell that contains Blue e.g.; Bluejay.

You changed the MatchCase:=True. Does the case match exactly?

Do you have a Sheet34 in the same workbook?

Sheet34.Range("E5")

What value is in Sheet34.Range("E5") ?

It should be H1 not H12

Sheet1.Range("H12")

Create a name range for C6:C14 (called MyRange in this example)

Reference the named range in the code
Range("MyRange")

If you insert a row within the named range, it will automatically adjust.

You could do the same with a Table.