Solved

VBA Ins

Posted on 2006-11-09
9
136 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 76

Accepted Solution

by:
GrahamSkan earned 125 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

832 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