Relative Cell References in Macros
 

Relative cell References within a LOOP and IF Statement


I’m new to writing macros for Excel 97
I wish to clear a range of cells on the current line to the left of th
active cell.
I’m having trouble with setting up a “range” with relative cel
values.
The following routine works well to clear an individual cell,
How do I modify it to clear a range of cells.

Any help would be really appreciated


Sub Loop1()
' This loop runs down a column until there is nothing in the column o
the it’s immediate left
‘ Clears a cell 5 columns to left

Do
ActiveCell.Select
If ActiveCell.Offset(0, -1) = 0 Then Clear_Cells
ActiveCell.Offset(1, 0).Select
‘ gets next row
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Sub Clear_Cells()
ActiveCell.Offset(0, -5).Select
Selection.ClearContents
ActiveCell.Offset(0, 5).Select
End Sub

Barry Staples Nottingham Englan

--
Message posted from http://www.ExcelForum.com

Relative Cell References in Macros
 

Range(Cells(activeCell.row,1),ActiveCell.offset(0,-1)).ClearContents

--
Regards
Tom Ogilvy


Barry Staples wrote in
message ...
Relative cell References within a LOOP and IF Statement


I'm new to writing macros for Excel 97
I wish to clear a range of cells on the current line to the left of the
active cell.
I'm having trouble with setting up a "range" with relative cell
values.
The following routine works well to clear an individual cell,
How do I modify it to clear a range of cells.

Any help would be really appreciated


Sub Loop1()
' This loop runs down a column until there is nothing in the column on
the it's immediate left
' Clears a cell 5 columns to left

Do
ActiveCell.Select
If ActiveCell.Offset(0, -1) = 0 Then Clear_Cells
ActiveCell.Offset(1, 0).Select
' gets next row
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Sub Clear_Cells()
ActiveCell.Offset(0, -5).Select
Selection.ClearContents
ActiveCell.Offset(0, 5).Select
End Sub

Barry Staples Nottingham England


---
Message posted from http://www.ExcelForum.com/

Relative Cell References in Macros
 

Sub Clear_Cells()
ActiveCell.Offset(0, -5).Resize(1,5).Select
Selection.ClearContents
ActiveCell.Offset(0, 5).Select
End Sub

Or, remove the Select:


Sub Clear_Cells()
ActiveCell.Offset(0, -5).Resize(1,5).ClearContents
End Sub


--
Darren
"Barry Staples " wrote in
message ...
Relative cell References within a LOOP and IF Statement


I'm new to writing macros for Excel 97
I wish to clear a range of cells on the current line to the left of the
active cell.
I'm having trouble with setting up a "range" with relative cell
values.
The following routine works well to clear an individual cell,
How do I modify it to clear a range of cells.

Any help would be really appreciated


Sub Loop1()
' This loop runs down a column until there is nothing in the column on
the it's immediate left
' Clears a cell 5 columns to left

Do
ActiveCell.Select
If ActiveCell.Offset(0, -1) = 0 Then Clear_Cells
ActiveCell.Offset(1, 0).Select
' gets next row
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Sub Clear_Cells()
ActiveCell.Offset(0, -5).Select
Selection.ClearContents
ActiveCell.Offset(0, 5).Select
End Sub

Barry Staples Nottingham England


---
Message posted from http://www.ExcelForum.com/

Relative Cell References in Macros
 

Barry,

Sub test()
With ActiveCell
If .Column 1 Then
.Offset(0, -1).Resize(1, Columns.Count - .Column +
2).ClearContents
End If
End With
End Sub


Rob

"Barry Staples " wrote in
message ...
Relative cell References within a LOOP and IF Statement


I’m new to writing macros for Excel 97
I wish to clear a range of cells on the current line to the left of the
active cell.
I’m having trouble with setting up a “range” with relative cell
values.
The following routine works well to clear an individual cell,
How do I modify it to clear a range of cells.

Any help would be really appreciated


Sub Loop1()
' This loop runs down a column until there is nothing in the column on
the it’s immediate left
‘ Clears a cell 5 columns to left

Do
ActiveCell.Select
If ActiveCell.Offset(0, -1) = 0 Then Clear_Cells
ActiveCell.Offset(1, 0).Select
‘ gets next row
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Sub Clear_Cells()
ActiveCell.Offset(0, -5).Select
Selection.ClearContents
ActiveCell.Offset(0, 5).Select
End Sub

Barry Staples Nottingham England


---
Message posted from http://www.ExcelForum.com/

Relative Cell References in Macros
 

Thanks Tom,Darren and Rob
With your help I have sorted the problem.
Interesting to see the different solutions


---
Message posted from http://www.ExcelForum.com/