Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
defining a range of cells in excel using cell content | Excel Programming | |||
defining unique range of cells for different sheets as the same n. | Excel Discussion (Misc queries) | |||
Drop down defining content of a range of cells | Links and Linking in Excel | |||
Defining Range of For Each Loop for Cells and Worksheets | Excel Programming | |||
Defining a variable Range for cells with values in them! | Excel Programming |