VBA Ins

Hello, I'm trying to validate values in spreadsheet cells before they are imported in a database.

I'm using this function to make sure this field is either a "P" or "B".  I need this to NOT be case sensitive.  In other words I want to validate this value is either P,p,B, or b and this InStr function is looking for literal string...upper-case in this example and will error if it finds "p" or "b".


Here's the function:

If InStr("P|B", Range("Buy_Pick").Cells(current_row)) = 0 Then
    result = result & "Invalid pick - buy value." & vbLf
End If


Thank You!

JMO9966

JMO9966Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
If InStr(1, "P|B", Range("Buy_Pick").Cells(current_row), vbtextcompare) = 0 Then
    result = result & "Invalid pick - buy value." & vbLf
End If
0
mvidasCommented:
JMO,

As Graham notes, adding vbtextcompare should do it.  But the way you have it currently, it is looking for the cell's value IN "P|B"

Perhaps the easiest way to do what you're looking for is to use Data Validation on those cells in Buy_Pick.  Select that range, go to Data / Validation.  On the Settings tab, change "Allow" to 'List', and for the source put (without quotes) "P,p,B,b".  Uncheck Ignore Blank if desired (so it has to be one of those), and check In-cell Dropdown if you'd like a dropdown put on the sheet so the user knows that is their only choice.

If you wanted to verify with VBA, you could also use the Like keyword:
 If Range("Buy_Pick").Cells(current_row).Text Like "[PpBb]" Then

And if you wanted to make sure one of those letters is in the cell (not necessarily the only thing in the cell) change the like to "*[PpBb]*" (the * being the wildcard on either end)
Matt
0
JMO9966Author Commented:
Thanks guys, but the first approach, vbtextcompare does not look just at my current_row as I want, it's looking at the whole range.

I tried the second approach but that also didn't work.  Let's say the column needs to hold "in" for inches OR "cm" for centimeters  There could be eight possibilities:

in
In
iN
IN
cm
Cm
cM
CM

I tried the "[inINIniNcmCmcMCM]" but that didn't work.  just looking for a way to set my data check to not be case-sensitive.


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GrahamSkanRetiredCommented:
Instr works on two strings, so if :

Range("Buy_Pick").Cells(current_row).Text

returns the string that you want to check, then you should be OK.





0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JMO9966Author Commented:
Oh, thanks.  Should I stick with the Instr(1, ..........................., vbtextcompare) = 0 too?

JMO9966
0
GrahamSkanRetiredCommented:
Yes. Your logic looks OK, but I can't guess about the Excel string.
0
mvidasCommented:
JMO,

If your named range "Buy_Pick" contains only one cell, the following statement (taken from your original code above) should only return one cell's value:
 Range("Buy_Pick").Cells(current_row)

If it contains more than one column in the range, you'll have to specify the column after the row, like
 Range("Buy_Pick").Cells(current_row, 1)
 Range("Buy_Pick").Cells(current_row, 2)

Also, you'd be best using the .Text of these object, like:
 InStr(1, Range("Buy_Pick").Cells(current_row).Text, "P|B", vbTextCompare)

For what it's worth (perhaps a version limitation of what I'm using), I couldn't get the "P|B" syntax to work with the "|" as an OR delimiter.  The only way I could get that to return true is if the string actually contained "p|b", but by no means do I know how everything works.

Matt
0
JMO9966Author Commented:
Thanks guys,

Sorry mvidas, I didn't see your response today before I accepted answer, I would have split the points had I seen yours.

If Instr(1, "in|cm", Range(("Imp_Metric").cells(current_row).Text, vbTextCompare) = 0 Then

This seemed to work for all scenarios:

in
IN
In
iN
cm
CM
Cm
cM

Thanks!
0
mvidasCommented:
Not a problem here, the points don't matter.  Your pasted code seems to be missing a parenthesis (or has an extra one after Range), I'm just just a typo in pasting.  
Strange that it does work (though you're testing = 0 which means it does not contain the string).  When I run the following:

Sub ldkfj()
 Dim str1 As String, str2 As String, str3 As String
 str1 = "12 in" 'in
 str2 = "hello" 'neither
 str3 = "acme" 'cm
 Debug.Print InStr(1, "in|cm", str1, vbTextCompare)
 Debug.Print InStr(1, "in|cm", str2, vbTextCompare)
 Debug.Print InStr(1, "in|cm", str3, vbTextCompare)
End Sub

I get 3 zeros in my immediate window.
Matt
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.