Inserted rows, now need Counta function

Hi

I've got a data dump.  I've figured out how to insert a blank row after a 
change in name in column A and insert "Total" - so...

bill....
bill....
bill Total - 
bob....
bob....
bob....
bob Total - 

What I need in column C next to total is to insert the COUNTA function for 
each person.

Any ideas?

cheers


0
Utf
11/17/2009 6:00:02 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
803 Views

Similar Articles

[PageSpeed] 25

You say your code inserts a blank row after a change in name and inserts 
"Total", but your example doesn't show this. Can you post the code you are 
using to do your "insert"? It will probably be easier to handle the 
insertion of the COUNTA function at the same time the word "Total" is 
inserted.

-- 
Rick (MVP - Excel)


"Adam" <Adam@discussions.microsoft.com> wrote in message 
news:4E73648B-EB84-48C9-AA0D-7429301F5622@microsoft.com...
> Hi
>
> I've got a data dump.  I've figured out how to insert a blank row after a
> change in name in column A and insert "Total" - so...
>
> bill....
> bill....
> bill Total -
> bob....
> bob....
> bob....
> bob Total -
>
> What I need in column C next to total is to insert the COUNTA function for
> each person.
>
> Any ideas?
>
> cheers
>
> 

0
Rick
11/17/2009 6:33:02 PM
It's not beautijful but it works so far!


Range("A5").Select

        Do Until Len(ActiveCell) = 0
            If ActiveCell.Offset(1, 0) = ActiveCell Then
                ActiveCell.Offset(1, 0).Select
            Else
                ActiveCell.Offset(1, 0).EntireRow.Insert
                ActiveCell.Offset(0, 1).Copy
                ActiveCell.Offset(1, 1).PasteSpecial
                ActiveCell.Offset(-1, 1).Copy
                ActiveCell.Offset(0, 1).PasteSpecial
                ActiveCell.Offset(0, 1).Value = "TOTAL"
                ActiveCell.EntireRow.Font.ColorIndex = 3
                ActiveCell.Offset(1, -2).Select
            End If
        Loop

Many thanks

"Rick Rothstein" wrote:

> You say your code inserts a blank row after a change in name and inserts 
> "Total", but your example doesn't show this. Can you post the code you are 
> using to do your "insert"? It will probably be easier to handle the 
> insertion of the COUNTA function at the same time the word "Total" is 
> inserted.
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Adam" <Adam@discussions.microsoft.com> wrote in message 
> news:4E73648B-EB84-48C9-AA0D-7429301F5622@microsoft.com...
> > Hi
> >
> > I've got a data dump.  I've figured out how to insert a blank row after a
> > change in name in column A and insert "Total" - so...
> >
> > bill....
> > bill....
> > bill Total -
> > bob....
> > bob....
> > bob....
> > bob Total -
> >
> > What I need in column C next to total is to insert the COUNTA function for
> > each person.
> >
> > Any ideas?
> >
> > cheers
> >
> > 
> 
> .
> 
0
Utf
11/18/2009 9:17:01 AM
Sorry I put C in the original e-mail but I meant E

Column A is code for that person, B is Surname, C is 1st name, D has "Total" 
enterred and E is where i would need the formula/count

cheers


"Rick Rothstein" wrote:

> You say your code inserts a blank row after a change in name and inserts 
> "Total", but your example doesn't show this. Can you post the code you are 
> using to do your "insert"? It will probably be easier to handle the 
> insertion of the COUNTA function at the same time the word "Total" is 
> inserted.
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Adam" <Adam@discussions.microsoft.com> wrote in message 
> news:4E73648B-EB84-48C9-AA0D-7429301F5622@microsoft.com...
> > Hi
> >
> > I've got a data dump.  I've figured out how to insert a blank row after a
> > change in name in column A and insert "Total" - so...
> >
> > bill....
> > bill....
> > bill Total -
> > bob....
> > bob....
> > bob....
> > bob Total -
> >
> > What I need in column C next to total is to insert the COUNTA function for
> > each person.
> >
> > Any ideas?
> >
> > cheers
> >
> > 
> 
> .
> 
0
Utf
11/18/2009 9:29:02 AM
Adam,
this may do what you want:


Sub AddTotals()
    Dim ws1 As Worksheet
    Dim rn As Long
    Dim counta As Long

    'name of worksheet where data stored
    'change as required

    Set ws1 = Worksheets("Sheet1")

    'start search from row 5
    rn = 5

    'row marker used to create
    'counta formula
    counta = rn

    With ws1

        Do Until .Cells(rn, 1).Value = ""

            If .Cells(rn + 1, 1) <> .Cells(rn, 1) Then

                With .Cells(rn, 1)

                    .Offset(1, 0).EntireRow.Insert

                    .Offset(1, 1).Value = .Offset(0, 1).Value

                    .Offset(1, 2).Value = .Offset(0, 2).Value

                    .Offset(1, 3).Value = "TOTAL"

                    .Offset(1, 4).Formula = _
                    "=Counta(A" & counta & ":A" & rn & ")"

                    .Offset(1, 0).EntireRow.Font.ColorIndex = 3

                End With

                'skip blank cell
                'created by row insert
                rn = rn + 1

                'set count start range
                counta = rn + 1

            End If

            'increment row
            rn = rn + 1

        Loop

    End With

End Sub

-- 
jb


"Adam" wrote:

> Sorry I put C in the original e-mail but I meant E
> 
> Column A is code for that person, B is Surname, C is 1st name, D has "Total" 
> enterred and E is where i would need the formula/count
> 
> cheers
> 
> 
> "Rick Rothstein" wrote:
> 
> > You say your code inserts a blank row after a change in name and inserts 
> > "Total", but your example doesn't show this. Can you post the code you are 
> > using to do your "insert"? It will probably be easier to handle the 
> > insertion of the COUNTA function at the same time the word "Total" is 
> > inserted.
> > 
> > -- 
> > Rick (MVP - Excel)
> > 
> > 
> > "Adam" <Adam@discussions.microsoft.com> wrote in message 
> > news:4E73648B-EB84-48C9-AA0D-7429301F5622@microsoft.com...
> > > Hi
> > >
> > > I've got a data dump.  I've figured out how to insert a blank row after a
> > > change in name in column A and insert "Total" - so...
> > >
> > > bill....
> > > bill....
> > > bill Total -
> > > bob....
> > > bob....
> > > bob....
> > > bob Total -
> > >
> > > What I need in column C next to total is to insert the COUNTA function for
> > > each person.
> > >
> > > Any ideas?
> > >
> > > cheers
> > >
> > > 
> > 
> > .
> > 
0
Utf
11/18/2009 1:15:01 PM
John

thanks for your time - i think i've just had a break through as the below 
seems to be working for me.  I will be stealing the best bit from yours 
though to improve what i've got.

Many thanks

    Range("A5").Select

        Do Until Len(ActiveCell) = 0
            If ActiveCell.Offset(1, 0) = ActiveCell Then
                ActiveCell.Offset(1, 0).Select
            Else
                ActiveCell.Offset(1, 0).EntireRow.Insert
                ActiveCell.Offset(0, 1).Copy
                ActiveCell.Offset(1, 1).PasteSpecial
                ActiveCell.Offset(-1, 1).Copy
                ActiveCell.Offset(0, 1).PasteSpecial
                ActiveCell.Offset(0, 1).Value = "TOTAL"
                ActiveCell.Offset(0, 2).Value = "=Countif(A:A,'" & 
ActiveCell.Offset(-1, -2).Value & "')"
                ActiveCell.EntireRow.Font.ColorIndex = 3
                ActiveCell.Offset(1, -2).Select
            End If
        Loop
    
    Columns("E:E").Select
    Selection.Replace What:="'", Replacement:=""""

"john" wrote:

> Adam,
> this may do what you want:
> 
> 
> Sub AddTotals()
>     Dim ws1 As Worksheet
>     Dim rn As Long
>     Dim counta As Long
> 
>     'name of worksheet where data stored
>     'change as required
> 
>     Set ws1 = Worksheets("Sheet1")
> 
>     'start search from row 5
>     rn = 5
> 
>     'row marker used to create
>     'counta formula
>     counta = rn
> 
>     With ws1
> 
>         Do Until .Cells(rn, 1).Value = ""
> 
>             If .Cells(rn + 1, 1) <> .Cells(rn, 1) Then
> 
>                 With .Cells(rn, 1)
> 
>                     .Offset(1, 0).EntireRow.Insert
> 
>                     .Offset(1, 1).Value = .Offset(0, 1).Value
> 
>                     .Offset(1, 2).Value = .Offset(0, 2).Value
> 
>                     .Offset(1, 3).Value = "TOTAL"
> 
>                     .Offset(1, 4).Formula = _
>                     "=Counta(A" & counta & ":A" & rn & ")"
> 
>                     .Offset(1, 0).EntireRow.Font.ColorIndex = 3
> 
>                 End With
> 
>                 'skip blank cell
>                 'created by row insert
>                 rn = rn + 1
> 
>                 'set count start range
>                 counta = rn + 1
> 
>             End If
> 
>             'increment row
>             rn = rn + 1
> 
>         Loop
> 
>     End With
> 
> End Sub
> 
> -- 
> jb
> 
> 
> "Adam" wrote:
> 
> > Sorry I put C in the original e-mail but I meant E
> > 
> > Column A is code for that person, B is Surname, C is 1st name, D has "Total" 
> > enterred and E is where i would need the formula/count
> > 
> > cheers
> > 
> > 
> > "Rick Rothstein" wrote:
> > 
> > > You say your code inserts a blank row after a change in name and inserts 
> > > "Total", but your example doesn't show this. Can you post the code you are 
> > > using to do your "insert"? It will probably be easier to handle the 
> > > insertion of the COUNTA function at the same time the word "Total" is 
> > > inserted.
> > > 
> > > -- 
> > > Rick (MVP - Excel)
> > > 
> > > 
> > > "Adam" <Adam@discussions.microsoft.com> wrote in message 
> > > news:4E73648B-EB84-48C9-AA0D-7429301F5622@microsoft.com...
> > > > Hi
> > > >
> > > > I've got a data dump.  I've figured out how to insert a blank row after a
> > > > change in name in column A and insert "Total" - so...
> > > >
> > > > bill....
> > > > bill....
> > > > bill Total -
> > > > bob....
> > > > bob....
> > > > bob....
> > > > bob Total -
> > > >
> > > > What I need in column C next to total is to insert the COUNTA function for
> > > > each person.
> > > >
> > > > Any ideas?
> > > >
> > > > cheers
> > > >
> > > > 
> > > 
> > > .
> > > 
0
Utf
11/18/2009 1:38:06 PM
Reply:

Similar Artilces:

Function in XL or in VBA for XL that pulls numeric digits from a t
Using XL 2003 and 97 In another product (ACL) there is a function called Incude() which has the following paramaters: INCLUDE(string,characters_to_include) INCLUDE( ) returns a string consisting of only those characters from string that appear in characters_to_include, in the same order that they appear in string. Example also from ACL: INCLUDE("123 any street","0123456789") ="123" Or if Cell B1 contained A123B456C789 [Assuming XL had an Include() function] INCLUDE("B1","0123456789") ="123456789&...

MLA format in bibliography function
MLA formatting changed in 2009, is there a way to update the way bibliographies are formatted without having to manually change each instance? For example, it is no longer recommended that titles are to be underlined; they should be italicized. Also the publication medium should be included and urls are not required, the website name is. There is no patch out yet. If you have a decent XSLT programming knowledge, you could fix the thing yourself as it is 'open source'. Yves -- BibWord : Microsoft Word Citation and Bibliography styles http://bibword.codeplex.com ...

Insert a reference table and have different size cells
I have a weekly job report I fill out where I record what jobs each employee has been working on for that day. I would like to add another table to the worksheet where I would be able to see a list of jobs to perform. Then I could record where some one worked and what he did. The problem is I need to make the cells in the second table smaller. Any one know how I can do this? ...

Need help building a model in Excel
I'm modeling a two-step industrial process for work Step #1 occurs anytime (date or day of week), step #1 requires 21 days to completion Step #2 follows step #1, but occurs ONLY on Mondays, so, for example,for a particular instance, if (start date of step #1 + 21 days) is a Wed, step #2 doesn't commence until the following Monday. I'm counting pieces (widgets) that will pass from step#1 to step#2, and this model helps to provide estimates for the resources needed to complete step #2 I've having intellectual problems trying to slove this So input is count of widgets ente...

Combox
I have written a nice little function that returns a list of values that i would like to use in a combo box. So far, It seems I can only pull from a named range for the values. The background: The contents of the combo need to vary depending on the value in the box next to it. Basically, we have a big lookup table and we use the value to the left of the combo box to filter that data through a custom function. The function comes in because we need to remove dupes, blanks, and clean it up somewhat. Is there any way, even a round-about way, to get it to use the function? Some kind of tempor...

Need to put a name in the column heading
I am a new user and I cannot seem to put titles in the column headings. Thanks for your help. The help section was not of any use for the searches I made. If you mean that you want to replace the "A", "B", etc., the borders are not customizable. -- Jim Rech Excel MVP "windsorhartung" <windsorhartung@discussions.microsoft.com> wrote in message news:7C556A73-3230-4FB8-A9AA-EAA36E5B15C3@microsoft.com... |I am a new user and I cannot seem to put titles in the column headings. | Thanks for your help. The help section was not of any use for the searches ...

My function won't function. Please assist?
I want the BusinssCity field & BusinessZip field to be automatically filled in with Durham and 27710 respectively if the BusinessName = Duke. What's wrong with this code? Private Sub BusinessName_AfterUpdate() ' If BusinessName = Duke, then fill in "Durham" for BusinessCity and "27710" for BusinessZip Select Case BusinessName Case "Duke" BusinessCity = "Durham" BusinessZip = "27710" End Select End Sub Thanks! Try: Private Sub BusinessName_AfterUpdate() ' If BusinessName = D...

Inserting table/query headers and footers
I want my students to insert their names in the header or footer of a report or query on assignments. How do I change the information in the header and footer of a table or query? I know how in a report, but not on the other objects. You can't. Neither Tables nor Queries are intended to be printed: only Reports are. For that reason, they haven't put a whole lot of functionality for things like footers or headers in other objects. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Hobson School" <Hobson School@discussions.m...

Inserting Template truncates subject line text
When I insert a template into an email being written in CRM, it seems to truncate the original text in the subject line, and replace it with that of the template. This is not helpful, as it removes the subject line that should stay with the email, and even though i'm sure another identifier will be added to the email once sent, I have had to type out a manual subject line. An example of this is using templates to store a signature block. Since this didn't happen in 1.2, and I can;t see any way of turning it off, can anyone suggest a workaround, rather than copying & pasting...

Need to Block Sender/Domain?
I am using outlook express and want to block a user by name or domain. It is junk mail. I am using XP but when I select any mail then message the 'Blocker Sender' option is not available, light gray rather than black text to select. I cannot get the option for any of my mail. I have also tried to block the sender or domain through hotmail, I know in the past I was able to block addresses, but cannot now. On hotmail I go to options, mail, but there is no option to block sender. How do I bock a sender or domain, has there been a change on how to do via outlook and the hotmail site? I...

Do I need a Smart Host?
We jsut upgraded Exchange 5.5 to 2003 in a win2k AD domain. We had no problems sending outgoing mail thru 5.5. With Ex2003, in event viewer I notice that any outside site looking for reverse ptr ( AOL, mercercounty.org, etc) rejects our mail. The mail will sit in the queue and retry, retry, retry, etc. We never had a problem sending to these sites with 5.5 Our email is hosted by Verizon, and they have assured me that there is a reverse ptr setup for us. I tend to believe them as this just occurred with the upgrade. Is Ex2003 sending directly to these sites, bypassing Verizon? I ...

Check boxes need to return a number
Hi, I have not done anything with check boxes before, so I am reall new at this. I am trying to do a sheet that will have check boxes tha when checked will return a number from another sheet. When the box i not checked, it should return a "0". If I just use a check column, don't have any problems with the formula to pull the number from th other sheet when you put an "x" in the column but I would like to us check boxes or buttons. I will be sharing this will peers and woul like to just make it a simple click. I have attached a copy of what have completed. Only...

How to combine rows?
How can I do this? Name| Company | City Jon Smith| IBM| Toronto Jon Smith| TD| montreal into this: Name| Company | Company2 | City | City 2 Jon Smith| IBM | TD | Toronto | Montreal Thank you!! Insert columns. Select Company column, right click to Insert column. Select City column, right click to insert column. You will then have your 5 columns. "Dina" wrote: > How can I do this? > > Name| Company | City > Jon Smith| IBM| Toronto > Jon Smith| TD| montreal > > into this: > > Name| Company | Company2 ...

cell function
Is there a way to have a cell in a spreadsheet be represented as a symbol? example: if i want cell A1 to show a checkmark or an x when the cell is checked, how would i do that (if it can be done)? greatly appreciated Not sure I totally get how you're envisioning it to work. I'm guessing you want to click on the cell at which point it will put an x in the cell then take it off. 2 ways to do it. 1) (I wouldn't do this) use forms, and put a checkbox over the cell. I've found the checkboxes are always tough to manage, expecially if people are inserting rows, resizi...

How do I insert the date the file was saved in the MS Excel foote.
I am trying to display the date the file was last saved in the footer field. the defualt date code displays the date the file was printed. I would appreciate any help with this. Hi kacate Try http://www.rondebruin.nl/print.htm#Saved -- Regards Ron de Bruin http://www.rondebruin.nl "kacate" <kacate@discussions.microsoft.com> wrote in message news:9F65885A-DC2D-4515-A0D2-5E39B7905FCF@microsoft.com... >I am trying to display the date the file was last saved in the footer field. > the defualt date code displays the date the file was printed. I would > apprec...

Formula Needed for Corresponding Blank Cell
Hi All, Well that didn't work: =IF($B$7:$B$689<>"",IF($W$7:$X$689="","SOMETHING IS MISSING",""),"") And neither did this: =IF($B$7:$B$689<>"",IF(OR($W$7:$W$689=""),OR($X$7:$X$689=""),OR($Y$7:$Y $689="")),"SOMETHING IS MISSING",""),"") No laughing. I'm trying. And I did use Ctrl-Shift-Enter. It's supposed to catch blank cells in columns W, X, or Y if column B in the same row isn't blank. Column B is a date and W, X and Y are ...

Inserting cover page in front of a template page
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, <br><br>I'm preparing a proposal and using a template from my word project gallery. I want the template page to show up as page 2, so I can insert a cover page as page 1. (I don't want my logo from my template to show on the cover page) How can I insert a blank page first, to create my cover page, before my template page? <br><br>My template page 1 has my logo, and when I try to insert a cover page, it places it on top of my template. <br><br>Thanks for your help!...

Function or insert/into temp table
I have rewritten a function that calls a function recusively that passes back a table from a stored procedure. It now seems that I have to call that procedure over and over from another list that I get from a common table expression. Something like: with MyCTE as { .... } INSERT INTO #Temp SELECT Account FROM MyCTE Read through #Temp and execute the stored procedure for each row. Is there a way to do this without a cursor? Thanks, Tom "tshad" <t@dslextreme.com> wrote in message news:%23qfThagyKHA.244@TK2MSFTNGP06.phx.gbl... >I have re...

inserting photos
I have created a spread sheet with the student demographics for our school. I want to include the student photos. I have the photos in a folder. I have a text file that matches student id numbers to photo names. What is the best (is there a best) way to proceed? Thanks, Jeremy On Feb 24, 1:02=A0am, "Jeremy Schubert" <j...@nospam.hotmail.com> wrote: > I have created a spread sheet with the student demographics for our schoo= l. > I want to include the student photos. =A0I have the photos in a folder. = =A0I > have a text file that matches student id numbers to ph...

Copy and Insert Row
Hi, The excel vba code does not generate the correct result and incomplete as I've no idea on how to rectify the codes to achieve the intended results Below is the extract of vba codes : - =A0 Dim C As Range =A0 Dim X As Long =A0 Dim LastRowX As Long =A0 Dim LastRowY As Long =A0 Dim CellsToColor() As String =A0 LastRowX =3D Worksheets("Wrksheet X").Cells(Rows.Count, "A").End (xlUp).Row =A0 LastRowY =3D Worksheets("Wrksheet Y").Cells(Rows.Count, "A").End (xlUp).Row =A0 With Worksheets("Wrksheet X") =A0 =A0 ReDim CellsToC...

excel file deleted. Now howdo you delete file NAME
I deleted an excel file I had typed. Now I can't figure out how to delete the file name. It still shows up when you click on open to see the files. You can still open this but there is nothing there. Need to delete FILE NAME you can configure to show 0 files or open n files until disapear the deleted file. "NANMAC" wrote: > I deleted an excel file I had typed. Now I can't figure out how to delete > the file name. It still shows up when you click on open to see the files. > You can still open this but there is nothing there. Need to delete FILE NAME ...

help on and/or formula needed
I have a formula that I cannot get right and have been working on it for several days now and would appreciate a "parenthesis" in the right place. The background : I have to calculate the total of a column based on the year of purchase of an item (depreciated over 5 years) BUT, the first 2 items only get included in the total if the column year is the year of purchase, i.e. the first year. Cells: o12:w12 contain the years to be totalled for K15/16 are the years for the first 2 items K19:K41 are the years for the remaining items BB15:BB41 are the values to be totalled all Year cell...

Can I split my worksheet and have different size columns and rows.
I like the main part of my spreadsheet, buy would like to add information with different size columns and rows from the original sheet. I want the original rows to remain the same but would like the addition to be smaller rows to get more info in there. Can I do it? tommy Row heights and Column widths are properties of the entire row and column and cannot be altered for different sections of a worksheet. I hesitate to mention the dreaded "merge cells" feature which allows you to merge two or more cells into one big cell. If you're just doing this for entering text you wo...

Need to display blank fields
It would be really good to have 26 records display per page (or at least on 1 page) even if there are only 2 records. In other words, today I have 2 checks but I need 24 blank records after to make up the entire deposit slip. Someone mentioned a line method but I do not believe that would get me the numbers 3. - 26. along side the blank records. Any ideas? "Michael" <michael@kisielrudnik.com> wrote > It would be really good to have 26 records > display per page (or at least on 1 page) even > if there are only 2 records. In other words, > today I hav...

Form Field/Function problem
On my main form, I have 4 boxes/fields as numbers. I have a function that takes the score from 3 of those fields and automatically adds them together and returns the total score to that 4th box/field. That part works the way it should. The problem I'm having and can't get to work is that if all 3 of the separate score boxes have a score of 60 or higher then the total score should be colored green, but if any of the 3 separate scores are below 60 then the total score should be red. I've entered some code that would check the scores of the 3 individual scores and change...