[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Ins

Posted on 2006-11-09
9
Medium Priority
?
158 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
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…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

873 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