| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding to choose the previous completed cell in a column
Hi,
I have used the Macro recorder to create a macro for me however when I apply the macro to other similar worksheets in bombs out. What I have recorded is for the macro in whatever column to go back to column A in the row and then go up to the previously filled cell in column A and complete a set function. What the recorder records is a change in the number of cells (0, -15) or whatever and it then applies that movement change to all future runnings. Coding is as follows ( I hope this helps) ActiveCell.Offset(0, 1).Range("A1:I1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone ActiveCell.Offset(0, 9).Range("A1:B1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, 5).Range("A1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, -15).Range("A1").Select Selection.Cut Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 0).Range("A1").Select Selection.ClearContents End Sub I don't want it to recorded the number of times I've moved up...just the fact that I've moved up. When I recorded it I use the Ctrl+(up arrow) to record the movement however it has given this a literal value of -15. I am trying to move around the worksheet using Ctrl+ and each time is does the above. I'm also selecting cells in a similar way by selecting the cell in a column (That is blank) and doing a Shift+Ctrl+up arrow and doing a Ctrl+D to fill the calculations held in the first cell. This also doesn't work as it's taking literal cell movements. I hope that someone is able to help me |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Coding to choose the previous completed cell in a column
Cells(ActiveCell.Row, "A").End(xlUp).Select
-- __________________________________ HTH Bob "Darren Ingram" wrote in message ... Hi, I have used the Macro recorder to create a macro for me however when I apply the macro to other similar worksheets in bombs out. What I have recorded is for the macro in whatever column to go back to column A in the row and then go up to the previously filled cell in column A and complete a set function. What the recorder records is a change in the number of cells (0, -15) or whatever and it then applies that movement change to all future runnings. Coding is as follows ( I hope this helps) ActiveCell.Offset(0, 1).Range("A1:I1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone ActiveCell.Offset(0, 9).Range("A1:B1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, 5).Range("A1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, -15).Range("A1").Select Selection.Cut Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 0).Range("A1").Select Selection.ClearContents End Sub I don't want it to recorded the number of times I've moved up...just the fact that I've moved up. When I recorded it I use the Ctrl+(up arrow) to record the movement however it has given this a literal value of -15. I am trying to move around the worksheet using Ctrl+ and each time is does the above. I'm also selecting cells in a similar way by selecting the cell in a column (That is blank) and doing a Shift+Ctrl+up arrow and doing a Ctrl+D to fill the calculations held in the first cell. This also doesn't work as it's taking literal cell movements. I hope that someone is able to help me |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Coding to choose the previous completed cell in a column
Don,
I have to confess that I'm not sure of what is going on... It seems that the code you wrote sends me to cell A1 all the time. (It did when I played around with it) However I only want it to go up the rows in a column (any column) to the previous entered cell either selecting all cells in between (and then doing a Ctrl+D) or I just bounce from the cell I'm currently in up to the previous filled in cell (not selecting the inbetween cells)..... ie. J10:J5 (selecting) or from J10 to J5 (assuming there is no data in J6,J7,J8,J9). I know that the answer lies in the "a" par of the following but I'm not sure of its relationship here. ****ActiveCell.Row, "a").End(xlUp***** I'm very new at this so please bear with me. Regards, "Don Guillett" wrote: I'm not quite sure of all you want to do but maybe this will get you started. However, Selections are NOT necessary or desirable. Sub gotocolacell() Cells(ActiveCell.Row, "a").End(xlUp).Select End Sub Sub workoncell() Cells(ActiveCell.Row, "a").End(xlUp).copy End Sub 'or Sub gotocolacell() With Cells(ActiveCell.Row, "a").End(xlUp) .Borders.LineStyle = xlNone .Borders(xlEdgeBottom).LineStyle = xlContinuous End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Darren Ingram" wrote in message ... Hi, I have used the Macro recorder to create a macro for me however when I apply the macro to other similar worksheets in bombs out. What I have recorded is for the macro in whatever column to go back to column A in the row and then go up to the previously filled cell in column A and complete a set function. What the recorder records is a change in the number of cells (0, -15) or whatever and it then applies that movement change to all future runnings. Coding is as follows ( I hope this helps) ActiveCell.Offset(0, 1).Range("A1:I1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone ActiveCell.Offset(0, 9).Range("A1:B1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, 5).Range("A1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, -15).Range("A1").Select Selection.Cut Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 0).Range("A1").Select Selection.ClearContents End Sub I don't want it to recorded the number of times I've moved up...just the fact that I've moved up. When I recorded it I use the Ctrl+(up arrow) to record the movement however it has given this a literal value of -15. I am trying to move around the worksheet using Ctrl+ and each time is does the above. I'm also selecting cells in a similar way by selecting the cell in a column (That is blank) and doing a Shift+Ctrl+up arrow and doing a Ctrl+D to fill the calculations held in the first cell. This also doesn't work as it's taking literal cell movements. I hope that someone is able to help me |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Coding to choose the previous completed cell in a column
Did you try mine?
-- __________________________________ HTH Bob "Darren Ingram" wrote in message ... Don, I have to confess that I'm not sure of what is going on... It seems that the code you wrote sends me to cell A1 all the time. (It did when I played around with it) However I only want it to go up the rows in a column (any column) to the previous entered cell either selecting all cells in between (and then doing a Ctrl+D) or I just bounce from the cell I'm currently in up to the previous filled in cell (not selecting the inbetween cells)..... ie. J10:J5 (selecting) or from J10 to J5 (assuming there is no data in J6,J7,J8,J9). I know that the answer lies in the "a" par of the following but I'm not sure of its relationship here. ****ActiveCell.Row, "a").End(xlUp***** I'm very new at this so please bear with me. Regards, "Don Guillett" wrote: I'm not quite sure of all you want to do but maybe this will get you started. However, Selections are NOT necessary or desirable. Sub gotocolacell() Cells(ActiveCell.Row, "a").End(xlUp).Select End Sub Sub workoncell() Cells(ActiveCell.Row, "a").End(xlUp).copy End Sub 'or Sub gotocolacell() With Cells(ActiveCell.Row, "a").End(xlUp) .Borders.LineStyle = xlNone .Borders(xlEdgeBottom).LineStyle = xlContinuous End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Darren Ingram" wrote in message ... Hi, I have used the Macro recorder to create a macro for me however when I apply the macro to other similar worksheets in bombs out. What I have recorded is for the macro in whatever column to go back to column A in the row and then go up to the previously filled cell in column A and complete a set function. What the recorder records is a change in the number of cells (0, -15) or whatever and it then applies that movement change to all future runnings. Coding is as follows ( I hope this helps) ActiveCell.Offset(0, 1).Range("A1:I1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone ActiveCell.Offset(0, 9).Range("A1:B1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, 5).Range("A1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, -15).Range("A1").Select Selection.Cut Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 0).Range("A1").Select Selection.ClearContents End Sub I don't want it to recorded the number of times I've moved up...just the fact that I've moved up. When I recorded it I use the Ctrl+(up arrow) to record the movement however it has given this a literal value of -15. I am trying to move around the worksheet using Ctrl+ and each time is does the above. I'm also selecting cells in a similar way by selecting the cell in a column (That is blank) and doing a Shift+Ctrl+up arrow and doing a Ctrl+D to fill the calculations held in the first cell. This also doesn't work as it's taking literal cell movements. I hope that someone is able to help me |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Coding to choose the previous completed cell in a column
Hi Bob,
I have just tried yours....but I think I have done something wrong (due to my inexperience with coding and general newbieness) I put your line of code into: Sub Part1() ' ' Part1 Macro ' Draw line under row from column B to column J ' ' Keyboard Shortcut: Ctrl+Shift+Q ' ActiveCell.Offset(0, 1).Range("A1:I1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone ActiveCell.Offset(0, 9).Range("A1:B1").Select Range(Selection, Selection.End(xlUp)).Select Cells(ActiveCell.Row, "A").End(xlUp).Select Selection.FillDown ActiveCell.Offset(0, 5).Range("A1").Select Range(Selection, Selection.End(xlUp)).Select Cells(ActiveCell.Row, "A").End(xlUp).Select Selection.FillDown ActiveCell.Offset(0, -15).Range("A1").Select Selection.Cut Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 0).Range("A1").Select Selection.ClearContents End Sub where my original code is left in but I have put ( ' ) in front so that it doesn't affect the script but I can see what I originally had for learning purposes. I put your line of code below it.... Was this right? It obviously wasn't because when I ran it I receive an error. Your thoughts? Thank you for your patience.... I'm really keen to learn macros as they would play such a benefical role in my day to day work activities. Regards, "Bob Phillips" wrote: Did you try mine? -- __________________________________ HTH Bob "Darren Ingram" wrote in message ... Don, I have to confess that I'm not sure of what is going on... It seems that the code you wrote sends me to cell A1 all the time. (It did when I played around with it) However I only want it to go up the rows in a column (any column) to the previous entered cell either selecting all cells in between (and then doing a Ctrl+D) or I just bounce from the cell I'm currently in up to the previous filled in cell (not selecting the inbetween cells)..... ie. J10:J5 (selecting) or from J10 to J5 (assuming there is no data in J6,J7,J8,J9). I know that the answer lies in the "a" par of the following but I'm not sure of its relationship here. ****ActiveCell.Row, "a").End(xlUp***** I'm very new at this so please bear with me. Regards, "Don Guillett" wrote: I'm not quite sure of all you want to do but maybe this will get you started. However, Selections are NOT necessary or desirable. Sub gotocolacell() Cells(ActiveCell.Row, "a").End(xlUp).Select End Sub Sub workoncell() Cells(ActiveCell.Row, "a").End(xlUp).copy End Sub 'or Sub gotocolacell() With Cells(ActiveCell.Row, "a").End(xlUp) .Borders.LineStyle = xlNone .Borders(xlEdgeBottom).LineStyle = xlContinuous End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Darren Ingram" wrote in message ... Hi, I have used the Macro recorder to create a macro for me however when I apply the macro to other similar worksheets in bombs out. What I have recorded is for the macro in whatever column to go back to column A in the row and then go up to the previously filled cell in column A and complete a set function. What the recorder records is a change in the number of cells (0, -15) or whatever and it then applies that movement change to all future runnings. Coding is as follows ( I hope this helps) ActiveCell.Offset(0, 1).Range("A1:I1").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone ActiveCell.Offset(0, 9).Range("A1:B1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, 5).Range("A1").Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown ActiveCell.Offset(0, -15).Range("A1").Select Selection.Cut Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 0).Range("A1").Select Selection.ClearContents End Sub I don't want it to recorded the number of times I've moved up...just the fact that I've moved up. When I recorded it I use the Ctrl+(up arrow) to record the movement however it has given this a literal value of -15. I am trying to move around the worksheet using Ctrl+ and each time is does the above. I'm also selecting cells in a similar way by selecting the cell in a column (That is blank) and doing a Shift+Ctrl+up arrow and doing a Ctrl+D to fill the calculations held in the first cell. This also doesn't work as it's taking literal cell movements. I hope that someone is able to help me |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| choose last cell with a value in a column | Excel Discussion (Misc queries) | |||
| Create formula to choose price based on previous choice. | Excel Worksheet Functions | |||
| Hiding column if cell in previous column is empty-revised | Excel Programming | |||
| How to indicate in formula to choose the entire column starting from a specified cell? | Excel Discussion (Misc queries) | |||
| Referring to Previous Worksheet - coding | Excel Worksheet Functions | |||
Linear Mode
