+ Reply to Thread
Results 1 to 8 of 8

Calculating Duplicates with user inpu

  1. #1
    Registered User
    Join Date
    01-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Calculating Duplicates with user inpu

    Hi all, new to VBA so please be gentle.....

    I have a script that check for duplicates and inserts a count in a column, this works fine however the sheets are often different so I need to ask the user which column to check for duplicates and which column to insert the count. I've modified the script, but I'm only getting zero's entered into the destination column. I can't see what's going wrong and don't really know how to debug it. Any help would be great. Thanks in advance.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Calculating Duplicates with user inpu

    Maybe if you remove the quotes:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    01-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Calculating Duplicates with user inpu

    Thanks, but it still fill's with zero's.

    This works fine:
    '-------------------------------------------------------

    'This is the original version of my script with set columns which works great.....

    'LastRow = Range("B" & Rows.Count).End(xlUp).Row
    ' With Range("E1")
    ' .FormulaR1C1 = "=COUNTIF(C2,RC[-3])"
    ' .AutoFill Destination:=Range("E1:E" & LastRow)
    ' Range("E1").Select
    ' ActiveCell.FormulaR1C1 = "Duplicates"
    '-----------------------------------------------------

    But I need to prompt the user for a column to check and a column to put the results as the data changes all the time.

    Thanks again.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Calculating Duplicates with user inpu

    Maybe:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Calculating Duplicates with user inpu

    Thanks very much, but it's still filling with zero's

    The value is calculated correctly in column E which I filled manually and the formula is: =COUNTIF($B:$B,B2)

    Now If I run the macro and select column B to check and column H to fill in H the formula is = =COUNTIF($B:$B,E2) // This is obviously where it's going wrong, if I correct it to B2 it works so the bit that's causing the issue is:

    .FormulaR1C1 = "=COUNTIF(C2,RC[-3])" I'm not sure what the RC[-3] means if anybody could help with that I think it will work.

    Thanks again.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Calculating Duplicates with user inpu

    RC[-3] means same row offset three columns to the left.

  7. #7
    Registered User
    Join Date
    01-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Calculating Duplicates with user inpu

    Thanks,

    I still cannot get this working, apologies if I'm missing something but I can't find any resources on this....

    The formula: =COUNTIF($B:$B,B2) works except when in the macro.

    I need to add this line to the macro replaced with variables from user input like: =COUNTIF($column1:$column1,column12) but I keep getting syntax errors.

    Thanks again.

  8. #8
    Registered User
    Join Date
    01-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Calculating Duplicates with user inpu

    Arrrrgh this is driving me crazy.

    I simply want to insert this formula: =COUNTIF($B:$B,B2) with variables to replace B and 2 to increase when filled so if a user if prompted fo a column and they input C and then prompted for another column to output and they choose D

    Column D will be filled with the formula =COUNTIF($C:$C,C2) then this will fill down

    Now this almost does it: .FormulaR1C1 = "=COUNTIF(C2,RC[-3])" but you cannot select a column. I've tried everything is this possible???

+ 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1