Validate User Input Data - Confirm against known list

[Askjerry]

I have a range of cells 10 columns wide by 16 rows tall... it is assigned the range name of: "USER_CHANNELS"
The cells in this range are validated against a range called: "VAL_CHAN_NAME"

If the user is typing in data... and they get something wrong... it stops them and forces them to select/type the correct information. (Typical validated cells.)

The problem lies when they use PASTE SPECIAL to drop in a bunch of data... for example...

Good data...
7CALL50
7CALL50D
7TAC 51
7TAC 51D
7TAC 52

Erroneous Data
7CALL50
7CALL50D
MONGO <--- This is NOT in the validation list.
7TAC 51D
7TAC 52

Since they did a PASTE SPECIAL... the spreadsheet is happy to accept the data... until I run my other routines... then obviously my VLOOKUP can't find matching data and crashes.

What I need, is a way to compare "USER_CHANNELS" against "VAL_CHAN_NAME" and highlight any that are wrong.
(Make them BOLD, color them red... whatever... alternately... it could change the data to "ERROR" and I could have conditional formatting do the rest.)

Is there a way to go through the range "USER_CHANNELS" and mark/change anything not found in "VAL_CHAN_NAME"?

Some function... or a routine that I can assign a button "VALIDATE" to??

Suggestions? Thoughts?

Thanks,
Jerry

 

[Gerald Higgins]

Several possible solutions spring to mind.

When users use PASTE SPECIAL, what are they actually pasting - values ?
If YES, then you can possibly use Conditional Formating to highlight the cell, if an entry appears that is not in the approved list.

Here's a simple example, use this formula within CF to determine if the value entered in A1 is found in the approved list in C1:C3
=ISERROR(VLOOKUP(A1,C1:C3,1,FALSE))

You should be able to adapt this to your situation.

 

[Askjerry]

They would be pasting supposedly valid channel names...
7CALL50
7CALL50D
7TAC 51
7TAC 51D
7TAC 52

But sometimes what they paste is incorrect... a valid name might be "7CALL50" whereas they had "7 CALL 50" on the sheet...
7 CALL 50
7CALL50D
7TAC 51
7TAC 51D
7TAC 52

As you can imagine... an extra space here or there... very easy to miss.

I tried this... as a conditional formatting formula ... fails miserably.
=ISERROR(VLOOKUP(C5,Worksheets("SOURCE").Range("VAL_CHAN_NAME"),1,FALSE))

I thought I could make a routine... verify the data... if good, fine, if not, paste in something else.
I set up a button on the USER page to call the subroutine...

This works with good data or blanks... but if data is bad (not in the list)... it fails with ERROR 400

Sub validate_user_input()
Dim user_data, user_cell, lookup_data As Range
Dim A As String
Set user_data = Worksheets("USER").Range("USER_CHANNELS")
Set lookup_data = Worksheets("SOURCE").Range("VAL_CHAN_NAME")
For Each user_cell In user_data
    If user_cell.Value = "" Then
        ' Do nothing if blank... leave it alone.
    Else
        user_cell = Application.WorksheetFunction.VLookup(user_cell, Worksheets("SOURCE").Range("VAL_CHAN_NAME"), 1, False)
    End If
Next
End Sub

I know it has something to do with handling the error generated when a match is NOT found.
Thanks,
Jerry

 

[Gerald Higgins]

I tried this... as a conditional formatting formula ... fails miserably.
=ISERROR(VLOOKUP(C5,Worksheets("SOURCE").Range("VAL_CHAN_NAME"),1,FALSE))

Are you doing this bit by basic worksheet Conditional Formating, or through VBA ?

 

[Askjerry]

It was part of conditional formatting... but didn't take.

I came up with a kludge solution... probably not the greatest... but functional.
I created three cells to be used as a scratchpad... (J25), (J26), and (J27).
The first cell (J25) gets handed the data by the routine.
The next cell (J26) Looks up the data in (J25) using VLOOKUP.
The final cell (J27) determines if (J26) has #N/A or not... if it does, it becomes whatever the original data was with "< >" added to it.

So if the cell contains "7CALL50" for example... then the output is "7CALL50".(Good data)If the cell contains "MONGO" (bad data), it outputs "< MONGO ><mongo></mongo>" and the conditional formatting finds the "<" character and turns it RED.

(J25) = Routine posted data
(J26) =VLOOKUP(I25,VAL_CHAN_NAME,1,FALSE)
(J27) =IFNA(J25,"<" & I25 & ">")

Here is the routine...

Sub validate_user_input()
Dim user_data, user_cell, lookup_data As Range
Dim A As String
Set user_data = Worksheets("USER").Range("USER_CHANNELS")
Set lookup_data = Worksheets("SOURCE").Range("VAL_CHAN_NAME")
For Each user_cell In user_data
    If user_cell.Value = "" Then
        ' Do nothing if blank... leave it alone.
    Else
        Range("I25").Value = user_cell.Value
        user_cell.Value = Range("K25").Value
    End If
Next
Range("I25").Value = ""
End Sub

I know... it's a Rube Goldberg... but for now it works.

Thanks,
Jerry