Defining Range using Cells
 

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

Defining Range using Cells
 

Set rng = range(Cells(x,m),Cells(y,m)).SpecialCells(xlConsta nts)

maybe?????

T De Villiers wrote:

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

--

Dave Peterson

Defining Range using Cells
 

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

Defining Range using Cells
 

You may need to envelope the cells statement with range()

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

hope this does the trick.

don't you just love niggly little syntax issues....


http://www.excel-ant.co.uk

Defining Range using Cells
 

if you're using a with...

with sheets("Sheet1")

set rng = range(.cells(row,col),.cells(row,col)

end with


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=566385

Defining Range using Cells
 

I like to qualify my ranges, too.


with sheets("Sheet1")
set rng = .range(.cells(row,col),.cells(row,col)
end with

(added dot before range)



MattShoreson wrote:

if you're using a with...

with sheets("Sheet1")

set rng = range(.cells(row,col),.cells(row,col)

end with

--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=566385

--

Dave Peterson

Defining Range using Cells
 

In fact, I'd add some checks...

Option Explicit
Sub ABC()

Dim TopCell As Range
Dim BotCell As Range
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myArea As Range

Set wks = ActiveSheet

With wks
Set TopCell = .Cells.Find(what:="ACTUALS", _
After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)

Set BotCell = .Cells.Find("FORECAST", _
After:=.Cells(1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False)

If TopCell Is Nothing _
Or BotCell Is Nothing Then
MsgBox "at least one key wasn't found"
Exit Sub
End If

Set myRng = Nothing
On Error Resume Next
Set myRng = .Range(.Cells(TopCell.Row, TopCell.Column), _
.Cells(BotCell.Row, TopCell.Column)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants"
Exit Sub
End If

For Each myArea In myRng.Areas
For Each myCell In myArea.Cells
If myCell.Row < myArea(1).Row Then
myCell.Offset(0, 1).Value = myArea(1).Value
End If
Next myCell
myArea(1).ClearContents
Next myArea
End With
End Sub



Dave Peterson wrote:

I like to qualify my ranges, too.

with sheets("Sheet1")
set rng = .range(.cells(row,col),.cells(row,col)
end with

(added dot before range)

MattShoreson wrote:

if you're using a with...

with sheets("Sheet1")

set rng = range(.cells(row,col),.cells(row,col)

end with

--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=566385

--

Dave Peterson

--

Dave Peterson

Defining Range using Cells
 

Sub ABC()
Dim rng As Range, cell As Range, ar As Range
Dim rng1 as Range, rng2 as Range
Dim x as Long, y as Long, m as Long
' 1) Defines first row and column
set rng1 = Cells.Find(What:="ACTUALS", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
m = rng1.Column
x = rng1.Row

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


Set rng = Range(Cells(x,m),Cells(y,m)).SpecialCells(xlConsta nts)

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

However, if you have Forecast and Actuals in the data range, then that
doesn't reflect your original sample and the code I provided was set up for
your example. Perhaps you want

x = rng1.Row + 1

. . .

y = rng2.row - 1

so those values are excluded. I can't say definitively since I don't know
how your data is laid out.

--
Regards,
Tom Ogilvy




"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

Defining Range using Cells
 

I like to qualify my ranges, too.
--But not essential using this method!

with sheets("Sheet1")
set rng = .range(.cells(row,col),.cells(row,col)
end with

(added dot before range


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=566385