Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Compare names to make numerical entry (MS Excel 2000 SP3)

    I am floundering - out of my depth & need help!

    In a worksheet I have a list of names. Below the list I am recording visits & when a visit is recorded by a name incrementally against that name in the list the numerical value of the number of visits made is increased by one.

    I had assistance to produce the code that is used for this function but it needs to be modified to meet the working situation. Of the two changes I have made so far one works & the other produces an odd result. There are other changes I wish to make but I have no idea on how to achieve them.

    Is a request for assistance of this nature within the scope of this Forum? I have received a great deal of assistance previously, for which I remain very grateful, & have no wish to ask for more than is expected. What I am trying to achieve has no business or commercial basis whatsoever. It is solely for the records I keep as the Secretary of a social group.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Compare names to make numerical entry (MS Excel 2000 SP3)

    If you provide us with details of what you need and even what you currently have, I am sure that we can provide some help to you.

    We do not keep tabs on how much people have asked for help. Our goals are to educate and to provide assistance...

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare names to make numerical entry (MS Excel 2000 SP3)

    Sure, give us details and if possible a copy of the workbook with any confidential information altered so that it is unrecoganizable. We'll be glad to try to help.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Compare names to make numerical entry (MS Exce

    Thank you for your encouraging reply. My worksheet with is code is attached. I have made some notes on the Working Test worksheet to indicate my inadequacies so far.

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Compare names to make numerical entry (MS Exce

    Thank you very much for your reply. Please see my response to Steve for my worksheet.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare names to make numerical entry (MS Exce

    I am afraid that I can't figure out what you are trying to do from your worksheet and code. I see the following problems:

    1- Your code defines a variable named isEmpty. IsEmpty is the name of a VBA function. Defining a variable that has the same name as a VBA function could cause all kinds of weird problems.

    2- It looks like you are trying to use the worksheet selection change event routine to set the isEmpty variable to control what happens in the worksheet change event routine. Unfortunately, the worksheet change event occurs before the worksheet selection change, so this will not work.

    3- Your THE_MEMBER_RANGE includes cell C50 which does not contain a name. The way that you are using the InStr function (backward I think), when you search for the cell C50 in cell you are entering, you will get a hit. This is what is causing cell E49 to update (I also thing that the index value i in your loop is off by one row).

    Basically, I think that the code that you have is not going to do what you want it to do. Maybe if you give us an explanation of what you want it to do we might be able to come up with something that works better.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Compare names to make numerical entry (MS Exce

    Thank you for your comments - it is so evident that I need help!

    What am I trying to do? My social group has 47 members & one of our many activities is to visit other groups. I wish to record & analyse such visits by members of my group in the following ways:

    1. The fact that a visit has been made is recorded in the data area. Under the list of members a visit record is entered manually by the date as Month/Day, the name of the Group visited & a list of the names of those who made that visit. I have added several examples of this visiting data to the Working test worksheet.

    2. When a name has been added in the data the number of visits against that name in the above list of members is to increase by one. Initially from blank to 1 & then to 2 & so on each time that a particular name is entered in a visiting record.

    3. At present the name data has to be entered in column D & the numeric increase appears, albeit one row above the corresponding name, in column E. For the month of April this is where I want it to appear. But I want to enter the name data also in column E. And for May I want to enter the name data in column F & the results to appear also in column F. Again I have added several examples.

    4. The rest of the analyses I need for my reports are implemented by formulas in the cells infilled pink.

    I hope this explains my aims.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare names to make numerical entry (MS Exce

    You don't need VBA code to do that, you can use formulas.
    In the attached version, the formula in E3 is =COUNTIF(E$57:E$65536,$C3) and this has been filled down to E49, then right to P49. The formula counts how many times the name in C3 occurs in the range from E57 down. The $ signs in the formula indicate that the reference immediately following it is absolute, i.e. won't be changed when filling down or right.
    To avoid zero counts from being displayed, the Number format for E3:P49 has been set to <code>0;;</code>
    The semicolons suppress the display of negative and zero values.

  9. #9
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Compare names to make numerical entry (MS Exce

    Hans thank you. Your solution solves all my problems so simply.

    But it does affect the formula in the row E53:P53. This needs to be amended to display only the number of those who have made a visit (the number greater than zero). At present it displays the number of names in the list. Can you show me how to do that please?

    Again my thanks for your contribution.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare names to make numerical entry (MS Exce

    Enter the formula

    =COUNTIF(E3:E49,">0")

    in E53 and fill right to P53. You can use the same number format as in E3:P49 if you want to hide zeros.

  11. #11
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Compare names to make numerical entry (MS Exce

    That's wonderful - job done. Thank you!

  12. #12
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Compare names to make numerical entry (MS Exce

    There is one more formula I cannot resolve without assistance. In the attachment cell M50 has the formula =COUNTIF(B2:L47,">0") and obviously it counts the number of times data has been entered in the cells between B2 and L47.

    However in this cell I want to count the number of those in the name column who have actually visited whether it is one or 10 or any number of visits. How do I modify COUNTIF to do this or should I use a different formula?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare names to make numerical entry (MS Exce

    Count the number of postivie entries in M2:M47:

    =COUNTIF(M2:M47,">0")

  14. #14
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Compare names to make numerical entry (MS Exce

    Thank you Hans. I now realise that I have tunnel vision - as well!

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare names to make numerical entry (MS Exce

    Sometimes you just need someone else to look at a problem with a fresh pair of eyes...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •