Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative movements within macros
I am trying to program some macros to do some automatic formatting of some
imported information. On any single run of the similar information the formatting works quite well. However, if there are added rows or columns, some parts seem to screw up. for example: A series of numbers currently are in cells C8:C18 and the cursor is currently on C8. From the keyboard I wish to hit Shift-End-Down Arrow to select the column to the end and then hit Shift-Down Arrow twice to go to the second line down leaving a blank line but selecting all the cells between C8:C18. I have relative referencing active when I started recording. So at this point I have cells C8:C20 highlighted. This works as long as the column only goes from C8 to C18. However, I cannot control this. Sometimes it may be the data starts at C8 and extends to C22 and I want to select the cells C8:C24 without having to know the extra rows exist. The macros keep recording Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A13").Select but I don't want it to defaul to A13 all the time because it could be longer or even shorter. Anyone have any ideas? I can't find anything on the MS website using searches like cursor move or moving cursor, etc. Ultimately I want to sum the column and have the sum show in the last cell selected, then copy that formula for all the remainingg columns (the number of which I will not know so I was going to use the keyboard commands to Copy the formula, go back up two lines, End Right Arrow to get to the last column, move down two rows and select the cells back to the original and paste. Hope this makes sense. TIA rasinc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative movements within macros
Did you wnat a formula in the cell to sum the column or would just a number
that is the sum of the column do. The formula is a bit more tricky. -- HTH... Jim Thomlinson "rasinc" wrote: I am trying to program some macros to do some automatic formatting of some imported information. On any single run of the similar information the formatting works quite well. However, if there are added rows or columns, some parts seem to screw up. for example: A series of numbers currently are in cells C8:C18 and the cursor is currently on C8. From the keyboard I wish to hit Shift-End-Down Arrow to select the column to the end and then hit Shift-Down Arrow twice to go to the second line down leaving a blank line but selecting all the cells between C8:C18. I have relative referencing active when I started recording. So at this point I have cells C8:C20 highlighted. This works as long as the column only goes from C8 to C18. However, I cannot control this. Sometimes it may be the data starts at C8 and extends to C22 and I want to select the cells C8:C24 without having to know the extra rows exist. The macros keep recording Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A13").Select but I don't want it to defaul to A13 all the time because it could be longer or even shorter. Anyone have any ideas? I can't find anything on the MS website using searches like cursor move or moving cursor, etc. Ultimately I want to sum the column and have the sum show in the last cell selected, then copy that formula for all the remainingg columns (the number of which I will not know so I was going to use the keyboard commands to Copy the formula, go back up two lines, End Right Arrow to get to the last column, move down two rows and select the cells back to the original and paste. Hope this makes sense. TIA rasinc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative movements within macros
Here it is with the formula at the bottom. This assumes that each column has
the same number of rows in it and that there are no populated cells below and/or to the right of the last cell in the last column you want to sum. Sub MakeTotals() Dim rngTotal As Range Dim rngTarget As Range Set rngTarget = LastCell() Set rngTotal = Range(rngTarget, Cells(1, rngTarget.Column)) Set rngTarget = rngTarget.Offset(1, 0) rngTarget.Formula = "=Sum(" & rngTotal.Address & ")" Do Set rngTarget = rngTarget.Offset(0, -1) Set rngTotal = rngTotal.Offset(0, -1) rngTarget.Formula = "=Sum(" & rngTotal.Address & ")" Loop Until rngTarget.Column = 1 End Sub Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Did you wnat a formula in the cell to sum the column or would just a number that is the sum of the column do. The formula is a bit more tricky. -- HTH... Jim Thomlinson "rasinc" wrote: I am trying to program some macros to do some automatic formatting of some imported information. On any single run of the similar information the formatting works quite well. However, if there are added rows or columns, some parts seem to screw up. for example: A series of numbers currently are in cells C8:C18 and the cursor is currently on C8. From the keyboard I wish to hit Shift-End-Down Arrow to select the column to the end and then hit Shift-Down Arrow twice to go to the second line down leaving a blank line but selecting all the cells between C8:C18. I have relative referencing active when I started recording. So at this point I have cells C8:C20 highlighted. This works as long as the column only goes from C8 to C18. However, I cannot control this. Sometimes it may be the data starts at C8 and extends to C22 and I want to select the cells C8:C24 without having to know the extra rows exist. The macros keep recording Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A13").Select but I don't want it to defaul to A13 all the time because it could be longer or even shorter. Anyone have any ideas? I can't find anything on the MS website using searches like cursor move or moving cursor, etc. Ultimately I want to sum the column and have the sum show in the last cell selected, then copy that formula for all the remainingg columns (the number of which I will not know so I was going to use the keyboard commands to Copy the formula, go back up two lines, End Right Arrow to get to the last column, move down two rows and select the cells back to the original and paste. Hope this makes sense. TIA rasinc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative movements within macros
Jim,
Thanks for the reply and the code. I hadn't expected that much. I thought the solution was easier but that I just couldn't find it. I ran a quick test on the code and it does calculate the sum for all numbers in the column to the bottom of the last row, however, I did not give you all the information because I did not think this approach was necessary. The columns will actually have two different sums. There are about 4 blank rows between and on the second row below the first group there will be a sum for each column. Then two rows below the last group of numbers, there will be another sum for only that second group. I will then be calculating the difference between the sums and placing that two rows below the second sum of each column. My macro does calculate the sums ok but the problem is the relative reference of the position does not translate to a recorded macro. The macro keeps using the absolute reference when moving with the arrow keys, etc. Thanks for your help. I'll try to figure out what you were doing with the code to see if I can get it to only sum a specific section. "Jim Thomlinson" wrote: Here it is with the formula at the bottom. This assumes that each column has the same number of rows in it and that there are no populated cells below and/or to the right of the last cell in the last column you want to sum. Sub MakeTotals() Dim rngTotal As Range Dim rngTarget As Range Set rngTarget = LastCell() Set rngTotal = Range(rngTarget, Cells(1, rngTarget.Column)) Set rngTarget = rngTarget.Offset(1, 0) rngTarget.Formula = "=Sum(" & rngTotal.Address & ")" Do Set rngTarget = rngTarget.Offset(0, -1) Set rngTotal = rngTotal.Offset(0, -1) rngTarget.Formula = "=Sum(" & rngTotal.Address & ")" Loop Until rngTarget.Column = 1 End Sub Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Did you wnat a formula in the cell to sum the column or would just a number that is the sum of the column do. The formula is a bit more tricky. -- HTH... Jim Thomlinson "rasinc" wrote: I am trying to program some macros to do some automatic formatting of some imported information. On any single run of the similar information the formatting works quite well. However, if there are added rows or columns, some parts seem to screw up. for example: A series of numbers currently are in cells C8:C18 and the cursor is currently on C8. From the keyboard I wish to hit Shift-End-Down Arrow to select the column to the end and then hit Shift-Down Arrow twice to go to the second line down leaving a blank line but selecting all the cells between C8:C18. I have relative referencing active when I started recording. So at this point I have cells C8:C20 highlighted. This works as long as the column only goes from C8 to C18. However, I cannot control this. Sometimes it may be the data starts at C8 and extends to C22 and I want to select the cells C8:C24 without having to know the extra rows exist. The macros keep recording Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A13").Select but I don't want it to defaul to A13 all the time because it could be longer or even shorter. Anyone have any ideas? I can't find anything on the MS website using searches like cursor move or moving cursor, etc. Ultimately I want to sum the column and have the sum show in the last cell selected, then copy that formula for all the remainingg columns (the number of which I will not know so I was going to use the keyboard commands to Copy the formula, go back up two lines, End Right Arrow to get to the last column, move down two rows and select the cells back to the original and paste. Hope this makes sense. TIA rasinc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative Reference Help Macros | Excel Worksheet Functions | |||
Should Be Easy - Relative Macros | Excel Programming | |||
Relative Value Macros | Excel Programming | |||
Relative References for macros | Excel Programming | |||
Excel Relative Macros | Excel Programming |