Solved

VBA Ins

Posted on 2006-11-09
9
132 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now