INTERCEPT cell with two parameter variable

[sal21]

I have myvar1="DDD" and myvar2="GGG" and the sheet attached.
How to find with vba code the cell of intersection from the 2 vaiable?
In this case is the cell C4 and insert the value of myvar3="1"

Note:
The numbers cells in row 1 and column A are variable... in this case in row 1 have 3 value and in column A have 3 value

[agibsonsw]

Something like this:

Code: Select all

Sub FindIntersectingValue()
    Dim myvar1 As String, myvar2 As String
    Dim fnd1 As Range, fnd2 As Range
    Dim fndValue As Integer
    
    myvar1 = "DDD"
    myvar2 = "GGG"
    
    Set fnd1 = Intersect(ActiveSheet.UsedRange, Rows("1:1")).Find(What:=myvar1)
    Set fnd2 = Intersect(ActiveSheet.UsedRange, Columns("A:A")).Find(What:=myvar2)

    fndValue = Intersect(ActiveSheet.UsedRange, fnd1.EntireColumn, fnd2.EntireRow).Value
    MsgBox "Found " & fndValue

End Sub

You'll need to add some code to check if the values are not found and additional arguments to 'Find' to be more specific about where it's looking, etc..

[agibsonsw]

This version is a bit neater:

Code: Select all

Sub FindIntersectingValue()
    Dim myvar1 As String, myvar2 As String
    Dim fnd1 As Variant, fnd2 As Variant
    Dim fndValue As Integer
    
    myvar1 = "DDD"
    myvar2 = "GGG"

    Set fnd1 = Intersect(ActiveSheet.UsedRange, Rows("1:1")).Find(What:=myvar1, _
        After:=Range("A1"), MatchCase:=False)
    Set fnd2 = Intersect(ActiveSheet.UsedRange, Columns("A:A")).Find(What:=myvar2, _
        After:=Range("A1"), MatchCase:=False)
    If fnd1 Is Nothing Or fnd2 Is Nothing Then
        MsgBox "Nothing found"
        Exit Sub
    End If
    
    fndValue = Intersect(ActiveSheet.UsedRange, fnd1.EntireColumn, fnd2.EntireRow).Value
    MsgBox "Found " & fndValue

End Sub