getting content from cells by the cross point of the row and colum

[BZeyger]

I am just getting started with Excel Macro’s I have learned a great deal from
the help files and the record feature. Is there a way to copy the information
of a cell in one sheet and paste it into another based on the point the row
and column meet?

For example:

I have a worksheet named “DataSheet†that contain all my info. The sheet
contains various amounts and totals. I am looking to select the cell that
align with the row Total and the Column 2009. Can this be done? The locations
of these cell are different from sheet to sheet. Same structure but some
might be longer. The cell content should be insert into a blank worksheet
named "sheet1".


2007 2008 2009 2010 Total
Labor x x x x x

Rate 2 5 4 5 2

Total Price $200 $459 $300 $756
$1715


I would like to select the cell that has the total price for 2009 as an
example.

 

[JW]

Does this have to be in a macro? If you are just trying to get the
value, you can use Index Match. What you are trying to accomplish is
known as a two-way table lookup. The formula below will do what you
want (assuming years are in row 1 and labor, rate, etc are in column
A).
=INDEX(A1:F4,MATCH("Total Price",A1:A4,0),MATCH(2009,A1:F1,0))

If you have to do it in code, here is one way (assuming years are in
row 1 and labor, rate, etc are in column A).
Sub this()
Dim r As Long, col As Integer
c = Rows(1).Find(What:="2009", After:=[A1], _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
r = Columns(1).Find(What:="Total Price", After:=[A1], _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
MsgBox Cells(r, c).Text
End Sub

You could also use WorksheetFunction in VBA and use the Index and
Match there.