PDA

View Full Version : VBA relative references



Saisaisai
06-28-2017, 10:45 AM
If I use the VBA recorder suppose I have numbers that start arbitrarily in any row in column A for any arbitrary length.

-I trun on relative reference then hit record
-I hit Crtl + home to start in A1
-Then I hit Ctrl+down arrow and then Ctrl+down again to get to the bottom of the list
-Then I hit the down arrow key
-Then I enter in = sign
-Then I hit Ctrl + up and again Ctrl + up to get to the top of the list
-Then I hit Ctrl +Shift +down and to highlight the entire column and finally hit enter

This works for one list but as soon as I start the list in another row and a different size the formula doesn't work

pleas help

mdmackillop
06-29-2017, 04:31 AM
Try these. Test1 uses your methodology totalling cells in activecell column. It will fail if there is a gap in the range.
Test2 is an alternative

Sub Test1()
Dim r As Range
Cells(1, ActiveCell.Column).Select
If ActiveCell = "" Then
Set r = Range(Selection.End(xlDown), Selection.End(xlDown).End(xlDown))
Else
Set r = Range(Selection, Selection.End(xlDown))
End If
r(r.Count + 1).Formula = "=SUM(" & r.Address & ")"
End Sub
'or
Sub Test2()
Dim rng
Set r = Selection.EntireColumn.SpecialCells(xlCellTypeConstants, 1)
Cells(Rows.Count, Selection.Column).End(xlUp).Offset(1).Formula = "=SUM(" & r.Address & ")"
End Sub