Solved

VBA - Identify duplicates and Blanks

Posted on 2014-04-24
10
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 70

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 70

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 70

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Part One of the two-part Q&A series with MalwareTech.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

615 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