Hi there,
I have a worksheet in Excel containing various data. The data is often on the same form but not at the same position. In this I want to copy a table of data that is located under a cell with the text FX.
I use this code to detect the FX cell.
Sub FoundIt()
Dim mycell As String
Cells.Find(What:="FX", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
mycell = ActiveCell.Address
It's not neccecarily so that the table is located directly under the cell containing the text FX but at least it is in the same column. So I would need a function that from FX looks in that column after the first numeric value and then copies that plus all that follows in the same column.
Example (should illustrate a normal worksheet)
| A | B | C | D |
| 1Test | blabla | ||
| 2 |
FX |
FY | |
| 3 | disp | disp | |
| 4 | blabla | ||
| 5 |
1 |
2 | |
| 6 |
6 |
5 | |
| 7 |
1 |
8 | |
| 8 |
8 |
9 | |
| 9 |
9 |
5 | |
| 10 |
6 |
4 | |
| 11 | |||
| 12 | |||
| 13 | |||
| 14 | blabla |
From this I want to copy the numbers under FX i.e. FX = [1 6 1 8 9 6]
Anyone that can help me on this Note that it isn't neccessarily so that the numbers starts, like in this example 2 rows under FX.
Grateful for help on this
\Jonas
Excel: Copying cells under a specific cell containing a title
philknight
Jonas this should be no problem... You've done the hard bit
Cells.Find returns a range object and the range object contains quite a few properties like row number and column. You just need a variable for the 'currentRow' and a loop that increments the 'currentRow' one at a time, for each iteration check the value of cell(col, currentRow) and make sure it IsNumeric() or <> "". Continue the loop until IsNumeric(value) = false or value = "" is true.