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

15

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

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.