excel countif with user input variables

Question

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. Any help would be great. Thanks in advance.

Sub LookForDuplicates()

Dim LastRow As Long

Dim column1 As String
'display an input box asking for column
column1 = InputBox( _
"Please enter column to ckeck")
'if no file name chosen, say so and stop
If Len(column1) = 0 Then
MsgBox "No column entered"

Exit Sub
End If

Dim column2 As String
'display an input box asking for column
column2 = InputBox( _
"Please enter column to insert results")
'if no file name chosen, say so and stop
If Len(column2) = 0 Then
MsgBox "No column entered"

Exit Sub
End If

'-------------------------------------------------------

'This is the original version of my script with set columns which works great..... However I need the user to specify the column to checck and also which column the results will be entered.

  '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"
'-----------------------------------------------------   
LastRow = Range(column1 & Rows.Count).End(xlUp).Row
 With Range(column2 & "1")
.FormulaR1C1 = "=COUNTIF(C2,RC[-3])"
.AutoFill Destination:=Range(column2 & "1" & ":" & column2 & LastRow)
 Range(column2 & "1").Select
ActiveCell.FormulaR1C1 = "Duplicates"

  End With
End Sub

I cannot get this working with the user input variables, 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.

I have updated the answer with your comments. Please take a look at it. I used range as it's much flexible compared to a string that would represent the range/cell.. :) — bonCodigo

bonCodigo bonCodigo · Accepted Answer · 2013-01-14T17:38:08

If you are expecting a String/Text value from your input box then you should specify it,

Dim column1 As String
'display an input box asking for column
column1 = InputBox("Please enter column to ckeck", "Range to Check", , , , 2)

Instead of juggling with a String here, why don't you just use the Range object where user can simply click on either full range or one cell in that column you want to check..

Using a range to get the inputbox data: You main issue seems to be setting range to check column in formula.

Option Explicit

Sub LookForDuplicates()
    Dim LastRow As Long, StartRow as Long
    Dim column1 As Range, column2 As Range

    Set column1 = Application.InputBox("Please enter _ 
            column to ckeck", "Range to Check", , , , , , 8)
    If column1 Is Nothing Then
        MsgBox "No column entered"
        Exit Sub
    End If

    Set column2 = Application.InputBox("Please _ 
                  enter column to insert results", _
                              "Range to Output Results", , , , , , 8)
    If column2 Is Nothing Then
        MsgBox "No column entered"
        Exit Sub
    End If

    LastRow = Cells(Rows.Count, column1.Column).End(xlUp).Row '--updated here
    StartRow = column2.Row '-- here a new code added, assuming that you will have at least one row for column titles 
     With column2
        .FormulaR1C1 = "=COUNTIF(R" & column1.Row _ 
                & "C[-1]:R" & LastRow + 2 & "C[-1],RC[-1])"
        .AutoFill Destination:=column2.Resize(LastRow - StartRow, 1)
    End With
    column2.Offset(-1, 0).FormulaR1C1 = "Duplicates"
End Sub

Output:

enter image description here

excel countif with user input variables

2 Answers