telling range when to stop.

[cereldine]

Ive got an idea about how to approach the following problem but believe
i may be going about it in the wrong way. I have many sheets in
workbook 2 that look like the following, i need to copy the raw data
from these and ignore the forecat results. With time the sheet grows
and the forecats become real values.

actual actual actual forecast forecast forecast
jan 06 feb 06 mar 06 apr 06 may 06 jun 06
jbj 1 2 3 4 5 6
guyg 4 4 4 4 4 4

like i say i only need the raw data, the approach i have thought about
would be to find the first occurence of forecast and save the column
letter(?) in a variable (var1). Then when im declaring the range i
could do something like
ActiveSheet.range(B3, var1 & "123").copy

Is there an easier way? WOuld be grateful for any advice.
Also how would i find out the letter of a column?

 

[Guest]

One way:

col = Application.CountIf(Range("1:1"), "Actual") + 1
Set rng = Range(Cells(3, "B"), Cells(123, col))
MsgBox rng.Address

HTH

 

[cereldine]

thats pointed me in the right direction thanks, i think i'm going to try
use the following as on further investigation i discovered every sheet
has forecase but not all have actual. Thanks again

col = (dSheet.Cells.Find("forecast").Column) - 1
Set rng = Range(Cells(10, "B"), Cells(136, col))
MsgBox rng.Address