| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountA, Subtotal and "move to the first cell in the next column"
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountA, Subtotal and "move to the first cell in the next column"
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountA, Subtotal and "move to the first cell in the next column"
On Oct 16, 3:26 pm, "Don Guillett" wrote:
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 "LongBeachGuy" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - here |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountA, Subtotal and "move to the first cell in the next column"
On Oct 16, 3:26 pm, "Don Guillett" wrote:
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 "LongBeachGuy" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - I am sorry about the re-posting. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CountA, Subtotal and "move to the first cell in the next colum
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. "LongBeachGuy" wrote: On Oct 16, 3:26 pm, "Don Guillett" wrote: 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 "LongBeachGuy" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - I am sorry about the re-posting. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is this possible: =counta(a1:a30) behind "text" same cell? | Excel Worksheet Functions | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
| CountA, Subtotal and "move to the first cell in the next column" | Excel Worksheet Functions | |||
| CountA, Subtotal and "move to the first cell in the next column" | Excel Discussion (Misc queries) | |||
Linear Mode
