Finding the value at the intersection of a row and column

I have a worksheet that holds sales data in a tabular layout. With months listed across the columns and categorys listed down the rows.

I have another worksheet (budget) that is built dynamically using vba for a date range which falls inside the column dates found in the sales data sheet.

The budget sheet also has a number of the same categories as the row labels.

I need to be able to pass the values of the column heading (vDate) from the budget sheet and the row label (vCategory) to a routine that will find me the value from the intersection for that date and category on the sales data sheet.

Is there an easy way to do this in vba?

Thanks in advance

The VBA Find Method can be used to return a range object for the location of the category and date. You could then find the row/column related to these and get the value from where these intersect quite easily.

If you need help with the code post back.

Dom

Put together an example using formula and code, see attached.

Dom

TheCyrusVirus,

Try:

Index Match Match, or VLOOKUP MATCH
Posts: 8 Lookup help
http://www.mrexcel.com/forum/showthread.php?t=466713


Or, post your workbook (click on the New Post button, and scroll down and see Manage Attachments).

Or in VBa

Sub intersectRowCol()
    Set isect = Application.Intersect(Range("B:B"), Rows(2))
    If Not isect Is Nothing Then
        MsgBox isect
    End If
    Set isect = Nothing
End Sub

or in more general terms

Function intersectRowCol(RowNo As Long, ColumnRef As String)
    Set isect = Application.Intersect(Range(ColumnRef), Rows(RowNo))
    If Not isect Is Nothing Then
        intersectRowCol = isect
    End If
    Set isect = Nothing
End Function

called as

    myValue = intersectRowCol(2, "B:B")

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.