Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA to check data in a column is similar

0

HI All,

Is it possible, using VBA, to check that all the names in a column, say from cell D15 down to the last entry in column D, is similar. The names from cell D15 down is the result of an advanced filter. The entries could be from one to possibly up to 30 names.

I want to ensure all the names are similar before producing charts and statistics for the person named. If not, then a msgbox with an appropriate message is displayed.

Been going round in circles, so as to speak, for a while now so any help would be greatly appreciated.

Andrew

Answer
Discuss

Answers

0

First off, "similar" names are not necessarily "same" names. To check for similarity, such as "Richard Smith" being recognised as "Richard Smithe", is possible, with a high degree of probability, using so-called regular expressions.

On the presumption that you actually mean to check if names are identical, there is no 100% sure way if both instances of each name was typed. Lots of hair has been pulled out over an accidental space added at the end. The thing to do is not to type a name twice at all. Instead, enter all the names in a list, load this list into data validation drop-downs or combo boxes, and let the user select the name from the drop-down. In this way the same name will always be the same name.

If this easy solution is not available to you the hard way would be to have code to do the checking. Please paste the code below in the code sheet of the worksheet on which you have the filtered list (look for Sheet1 (Sheet1) in VBE's Project Explorer and double-click on it to open the correct code sheet. It isn't necessaril.y Sheet1 and the name in parentheses is the name you gave to the tab.)

Option Explicit
Private Sub Worksheet_Calculate()
    ' 18 May 2018
    
    Dim Rng As Range
    Dim FirstName As Variant
    Dim Cell As Range
    Dim R As Long
    
    R = Cells(Rows.Count, "D").End(xlUp).Row
    Set Rng = Range(Cells(15, "D"), Cells(R, "D"))
    
    For Each Cell In Rng.SpecialCells(xlCellTypeVisible)
        If IsEmpty(FirstName) Then
            FirstName = Cell.Value
        Else
            If Cell.Value <> FirstName Then
                MsgBox "The filtered selection includes """ & FirstName & _
                       """" & vbCr & " and at least one other name.", _
                       vbInformation, "Incorrect filter result"
                Exit For
            End If
        End If
    Next Cell
End Sub

This code will run when you apply the filter provided you have a SUM or SUBTOTAL function on the sheet which changes when the filter does. If you don't have such a function (sounds like you do) please add one. The trick is that the filter triggers the calculation of that total or subtotal and the act of calculation triggers the code. The code will stay quiet if all filtered items have the same name.

Discuss

Discussion

Thanks for your reply,
You are correct, I did mean 'the same'.
When the user enters a first date and last date, and a name, or part of a name for example,  'Eleanor'. The filter extracts all Eleanors between the dates entered. If the user only enters 'ele' in the name box it filters out 'Eleanor' as well as 'Helen', if there was a 'Helen'.
This is fine for one aspect of the application, but when the user wishes to go further and perform analysis on a particular name, before this analysis is carried out I want an error message to be displayed if all the names in the filtered data is not the same, as this would give incorrect figures if both Eleanor and Helen is in the filtered data.
Currently the filtered names start in cell D15 and continue down until there are no more Eleanors - dependant on the first and last dates entered in the criteria for the filter.
I was hoping to get a simple solution but getting the syntax for the VBA correct is causing me problems hence my question.

Thanks once again
DownSouthDrew May 18, '18 at 6:05 am
I have added the code you are likely to require to my original answer. You should be able to change both the column ("D") and the starting row (15) in the code if needed in future. Please ask if you need instruction for such a change. There are three "D"s and one 15 in the procedure.
Variatus (rep: 4889) May 18, '18 at 7:45 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login