maybe?
Sub definerange()
fc = Cells.Find("ACTUALS").Address
lc = Cells.Find("FORECAST").Address
'Range(fc & ":" & lc).Select
set mr=Range(fc & ":" & lc)
'I don't understand the rest. what is ar(1)?,etc
End Sub

--
Don Guillett
SalesAid Software

"T De Villiers"
wrote in message
news:T.De.Villiers.2br9n2_1154258105.4327@excelfor um-nospam.com...

I am having problems with the asterixed row, cant seem to define the
range
using the cells object.

Many Thanks


Sub ABC()
Dim rng As Range, cell As Range, ar As Range

' 1) Defines first row and column
Cells.Find(What:="ACTUALS", After:=ActiveCell, LookIn:=xlValues, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Activecell.column = m
Activecell.row = x

'2) Defines last row
Cells.Find(What:="FORECAST", After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Activecell.row = y


**Set rng = (Cells(x,m),Cells(y,m)).SpecialCells(xlConstants)

For Each ar In rng.Areas
For Each cell In ar
If cell.Row < ar(1).Row Then
cell.Offset(0, 1).Value = ar(1).Value
End If
Next cell
ar(1).ClearContents
Next ar
End Sub


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566385