?
Solved

VBA Ins

Posted on 2006-11-09
9
Medium Priority
?
150 Views
Last Modified: 2010-04-30
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

0
Comment
Question by:JMO9966
[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
  • 3
  • 3
  • 3
9 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17906214
If InStr(1, "P|B", Range("Buy_Pick").Cells(current_row), vbtextcompare) = 0 Then
    result = result & "Invalid pick - buy value." & vbLf
End If
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17906957
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
 

Author Comment

by:JMO9966
ID: 17909918
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 17910091
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
 

Author Comment

by:JMO9966
ID: 17910200
Oh, thanks.  Should I stick with the Instr(1, ..........................., vbtextcompare) = 0 too?

JMO9966
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17910348
Yes. Your logic looks OK, but I can't guess about the Excel string.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 17929729
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
 

Author Comment

by:JMO9966
ID: 17929911
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
 
LVL 35

Expert Comment

by:mvidas
ID: 17930021
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month8 days, 18 hours left to enroll

764 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