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

L

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
 
D

Don Guillett

Again. You will not make friends posting in multiple groups.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CountA, Subtotal and "move to the first cell in the next column" 4
Crosstab - with one column heading and more than one value field 4
Picking out a Period, in relation to certain cells 6
how do i find the last specific value in a column? 8
I need to help with Excel (beginner) -- year row and month column 2
How to get sequence number in the next column? 2
How can I easily move to the next available cell in a column? 3
how do i Count with in the cell range value is >5 and <=15 1

Top