CountA, Subtotal and "move to the first cell in the next column"

[LongBeachGuy]

Help,
I am using the"CountA" Worksheet function to paste data from another
sheet into the 1st empty cell starting at "B2". It works fine all the
way to row 14 but it continues to row 15.

I want it to reset the CountA function to 2 and not keep count 15 and
start all over at column "D2" down to "D14" and repeat to column "F2"
and so on.

What as I doing wrong? How do I do it?

MY CODES:

Sheets("Sheet1").Select
Range("A1").Select
'------------------------------------------------------------------------
''Column_Index_For_Calculation was initailzed to 0.
-----------------------------------------------------------------------

Column_Index_For_Calculation = Column_Index_For_Calculation + 1
second_MyRow = ActiveCell.row
second_MyCol = ActiveCell.Column
NumColumns = My_Current_Range.Columns.Count
'-----------------------------------------------------------------------------------------------------------
Sheets("The Calculations").Select
Calculation_Column = second_MyCol + Column_Index_For_Calculation
-----------------------------------------------------------------------------------------------------------
'Calculation_Column is at 4
Change_The Columns is at True
------------------------------------------------------------------------------------------
New_Calculation_Column: If Change_The_Columns = True Then
Selection.End(xlUp).Select
Selection.Offset(1,
0).Select
'Next_Line = 2
ActiveCell.Select
End If
'-----------------------------------------------------------------------------------------------------------------------
Next_Line = Application.WorksheetFunction.CountA(Range("b:b"),
NumColumns)
'---------------------------------------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------------------------------------
' Next line is 15 not 2. I cannot make it to restart the count on the
next column.

'------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------
Cells(Next_Line, Calculation_Column) = Date_For_Calculation '
DATES
Cells(Next_Line, (Calculation_Column + 1)) = Diff_for_Calculations
'DIFF
'----------------------------------------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------------------------------------
A B C D F
GAMES DATES DIFF DATE DIFF
1st item 1/17/2006 14 11/25/07 4
2nd item 1/31/2006 21
3rd item 2/21/2006 143
4th item 7/14/2006 25
5th item 8/8/2006 59
6th item 10/6/2006 35
7th item 11/10/2006 84
8th item 2/2/2007 18
9th item 2/20/2007 31
10th item 3/23/2007 18
11th item 4/10/2007 7
12th item 4/17/2007 3
13th item 4/20/2007 4
14th item 4/24/2007 52
15th item 11/25/2005 4

 

[Don Guillett]

Pls do not post in more than one group. Do you want the answers here or in
misc?

 

[LongBeachGuy]

Pls do not post in more than one group. Do you want the answers here or in
misc?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software











- Show quoted text -

here

 

[LongBeachGuy]

Pls do not post in more than one group. Do you want the answers here or in
misc?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software











- Show quoted text -

I am sorry about the re-posting.

 

[JLGWhiz]

Since you are using the second argument for the CountA() function, you will
get one more than the count for argument 1. That is why you are getting 15.
If you want 14, then eliminate the second argument like:

Next_Line = Application.WorksheetFunction.CountA(Range("b2:b15"))

If you use Range("B:B"). you are also including the header. I suggest you
read up on COUNTA in the Excel help files.