+ Reply to Thread
Results 1 to 15 of 15

Need to properly dimension and define variable range for use in CountIf

  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Need to properly dimension and define variable range for use in CountIf

    Hi,

    I am having trouble setting up my variable range for use in a CountIf.

    First I select column A. Then go through each value in the column, looking for duplicates. I determine if it is a duplicate by checking all the cells above it for a match. The If statement should be false for the first value and true for all duplicate values, hence the "If countif > 1". I believe the problem is the variable range.

    Here is my code. Thanks for any help.

    PHP Code: 
    Sub T()

    Range("A2").Select
    Range
    (SelectionSelection.End(xlDown)).Select
    Dim x 
    As Range
    Dim y 
    As Long
    Dim Rng 
    As String


     
    For Each x In Selection
      y 
    x.Row
      Rng 
    = ("A2:A" y)
      If 
    Application.CountIf(Rng"x") > 1 Then
      x
    .Offset(08) = 1
      End 
    If
     
    Next x
    End Sub 

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Need to properly dimension and define variable range for use in CountIf

    try changing application.countif to worksheetfunction.countf
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    05-16-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to properly dimension and define variable range for use in CountIf

    Andy,

    I tried that and the error is "Type Mismatch" for the Rng in CountIf.

  4. #4
    Forum Contributor
    Join Date
    01-03-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    163

    Re: Need to properly dimension and define variable range for use in CountIf

    Because first argument in countif function must be Range data type and your is String

  5. #5
    Registered User
    Join Date
    05-16-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to properly dimension and define variable range for use in CountIf

    When I change the string to a range... The error message "Object variable or With block variable not set" pops up for Rng = ("A2:A" & y).

  6. #6
    Forum Contributor
    Join Date
    01-03-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    163

    Re: Need to properly dimension and define variable range for use in CountIf

    Because your Rng must be like this:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-16-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to properly dimension and define variable range for use in CountIf

    Thanks for the help so far, but although the code is free of errors, the IF statement is never true. For example when cells A2 and A3 have a value of 50, the statement is not true. In that case it should be true for A3.

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to properly dimension and define variable range for use in CountIf

    AndyLitch in post #4 has the solution, to my eye. Be sure to make that edit. Show us your resulting code if it still isn't working.

  9. #9
    Registered User
    Join Date
    05-16-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to properly dimension and define variable range for use in CountIf

    This is my latest, but still not perfect code...

    When I step through it, I notice the If statement is never true.

    PHP Code: 
    Sub T()

    Range("A2").Select
    Range
    (SelectionSelection.End(xlDown)).Select
    Dim x 
    As Range
    Dim y 
    As Long
    Dim Rng 
    As Range


     
    For Each x In Selection
      y 
    x.Row
      Set Rng 
    Range("A2:A" y)
      If 
    WorksheetFunction.CountIf(Rng"x") > 0 Then
      x
    .Offset(08) = 1
      End 
    If
     
    Next x
    End Sub 

  10. #10
    Registered User
    Join Date
    05-16-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to properly dimension and define variable range for use in CountIf

    Oops. The > should be 1. I modified it to see if I could get it to be true.

    Here it is with > 1

    PHP Code: 
    Sub T()

    Range("A2").Select
    Range
    (SelectionSelection.End(xlDown)).Select
    Dim x 
    As Range
    Dim y 
    As Long
    Dim Rng 
    As Range


     
    For Each x In Selection
      y 
    x.Row
      Set Rng 
    Range("A2:A" y)
      If 
    WorksheetFunction.CountIf(Rng"x.value") > 1 Then
      x
    .Offset(08) = 1
      End 
    If
     
    Next x
    End Sub 

  11. #11
    Forum Contributor
    Join Date
    01-03-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    163

    Re: Need to properly dimension and define variable range for use in CountIf

    remove quot marks from "x.value"

  12. #12
    Registered User
    Join Date
    05-16-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to properly dimension and define variable range for use in CountIf

    YES! Yes yes yes!
    Thank you to Sbarro79, AndyLitch and JBeaucaire!

    I've tripled my VBA knowledge and productivity in just a few days because of helpful people like you.

  13. #13
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need to properly dimension and define variable range for use in CountIf

    Does this accomplish the same thing? It puts a regular formula into column I, all cells at once, then removes the formula leaving the results behind, no looping.
    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,442

    Re: Need to properly dimension and define variable range for use in CountIf

    Hi under00c,

    Here's a way to achieve your desired result without looping and hence much faster and efficient:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  15. #15
    Registered User
    Join Date
    05-16-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Need to properly dimension and define variable range for use in CountIf

    Yes, brilliant. Ill use that one in my code. Im a beginner so looping was easier for me to understand.

    But now Ive learned a new method!

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.17028 seconds
  • Memory Usage 9,225KB
  • Queries Executed 14 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (3)bbcode_code
  • (3)bbcode_php
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (15)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (15)postbit_legacy
  • (15)postbit_onlinestatus
  • (15)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (46):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1