Defining Range using Cells

[T De Villiers]

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

[Dave Peterson]

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

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

[Don Guillett]

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
[email protected]
"T De Villiers" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[somethinglikeant]

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

[MattShoreson]

if you're using a with...

with sheets("Sheet1")

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

end with

[Dave Peterson]

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]

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

[Tom Ogilvy]

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(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

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[MattShoreson]

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)