Special Characters and Blank Cells

P

Pothead

New Member
Joined
May 11, 2016
Messages
16
I have 2 sheets, Sheet1 is for the Summary, Sheet2 is the Raw Data.
I need a code that will detect special characters from Sheet2 (only accepts Aa - Zz, 0 - 9, dot, underscore and dash), blank cells and mismatch cells. Please see "error description" for summary of error criteria. If special character is found it will highlight the cell in red and will give a Summary of error in Sheet1. You can download my spreadsheet here. Hoping for your positive response, thank you.

Sheet1(Summary)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]CHECK ITEMS[/TD]
[TD]OCCURENCE[/TD]
[TD]Error Description[/TD]
[/TR]
[TR]
[TD]1. Contains Special Characters[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1a. in description[/TD]
[TD="align: center"]0[/TD]
[TD]special characters found in description[/TD]
[/TR]
[TR]
[TD="align: right"]1b. in attachment[/TD]
[TD="align: center"]0[/TD]
[TD]special characters found in attachment[/TD]
[/TR]
[TR]
[TD]2. Wrong File Attachment[/TD]
[TD="align: center"]0[/TD]
[TD]if Receipt No. did not match with Attachment filename (ignore the extension)[/TD]
[/TR]
[TR]
[TD]3. Missing Attachment[/TD]
[TD="align: center"]0[/TD]
[TD]if Attachment Column is blank[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (Raw Data)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Receipt No.[/TD]
[TD]Description[/TD]
[TD]Attachment[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-001[/TD]
[TD]TRANSPORTATION ALLOWANCE[/TD]
[TD]ITEM-A-BCD-001.PDF[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-002[/TD]
[TD]FOOD ALLOWANCE%[/TD]
[TD]ITEM-A-BCD-002.XLS[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-003[/TD]
[TD]MISCELLANEOUS[/TD]
[TD]ITEM-A-BCD-003.DOC[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-004[/TD]
[TD]WORKSHOP AND WAREHOUSE[/TD]
[TD]ITEM-A-BCD-004.PDF[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-005[/TD]
[TD]TERMINAL FEE[/TD]
[TD]ITEM-A-BCD-005.JPG[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-006[/TD]
[TD]TRAVEL TAX[/TD]
[TD]ITEM-A-BCD-006.PDF[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Hello. I did it this way:

Code: 
Option Explicit

Type ItemType
  ReceiptNo As String
  Description As String
  Attachment As String
End Type

Sub CheckItems()
  Dim totalItems As Long
  Dim specCharsInDesc As Long
  Dim specCharsInAttach As Long
  Dim wrongAttach As Long
  Dim missingAttach As Long
  Dim itm As ItemType
  Dim r As Long
  
  totalItems = Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row - 1
  
  For r = 2 To totalItems + 1
    itm.ReceiptNo = Sheet2.Cells(r, 1)
    itm.Description = Sheet2.Cells(r, 2)
    itm.Attachment = Sheet2.Cells(r, 3)
    
    If SpecialChars(itm.Description) Then
      specCharsInDesc = specCharsInDesc + 1
    End If
    If SpecialChars(itm.Attachment) Then
      specCharsInAttach = specCharsInAttach + 1
    End If
    
    If (itm.Attachment = "") Then
      missingAttach = missingAttach + 1
    Else
      If (Split(itm.Attachment, ".")(0) <> itm.ReceiptNo) Then
        wrongAttach = wrongAttach + 1
      End If
    End If
  Next r
  
  Sheet1.Range("B2") = totalItems
  Sheet1.Range("B6") = specCharsInDesc
  Sheet1.Range("B7") = specCharsInAttach
  Sheet1.Range("B8") = wrongAttach
  Sheet1.Range("B9") = missingAttach
End Sub

Function SpecialChars(txt As String) As Boolean
  Dim allowedChars(0 To 6) As String
  Dim char As String
  Dim pos As Integer
  Dim itm
  
  allowedChars(0) = "#"     ' digit 0-9
  allowedChars(1) = "[A-Z]" ' ucase letter
  allowedChars(2) = "[a-z]" ' lcase letter
  allowedChars(3) = "."     ' decimal
  allowedChars(4) = "-"     ' hyphen
  allowedChars(5) = "_"     ' underscore
  allowedChars(6) = " "     ' space
  
  For pos = 1 To Len(txt)
    SpecialChars = True
    char = Mid(txt, pos, 1)
    
    For Each itm In allowedChars
      SpecialChars = SpecialChars And Not (char Like itm)
    Next itm
    
    If SpecialChars Then Exit Function
  Next pos
End Function
 
Upvote 0
Hello gpeacock, thanks for the reply but the code doesn't give me any output, sorry im totally noob with excel. Please advise,
 
Upvote 0
Pothead said:
I have 2 sheets, Sheet1 is for the Summary, Sheet2 is the Raw Data.
I need a code that will detect special characters from Sheet2 (only accepts Aa - Zz, 0 - 9, dot, underscore and dash), blank cells and mismatch cells. Please see "error description" for summary of error criteria. If special character is found it will highlight the cell in red and will give a Summary of error in Sheet1. You can download my spreadsheet here. Hoping for your positive response, thank you.

Sheet1(Summary)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]CHECK ITEMS[/TD]
[TD]OCCURENCE[/TD]
[TD]Error Description[/TD]
[/TR]
[TR]
[TD]1. Contains Special Characters[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1a. in description[/TD]
[TD="align: center"]0[/TD]
[TD]special characters found in description[/TD]
[/TR]
[TR]
[TD="align: right"]1b. in attachment[/TD]
[TD="align: center"]0[/TD]
[TD]special characters found in attachment[/TD]
[/TR]
[TR]
[TD]2. Wrong File Attachment[/TD]
[TD="align: center"]0[/TD]
[TD]if Receipt No. did not match with Attachment filename (ignore the extension)[/TD]
[/TR]
[TR]
[TD]3. Missing Attachment[/TD]
[TD="align: center"]0[/TD]
[TD]if Attachment Column is blank[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (Raw Data)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Receipt No.[/TD]
[TD]Description[/TD]
[TD]Attachment[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-001[/TD]
[TD]TRANSPORTATION ALLOWANCE[/TD]
[TD]ITEM-A-BCD-001.PDF[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-002[/TD]
[TD]FOOD ALLOWANCE%[/TD]
[TD]ITEM-A-BCD-002.XLS[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-003[/TD]
[TD]MISCELLANEOUS[/TD]
[TD]ITEM-A-BCD-003.DOC[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-004[/TD]
[TD]WORKSHOP AND WAREHOUSE[/TD]
[TD]ITEM-A-BCD-004.PDF[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-005[/TD]
[TD]TERMINAL FEE[/TD]
[TD]ITEM-A-BCD-005.JPG[/TD]
[/TR]
[TR]
[TD]ITEM-A-BCD-006[/TD]
[TD]TRAVEL TAX[/TD]
[TD]ITEM-A-BCD-006.PDF[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Hi PotHead,

Does this do what you need?

Code: 
Sub CheckCellValues()
' Define variables
Dim Cell As Range, cRange As Range
' Define LastRowF as the last row of data in column A
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
' Set range to check as A1 to the last row of C
Set cRange = Range("A1:C" & LastRow)
' For each cell in the check range
For Each Cell In cRange
    ' If the cell value is blank or contains one of the specified special characters then...
    If Cell.Value = "" Or Cell.Value Like "*[%?/!]*" Then
        ' Highlight the cell with a red fill
        Cell.Interior.ColorIndex = 3
    End If
' Check next cell in check range
Next Cell


End Sub
 
Upvote 0
Hey guys thanks for the reply, it really helps me a lot. I already manage it to work but I have one more problem below, Column A "Receipt No" should matched with Column C "Attachment" filename (ignore the file extension) but sometimes there are multiple attachments separated by a semi-colon as shown below, can you give me a code to detect if the two filenames matched with the its receipt no.?

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Receipt No.[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Attachment[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]ITEM-A-BCD-001[/TD]
[TD="align: center"]TRANSPORTATION ALLOWANCE[/TD]
[TD="align: center"]ITEM-A-BCD-001.PDF; ITEM-A-BCD-001.XLS[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Pothead said:
Hey guys thanks for the reply, it really helps me a lot. I already manage it to work but I have one more problem below, Column A "Receipt No" should matched with Column C "Attachment" filename (ignore the file extension) but sometimes there are multiple attachments separated by a semi-colon as shown below, can you give me a code to detect if the two filenames matched with the its receipt no.?

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Receipt No.[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Attachment[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]ITEM-A-BCD-001[/TD]
[TD="align: center"]TRANSPORTATION ALLOWANCE[/TD]
[TD="align: center"]ITEM-A-BCD-001.PDF; ITEM-A-BCD-001.XLS[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Hi again mate, glad to hear you got the last issue sorted.

With regards to your newer issue, I am sure there is probably a more elegant way of doing this, but try the following:

=IFERROR(IF(FIND(";",C2),IF(LEFT(C2,LEN(A2))=A2,IF(MID(C2,LEN(A2)+7,LEN(A2))=A2,"YES","NO"))),IF(LEFT(C2,LEN(A2))=A2,"YES","NO"))

Basically it first looks for a ";" in cell C2. If one is found then it compares the first half (without file extension) to cell A2 and if that matches then it checks the second half (without file extension) to cell A2. If they both match then you get a yes response. If one of them doesn't match you get a no response.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Averageif of cells from three sheets
Replies
7
Views
345
Fluff
Fluff
H
  • Solved
Delete the last character of cells in column "K" if it is equal to "X" or "x"
Replies
8
Views
185
Fuji
F
L
  • Question Question
How to fix VBA code to stop saving and emailing PDF of blank cell from data validation list?
Replies
2
Views
308
lharr28
L
Sufiyan97
  • Question Question
Add Auto sum in selected blank cells VBA
Replies
4
Views
371
Sufiyan97
Sufiyan97
L
  • Solved
auto repeating the dates in specific cells in column by writing in two input box
Replies
3
Views
262
mumps
mumps

Forum statistics

Threads
1,225,156
Messages
6,183,246
Members
453,152
Latest member
ChrisMd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top