CODEWITHSUNDEEP
excelvba
Don Desrosiers
Don Desrosiers

Reputation: 143

Macro to select one cell if two other cells match

I need a Macro (must be a macro) that will select a cell if - and only if - two cells match. =IF(a3=k8) select k9. A3 is static, but the matching data can be anywhere along a row range. Therefore, I need to look for the data in a3 in the range k8:bz8, and in every case, follow by selecting the cell directly below it. Basically HLOOKUP. Once that cell is selected i will call another macro to populate that cell. I am using Office 2016 for Mac (which sux) Thanks Don

Upvotes: 1

Views: 304

Answers (2)

user4039065
user4039065

Reputation:

Presumably you shouldn't have any trouble with the WorksheetFunction object using HLOOKUP function with a wildcard search.

    dim val as variant
    with activesheet
        on error goto no_match
        val = worksheetfunction.hlookup(chr(42) & .range("a3").value & chr(42), .range("k8:bz9"), 2, false)
        on error goto 0
    end with
    debug.print val
no_match:
    if err.number = 1004 Then _
        debug.print "no match"

Upvotes: 2

Adam Belnap
Adam Belnap

Reputation: 38

I think I would do a Do Until Loop. Something like:

Range("A8").Select

Do Until Selection = Range("A3").Value
    ActiveCell.Offset(0, 1).Select
Loop

ActiveCell.Offset(0, 1).Select

To test it I put the value 14 in cell A3. Then I put a bunch of different values in each cell along row 8. I put 14 in k8. The macro will look at each cell and see if it equals 14 and then move on. You'll need an exit condition if you run out of data but that should get you started.

Upvotes: 1

Related Questions