Calculating Duplicates with user inpu

[carbonuk]

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.

[xladept]

Maybe if you remove the quotes:

Please Login or Register  to view this content.

[carbonuk]

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.

[xladept]

Maybe:

Please Login or Register  to view this content.

[carbonuk]

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.

[xladept]

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

[carbonuk]

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.

[carbonuk]

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???