Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting content from cells by the cross point of the row and colum
I am just getting started with Excel Macros 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting content from cells by the cross point of the row and colum
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. BZeyger wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross-referencing content of one worksheet with another | Excel Worksheet Functions | |||
Sum and Offset: to add from a point to the last entry in the colum | Excel Worksheet Functions | |||
Cell Content Cross Reference | Excel Worksheet Functions | |||
Why did my Excel insertion point change to a cross? | New Users to Excel | |||
Finding the value for the point two lines cross | Charts and Charting in Excel |