Searching for what is not there.

[darronb]

I am writing a VB macro in Excel 97, I have a column that should only hold 12 different strings

E.g. A through to L.

Can anyone advice me on a short peice of code that will check that there is nothing else in this column apart from what should be. (A to L)

Thanks.

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[darronb]

I have tried a Case statment, with the last statment coping the unrecognised code into an array.

This seems long winded. hoping there is a simpler way

N.B. I am no programmer so i have limited knowledge on this.

 

[xlbo]

this will depend on the situation - you can prevent users entering erroneous data by using a Data>Validation list

If the data is already there and you just want to check it, have a look at the advanced filter

Other than that, you could try doing a lookup (formula) to a table of valid entries and then use autofilter on the results of that to get your invalid entries...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kodr]

if activecell.value < chr$(x) or > chr$(y) then
....
end if

Something like this?

 

[faxof]

might be long winded but it works

assuming that column A contains your data...

enter the 12 strings in cells B1 to B12

ensure cell C1 is empty

run this:

Sub checkstring()
For x = 35536 To 1 Step -1
 Cells(1, 3).ClearContents
 For y = 1 To 12
  If Cells(x, 1).Value = Cells(y, 2).Value Then
   Cells(1, 3).Value = "x"
  End If
 Next y
 If Cells(1, 3).Value <> "x" Then Cells(x,1).ClearContents
Next x
Cells(1, 3).ClearContents
End Sub

 

[darronb]

Sorry to be a bit thick but could you please expland on the explanation of the code.

 

[xlbo]

Unless you only have a few hundred rows, it will be much quicker to code a formula lookup and use autofilter rather than looping through all rows...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[darronb]

Sorry guys, I still can't work out how to code the lookup and then do an autofilter in a macro.

I have the basic understanding of the VLOOKUP command and i can use it to search for a particular entry.

but how do i check for an invalid entry ???????????

I have 1 column of data to lookup, and a variable length column to check.

This is realy giving me a headache

 

[xlbo]

your lookup formula should be along the lines of

=IF(ISNA(vlookup(theValue,LookupRange,1,false)),"Invalid","")

Your autofilter should then filter on "Invalid"

et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question