Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing macros to sequentially change the address of a cell in
-- Ken living downunder "Ken Johnson" wrote: Ken wrote: 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 Ken, Do you mean something like this... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then If Cells(Rows.Count, 1).End(xlUp).Row = 1 _ And Range("A2") < "" Then MsgBox "You have reached the bottom of the sheet!" Exit Sub End If Application.EnableEvents = False On Error GoTo ERRORHANDLER Dim rngOld As Range, rngNew As Range Set rngOld = Range(Cells(1, 1), _ Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) Set rngNew = rngOld.Offset(1, 0) rngNew.Value = rngOld.Value With Range("A1") .ClearContents .Select End With Application.EnableEvents = True End If Exit Sub ERRORHANDLER: Application.EnableEvents = True End Sub It's an event procedure. It's set up for A1 and could easily be modified to work on any cell. When new data is entered into A1 it is immediately moved to A2. Also, all cells (with data) below A1 are moved down as well. To get the code in place... 1. Copy it 2. Right click the sheet tab then select "View Code" from the popup menu. 3. Paste the code into the worksheet's code module. 4. Press Alt + F11 to get back to Excel's user interface. The code will only work if your Security setting is "Medium". If this is not the case then... 1. Go Tools|Macro|Security...then click on Medium then click OK 2. Close the workbook then reopen it. 3. Click "Enable Macros" on the "Security Warning" dialog. Everytime the workbook is opened you must click "Enable Macros". Ken Johnson Hi Ken Thanks for your help. I will be leaving today (Christmas eve down here) with my wife to spend Christmas with our son and grand children who live in Brisbane. Will try on my return. Have an enjoyable Christmas and New Year Many thanks Ken Pearson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing macros to sequentially change the address of a cell in
Ken wrote:
-- Ken living downunder "Ken Johnson" wrote: Ken wrote: 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 Ken, Do you mean something like this... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then If Cells(Rows.Count, 1).End(xlUp).Row = 1 _ And Range("A2") < "" Then MsgBox "You have reached the bottom of the sheet!" Exit Sub End If Application.EnableEvents = False On Error GoTo ERRORHANDLER Dim rngOld As Range, rngNew As Range Set rngOld = Range(Cells(1, 1), _ Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) Set rngNew = rngOld.Offset(1, 0) rngNew.Value = rngOld.Value With Range("A1") .ClearContents .Select End With Application.EnableEvents = True End If Exit Sub ERRORHANDLER: Application.EnableEvents = True End Sub It's an event procedure. It's set up for A1 and could easily be modified to work on any cell. When new data is entered into A1 it is immediately moved to A2. Also, all cells (with data) below A1 are moved down as well. To get the code in place... 1. Copy it 2. Right click the sheet tab then select "View Code" from the popup menu. 3. Paste the code into the worksheet's code module. 4. Press Alt + F11 to get back to Excel's user interface. The code will only work if your Security setting is "Medium". If this is not the case then... 1. Go Tools|Macro|Security...then click on Medium then click OK 2. Close the workbook then reopen it. 3. Click "Enable Macros" on the "Security Warning" dialog. Everytime the workbook is opened you must click "Enable Macros". Ken Johnson Hi Ken Thanks for your help. I will be leaving today (Christmas eve down here) with my wife to spend Christmas with our son and grand children who live in Brisbane. Will try on my return. Have an enjoyable Christmas and New Year Many thanks Ken Pearson Hi Ken, I hope Brisbane wasn't too hot and muggy. Version 3 enables you to have more than one input cell on the sheet. Just edit the address string in the line... Dim rngInput As Range: Set rngInput = Range("B3,D3,F3") As it stands, B3, D3 and F3 are input cells. If you wanted the first four cells in the second row to be input cells you would change the address string to "A2:D2". Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ERRORHANDLER 'Change the address string below to suit your needs Dim rngInput As Range: Set rngInput = Range("B3,D3,F3") '<<< If Not Intersect(Target, rngInput) Is Nothing Then Application.ScreenUpdating = False Dim rngCell As Range For Each rngCell In Intersect(Target, rngInput) If Cells(Rows.Count, rngCell.Column).End(xlUp).Row = _ rngCell.Row And rngCell.Offset(1, 0).Value < "" Then MsgBox "You have reached the bottom of the sheet" _ & vbNewLine & "in column " _ & Mid(rngCell.Address, 2, _ WorksheetFunction.Find("$", rngCell.Address, 2) - 2) GoTo NEXT_rngCell End If Application.EnableEvents = False Dim rngOld As Range, rngNew As Range Set rngOld = Range(rngCell, Cells(Cells(Rows.Count, _ rngCell.Column).End(xlUp).Row, rngCell.Column)) Set rngNew = rngOld.Offset(1, 0) rngNew.Value = rngOld.Value With rngCell .ClearContents .Select End With NEXT_rngCell: Next rngCell Application.EnableEvents = True End If Exit Sub ERRORHANDLER: Application.EnableEvents = True Me.Protect End Sub Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing macros to sequentially change the address of a cell in
Ken Johnson wrote: Ken wrote: -- Ken living downunder "Ken Johnson" wrote: Ken wrote: 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 Ken, Do you mean something like this... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then If Cells(Rows.Count, 1).End(xlUp).Row = 1 _ And Range("A2") < "" Then MsgBox "You have reached the bottom of the sheet!" Exit Sub End If Application.EnableEvents = False On Error GoTo ERRORHANDLER Dim rngOld As Range, rngNew As Range Set rngOld = Range(Cells(1, 1), _ Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) Set rngNew = rngOld.Offset(1, 0) rngNew.Value = rngOld.Value With Range("A1") .ClearContents .Select End With Application.EnableEvents = True End If Exit Sub ERRORHANDLER: Application.EnableEvents = True End Sub It's an event procedure. It's set up for A1 and could easily be modified to work on any cell. When new data is entered into A1 it is immediately moved to A2. Also, all cells (with data) below A1 are moved down as well. To get the code in place... 1. Copy it 2. Right click the sheet tab then select "View Code" from the popup menu. 3. Paste the code into the worksheet's code module. 4. Press Alt + F11 to get back to Excel's user interface. The code will only work if your Security setting is "Medium". If this is not the case then... 1. Go Tools|Macro|Security...then click on Medium then click OK 2. Close the workbook then reopen it. 3. Click "Enable Macros" on the "Security Warning" dialog. Everytime the workbook is opened you must click "Enable Macros". Ken Johnson Hi Ken Thanks for your help. I will be leaving today (Christmas eve down here) with my wife to spend Christmas with our son and grand children who live in Brisbane. Will try on my return. Have an enjoyable Christmas and New Year Many thanks Ken Pearson Hi Ken, I hope Brisbane wasn't too hot and muggy. Version 3 enables you to have more than one input cell on the sheet. Just edit the address string in the line... Dim rngInput As Range: Set rngInput = Range("B3,D3,F3") As it stands, B3, D3 and F3 are input cells. If you wanted the first four cells in the second row to be input cells you would change the address string to "A2:D2". Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ERRORHANDLER 'Change the address string below to suit your needs Dim rngInput As Range: Set rngInput = Range("B3,D3,F3") '<<< If Not Intersect(Target, rngInput) Is Nothing Then Application.ScreenUpdating = False Dim rngCell As Range For Each rngCell In Intersect(Target, rngInput) If Cells(Rows.Count, rngCell.Column).End(xlUp).Row = _ rngCell.Row And rngCell.Offset(1, 0).Value < "" Then MsgBox "You have reached the bottom of the sheet" _ & vbNewLine & "in column " _ & Mid(rngCell.Address, 2, _ WorksheetFunction.Find("$", rngCell.Address, 2) - 2) GoTo NEXT_rngCell End If Application.EnableEvents = False Dim rngOld As Range, rngNew As Range Set rngOld = Range(rngCell, Cells(Cells(Rows.Count, _ rngCell.Column).End(xlUp).Row, rngCell.Column)) Set rngNew = rngOld.Offset(1, 0) rngNew.Value = rngOld.Value With rngCell .ClearContents .Select End With NEXT_rngCell: Next rngCell Application.EnableEvents = True End If Exit Sub ERRORHANDLER: Application.EnableEvents = True Me.Protect End Sub Ken Johnson Hi Ken, Oops! I was fooling around with Sheet Protection at some stage then I neglected to remove all of that part of the code before posting Version 3, so use this instead, or just delete the second last line (Me.Protect)... Private Sub Worksheet_Change(ByVal Target As Range) 'Change the address string below to suit your needs On Error GoTo ERRORHANDLER Dim rngInput As Range: Set rngInput = Range("A1:D1") '<<< If Not Intersect(Target, rngInput) Is Nothing Then Application.ScreenUpdating = False Dim rngCell As Range For Each rngCell In Intersect(Target, rngInput) If Cells(Rows.Count, rngCell.Column).End(xlUp).Row = _ rngCell.Row And rngCell.Offset(1, 0).Value < "" Then MsgBox "You have reached the bottom of the sheet" _ & vbNewLine & "in column " _ & Mid(rngCell.Address, 2, _ WorksheetFunction.Find("$", rngCell.Address, 2) - 2) GoTo NEXT_rngCell End If Application.EnableEvents = False Dim rngOld As Range, rngNew As Range Set rngOld = Range(rngCell, Cells(Cells(Rows.Count, _ rngCell.Column).End(xlUp).Row, rngCell.Column)) Set rngNew = rngOld.Offset(1, 0) rngNew.Value = rngOld.Value With rngCell .ClearContents .Select End With NEXT_rngCell: Next rngCell Application.EnableEvents = True End If Exit Sub ERRORHANDLER: Application.EnableEvents = True End Sub Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing macros to sequentially change the address of a cell in
Hi Ken,
I've been looking at how others achieve the same effect and have discovered it's a lot easier than I thought. It turns out that all that is needed to shift all of the old data cells down one row is .Insert Shift:= xlDown. Also, the way that I was checking that there was still space on the sheet for moving the data down one more row was logically flawed, so I've fixed that up too. The logically correct way also turned out to be a lot simpler than I originally thought. So, hopefully my final version is... Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Dim rngInput As Range Dim rngCell As Range Set rngInput = Range("A2") '<<<<<<<<<<<<<<<<<<<<<< 'Edit rngInput's Address String to suit your needs^ 'Examples... ' "A2,C2" for A2 and C2 ' "D1:G1" for D1, E1, F1 and G1 ' "A2,C2,D1:G1" for A2, C2, D1, E1, F1 and G1. If Not Intersect(Target, rngInput) Is Nothing Then On Error GoTo ERRORHANDLER Application.EnableEvents = False For Each rngCell In Intersect(Target, rngInput) If rngCell.Value < "" Then If Cells(Rows.Count, _ rngCell.Column).Value = "" Then rngCell.Insert shift:=xlDown With rngCell.Offset(-1, 0) .ClearContents .Select End With Else: MsgBox "No more room in column " _ & Mid(rngCell.Address, 2, _ WorksheetFunction.Find( _ "$", rngCell.Address, 2) - 2) End If End If Next rngCell Application.EnableEvents = True End If Exit Sub ERRORHANDLER: Application.EnableEvents = True End Sub Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing macros to sequentially change the address of a cell in Exc | Excel Programming | |||
Writing macros to sequentially change the address of a cell in | Excel Programming | |||
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 |