Solved

VBA - Identify duplicates and Blanks

Posted on 2014-04-24
10
322 Views
Last Modified: 2014-04-25
Hello,

I would like to create a VBA script in Excel (2013).

I would like the script to work down a worksheet (called 'PM') from row 4 (in column A) downwards until the end of the list and look for two conditions:

The cell in column C of the same row is blank or;
The value in column C is not duplicated elsewhere in column C at all on this worksheet.

If the condition(s) are met above I would like the row number on which the condition was picked up and quick outline of which issue was identified above to be put on another worksheet called errors!

I know  a little about VBA scripts - could someone start me off in the right direction as I have not picked one up in a little while.

thanks,

GISVPN
0
Comment
Question by:gisvpn
  • 4
  • 4
  • 2
10 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40021463
Although a VBA solution is viable, I usually avoid them if possible because they are more prone to errors than Excel formula solutions such as the one below.  The main reason to use VBA is that the formulas would be too complex or that you want to repeat a process that is difficult to duplicate in a formula (like copy/paste)

So to create a formula to solve your problem, go to the errors worksheet and put this in cell A1:

=MATCH(PM!C1,INDIRECT("PM!A"&(ROW()+1)&":A10000"),0)+ROW()

Then put this in B1:
=IF(ISNA(A1),IF(PM!C1="","blank",""),A1)

If you copy these down to match the number of entries you have in your PM worksheet, then you should see the word blank wherever there was a blank in column C and a number wherever any number was found below it in the list.  You may want to hide column A in the errors tab since it's a "work" column.

If this is not sufficient, I can write something in VBA for you.
0
 

Author Comment

by:gisvpn
ID: 40021467
Hi rspahitz,

Thank you for posting! I unfortunately have to use some VBA on this project ;) but thank you for the suggestion above!

GISVPN
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 200 total points
ID: 40022246
Something similar to
Dim err as Range
set err = [Errors!A1]
For Each c in Range([A4], [A4].End(xlDown))
  If c.Offset(,2).Value = "" Then
    err.Offset(,0) = c.Row
    err.Offset(,1) = "empty"
    set err = err.Offset(1,)
  ElseIf [C:C].Find(c, c, xlValues, xlWhole, xlByRows, xlNext).Address = c.Address Then
    err.Offset(,0) = c.Row
    err.Offset(,1) = "unique"
    set err = err.Offset(1,)
  End If
Next

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:gisvpn
ID: 40022711
Hi Qlemo,

Could we simplify the question at all, this will make it easier for me to get back into things?

I would just like a loop which would run down the A column (as long as there are values in it) and where the C column (on the same row) has a blank cell it will put a message on the Errors Worksheet (which includes the row number the error was picked up on and a short bit of text (something like Blank Cell in C identified)

;)

GISVPN
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40022811
If you remove lines 8 to 11, you will have exactly that simplification.
The For Each will go thru all A's, starting in row 4, look for the value in C, and if empty writes to the Errors sheet, with the row number in A, and text in B.
0
 

Author Comment

by:gisvpn
ID: 40022844
ok thank you - can I ask specifically what this is defining:

set err = [Errors!A1]

and will this move on the row one more?

set err = err.Offset(1,)
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40022978
[Sheet!Cell] is a complete cell address, so [Errors!A1] is the first cell of the Errors sheet.

We need a SET here because we handle the object (which is a cell), and not the value of it. err =[Errors!A1] is the same as err = [Errors!A1].Value, so the variable would contain what is written in that cell (probably nothing).

The first SET says "store the object in this var", and the second "replace the var with the object one row down". Yes, that is just moving the range (which is a cell here) down one row, and next run will write into Errors!A2.
0
 

Author Comment

by:gisvpn
ID: 40023020
ok thanks that has helped a lot. How do I set the worksheet where this part looks at the A4 downwards ?

For Each c in Range([A4], [A4].End(xlDown))

Can I do something like sheets("name").select ?
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40023031
Set the sheet active, selecting will not do. Or includ the sheet name in the square  brackets, like witb the Errors sheet.
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 300 total points
ID: 40023207
The previous code is good, but I prefer to use more pure VB code with this (rather than the Excel functions of ".End")

This should work too:

Sub FindErrors()
    Dim PMRow As Integer
    Dim ErrorRow As Integer
    Dim ACellValue As String
    Dim CCellValue As String
    
    PMRow = 4
    ErrorRow = 1
    Sheets("Errors").Cells.ClearContents
    Do
        ACellValue = Sheets("PM").Cells(PMRow, 1).Value
        If ACellValue = "" Then
            Exit Do
        End If
        
        CCellValue = Sheets("PM").Cells(PMRow, 3).Value
        If CCellValue = "" Then
            Sheets("Errors").Cells(ErrorRow, 1) = PMRow
            Sheets("Errors").Cells(ErrorRow, 2) = "blank"
            ErrorRow = ErrorRow + 1
        ElseIf CheckDuplicate(CCellValue) Then
            Sheets("Errors").Cells(ErrorRow, 1) = PMRow
            Sheets("Errors").Cells(ErrorRow, 2) = "duplicate (" & CCellValue & ")"
            ErrorRow = ErrorRow + 1
        End If
        
        PMRow = PMRow + 1
    Loop
End Sub

Private Function CheckDuplicate(ValuetoCheck As String) As Boolean
    Dim ValueCount As Integer
    Dim PMRow As Integer
    Dim ACellValue As String
    Dim CCellValue As String
    Dim DuplicateFound As Boolean
    
    DuplicateFound = False
    PMRow = 4
    Do
        ACellValue = Sheets("PM").Cells(PMRow, 1).Value
        If ACellValue = "" Then
            Exit Do
        End If
        
        CCellValue = Sheets("PM").Cells(PMRow, 3).Value
        If CCellValue = ValuetoCheck Then
            ValueCount = ValueCount + 1
            If ValueCount > 1 Then
                DuplicateFound = True
            End If
        End If
        
        PMRow = PMRow + 1
    Loop
    
    CheckDuplicate = DuplicateFound
End Function

Open in new window


run the macro, FindErrors, and it will put the errors in the errors tab
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is about my first experience with programming Arduino.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question