Writing macros to sequentially change the address of a cell in Exc
 

I always name the cell of the first title. So if my title is A4, I name A4.
I expect the first row of data to start at A5. This way, if anyone inserts
or delete a title row, my code continues to work. Then:

Sub NewData()
Dim Rng As Range
Dim NextCell As Range
Set Rng = Range("First Title")
If IsEmpty(Rng.Offset(1, 0)) Then
Set NextCell = Rng.Offset(1, 0)
Else
NextCell = Rng.End(xlDown).Offset(1, 0)
End If
End Sub

Works well. This code also assumes that the first column of data always has
a value, otherwise the end method doesn't work. Note that End(xlDown) is the
same as Ctrl+Down.

--

Rod Gill

"Ken" wrote in message
...
I enter data into a spreadsheet on a daily basis and keep a sequential
record
of this data.

Rather than copy and paste to build up this historical data base I need a
macro that changes the cell address as new data is entered. This new
address
is the next line down in sequence for storing this historical information.

In other words if the last address was C55 after running the macro the
next
address would be C56 thence C57 if it is run again. So every time the
macro
is run it sequentially directs the data being stored into the next line.

I have used the Find function to get the first line for data storage but
rather than just searching for a key word it remembers the original cell
address and will not allow sequential recording to occur. What I need is
to
be able to change the cell address within the macro so future data can be
stored in subsequent cells.

Any assistance would be appreciated
--
Ken living downunder

Writing macros to sequentially change the address of a cell in
 

--
Ken living downunder


"Rod Gill" wrote:

I always name the cell of the first title. So if my title is A4, I name A4.
I expect the first row of data to start at A5. This way, if anyone inserts
or delete a title row, my code continues to work. Then:

Sub NewData()
Dim Rng As Range
Dim NextCell As Range
Set Rng = Range("First Title")
If IsEmpty(Rng.Offset(1, 0)) Then
Set NextCell = Rng.Offset(1, 0)
Else
NextCell = Rng.End(xlDown).Offset(1, 0)
End If
End Sub

Works well. This code also assumes that the first column of data always has
a value, otherwise the end method doesn't work. Note that End(xlDown) is the
same as Ctrl+Down.

--

Rod Gill

"Ken" wrote in message
...
I enter data into a spreadsheet on a daily basis and keep a sequential
record
of this data.

Rather than copy and paste to build up this historical data base I need a
macro that changes the cell address as new data is entered. This new
address
is the next line down in sequence for storing this historical information.

In other words if the last address was C55 after running the macro the
next
address would be C56 thence C57 if it is run again. So every time the
macro
is run it sequentially directs the data being stored into the next line.

I have used the Find function to get the first line for data storage but
rather than just searching for a key word it remembers the original cell
address and will not allow sequential recording to occur. What I need is
to
be able to change the cell address within the macro so future data can be
stored in subsequent cells.

Any assistance would be appreciated
--
Ken living downunder

Hi Rod

Thanks for your interest and help.

As I have stated in my pervious responses my wife andI will be flying to
Brisbane to spend Christmas with our son and his family.

I will try yours and the other help I have received on my return.

Have a Merry Christmas and a pleasant New Year. Christmas here as usual
will be warm to hot, no snow down here.

Best regards

Ken Pearson

Writing macros to sequentially change the address of a cell in
 

--
Ken living downunder


"Ken" wrote:


--
Ken living downunder


"Rod Gill" wrote:

I always name the cell of the first title. So if my title is A4, I name A4.
I expect the first row of data to start at A5. This way, if anyone inserts
or delete a title row, my code continues to work. Then:

Sub NewData()
Dim Rng As Range
Dim NextCell As Range
Set Rng = Range("First Title")
If IsEmpty(Rng.Offset(1, 0)) Then
Set NextCell = Rng.Offset(1, 0)
Else
NextCell = Rng.End(xlDown).Offset(1, 0)
End If
End Sub

Works well. This code also assumes that the first column of data always has
a value, otherwise the end method doesn't work. Note that End(xlDown) is the
same as Ctrl+Down.

--

Rod Gill

"Ken" wrote in message
...
I enter data into a spreadsheet on a daily basis and keep a sequential
record
of this data.

Rather than copy and paste to build up this historical data base I need a
macro that changes the cell address as new data is entered. This new
address
is the next line down in sequence for storing this historical information.

In other words if the last address was C55 after running the macro the
next
address would be C56 thence C57 if it is run again. So every time the
macro
is run it sequentially directs the data being stored into the next line.

I have used the Find function to get the first line for data storage but
rather than just searching for a key word it remembers the original cell
address and will not allow sequential recording to occur. What I need is
to
be able to change the cell address within the macro so future data can be
stored in subsequent cells.

Any assistance would be appreciated
--
Ken living downunder

Hi Rod

Thanks for your interest and help.

As I have stated in my pervious responses my wife andI will be flying to
Brisbane to spend Christmas with our son and his family.

I will try yours and the other help I have received on my return.

Have a Merry Christmas and a pleasant New Year. Christmas here as usual
will be warm to hot, no snow down here.

Best regards

Ken Pearson


By Ken Pearson


I have found the responses helpful even though they did not give the
solution I needed.

The solution I developed is given by the macro below.

Part 1 of macro converts dates from month/day format into day/month. This is
the format used in OZ.

Part 2 is below this provides the macro that sequentially logs data into new
rows

' PART 2 of Macro

' Sequentially logs daily data

' Finds new row to record current lot of data

Cells.Find(What:="xray", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate




' Prepares cell to identify next row that will record next lot of new data

Dim Rnge As Range

Set Rnge = ActiveCell

Rnge.Offset(1, 0).Activate

ActiveCell.FormulaR1C1 = "xray"

' Copies cells C19:G19 (Date:Volume) into selected new row


Range("b19:g19").Select
Selection.Copy

Rnge.Offset(0, 0).Activate
ActiveCell.Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False


Range("b19").Select



'
End Sub

This is a simple macro that can be used to sequentially log data.

Best regards

Ken Pearson