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

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

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

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