Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel macros VBA
I am trying to create a macro in excel that prompts a user to input a column to check and a column to output the results to.
 
for example I would like to check column a for duplicates and output a count to column f
 
I've tried everyhting but cannot get this working:
 
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.....

  '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
 
When the script runs it prompts for the column to check and the column for the output as expected but if fills the column with zero's even though there are duplicates.
 
It seems to be an issue with the fill formula, which ever column is selected to check for duplicates the formula output always reads:
=COUNTIF($B:$B,E2)
It makes no difference which column I selected to check this one should of been A not B. As soon as I change it to A it works, but it will not fill correctly.
 
So i guess this line:
 
  .FormulaR1C1 = "=COUNTIF(C2,RC[-3])" 
 
Is incorrect but I cannot get it working.
Posted 5-Mar-13 4:46am
Edited 6-Mar-13 0:40am
v3
Comments
ryanb31 at 5-Mar-13 10:23am
   
What specifically is not working?
Paul Hayman at 5-Mar-13 10:26am
   
Sorry:
 
When the script runs it prompts for the column to check and the column for the output as expected but if fills the column with zero's even though there are duplicates.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

First of all, you need to know something about cell referencing: Overview of formulas[^]. Then have a look here at: COUNTIF formula[^]. Define criteria as constant calue or absolute reference to the cell.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Solution if anybody else might find this useful:
 
The issue was even though column 1 was entered as a Column Reference H for example the COUNTIF function required this as a numeric reference so added an extra variable on the column1 value to the numeric value and modified the formula to suit. All working now:
 
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
    ColumnNumber = Columns(column1).Column
 '   MsgBox ColumnNumber
    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.....

  '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(C" & ColumnNumber & ",C" & ColumnNumber & ")"
    .AutoFill Destination:=Range(column2 & "1" & ":" & column2 & LastRow)
     Range(column2 & "1").Select
    ActiveCell.FormulaR1C1 = "Duplicates"
    
  End With
End Sub
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 Sergey Alexandrovich Kryukov 130
4 DamithSL 105
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 7 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100