Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sequentially run macros | Excel Programming | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
Writing a DLL using Visual Studio 6 to return cell address! | Excel Programming | |||
Run Macros Sequentially | Excel Programming | |||
How not to fixed cell address in Macros | Excel Programming |