How to search for a value in a cell two rows over from first cell that meets criteria?

Question

I created a for each loop to search for cells that match a value in all my worksheets for the active workbook it works fine but I need to check the value on the second row to the right of the cell that matches. So if it matches in a2 it will need to check the contents of c2.

I don't know how to reference the adjacent cells to the match. I will like to be able to do something like. If cel.value like "boston*" and the cell in the same row two columns over like "mass*" then do whatever

 For Each ws In ActiveWorkbook.Worksheets

    ws.Activate


    For Each cel In rngToSearch.Cells

        With cel


            If cel.Value Like "boston*" Or cel.Value Like "manfield*" Or 
cel.Value Like "barnes*" Or cel.Value Like "langley*" Then


                Set vsoShape = 
Application.ActiveWindow.Page.Drop(Application.DefaultRectangleDataObject, 
aoffset, boffset)

                vsoShape.Text = cel.Value


            Else

            End If



        End With

    Next cel
    Next ws

I need to be able to check the value of the cell two rows over from the match if the match is in a2 I will need to also be able to check c2. Thanks

Use `Offset`. So you could try something like: `cel.Offset(2,0).Value`. Just as a tip, you don't need to use things like `Activate` or `Select`. Have a read on why we shouldn't use these in VBA — Zac, Jul 08 '19 at 12:35
When using `With cel` you can omit the `cel` in the enclosed lines, e.g. `If .Value Like "boston*" Or .Value Like "manfield*" Or .Value Like "barnes*" Or .Value Like "langley*" Then [...]` — Horst, Jul 08 '19 at 12:55
Have a look at [this post](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It should help to explain.. hopefully — Zac, Jul 08 '19 at 12:56
@Zac Thanks again Zac I tried removing the ActiveWorkbook as in the post and created a variable for the workbook and it didn't work so I changed back my loop just find the first two values it looks like is iterating thru the worksheets but something is wrong but when I created the variable for the workbook it wouldn't do nothing it gives me error 438 Object doesn't support this property or method — Michael Ziegler, Jul 09 '19 at 15:23

score 0 · Accepted Answer · answered Jul 08 '19 at 13:34

0

You may need this:

With cel
        If (.Value Like "boston*" Or _
            .Value Like "manfield*" Or _
            .Value Like "barnes*" Or _
            .Value Like "langley*") _
         And .Offset(0, 2).Value Like "mass*" Then

answered Jul 08 '19 at 13:34

AcsErno

1,597
1
7
10

Thanks that works perfect i just found out that my loop just find the first two values it looks like is iterating thru the worksheets but something is definitely wrong. Again Thanks your answer works perfect. – Michael Ziegler Jul 09 '19 at 14:56
@MichaelZiegler Welcome. Then please accept my answer by clicking on the 0 to increase my reputation. Thank you. – AcsErno Jul 10 '19 at 06:56

How to search for a value in a cell two rows over from first cell that meets criteria?

1 Answers1