Last cell creates circular reference!

  • Thread starter Thread starter aapp81
  • Start date Start date
A

aapp81

i have several columns which all differ in lengths (some have more rows
than others...

i already have a sub that will find the longest column... but now i
need a COUNTA formula i can write in that cell that will only calculate
A$2:A$last cell in column... and i can't use A:A b/c my formula is in
that row so it'll create a circ. ref. and i can't create another column
b/c this is the way it needs to be calculated...
also, i need this formula to be able to paste into another cell and
it'll change the column (A to C, etc...) accordingly...
please help!
 
OK, so you are using a sub to find the last cell in a
column, therefore, you need to use the sub to insert your
formula.

whichever method you use to determine the last cell,
should be able to return a reference of that last cell.

Assuming you start at Column A use endXldown to find last
row, and then go right one column using activecell.offset
(0,1), and test whether or not that cell has a value,
then check if the next row has a value and if yes, use
end XL down, and loop until you have completed your
column range; you can return the activecell.row property
(say 275), add 1 to the returned value, and then select
cell A276 and use something like

dim scol as string
scol = Chr$(ActiveCell.Column + 64)
ActiveCell.Formula = "=counta(" & scol & "2:" & scol &
ActiveCell.Row - 1 & ")"

enclose the 2 lines within a loop to cover your column
range, and it should work

Steve
 
thanks for the reply this is what i'm trying to do:

Sub CountProducts()
Dim scol As String
Dim selRange As Range

'THIS WILL SELECT ALL MY DATA MINUS THE HEADING
Range("A2").Select
Set SelRng = Selection.CurrentRegion
If SelRng.Row = 1 Then _
Set SelRng = SelRng.Offset(1, 0).Resize(SelRng.Rows.Count - 1)
SelRng.Select

'THIS IS YOUR CODE WHICH WORKS GREAT
scol = Chr$(ActiveCell.column + 64)
ActiveCell.Formula = "=counta(" & scol & "2:" & scol & ActiveCell.Row
1 & ")"

End Sub

but i'm trying to incorporate this:
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A" & LastRow + 3).Select
so that it lists the "result" or counta of your code in the lastrow+
of columns A:S

but don't forget all my columns are different lenghts so if the longes
one has 40 rows than all the counta must be in A43, B43, C43, ....
etc..
 
Back
Top