If statement with wildcard

[lbsstacey]

I am trying to write code where an If statement woud look at a cell and
determine if the cell contains certain data elements and, if so, then the If
statement will perform a function. This, however, is not working for me:

If ActiveCell.Value = "* STREET" Then

I'm positive that it's because the code is looking for an exact match on "*
STREET" and does not recognize the * variable.

Any suggestions would be appreciated.

 

[Jeff Boyce]

I don't believe Access has "ActiveCell" ... isn't that an Excel property?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[lbsstacey]

Yes, this is an Access module which refers to an Excel spreadsheet.

 

[pietlinden]

I am trying to write code where an If statement woud look at a cell and
determine if the cell contains certain data elements and, if so, then theIf
statement will perform a function. This, however, is not working for me:

If ActiveCell.Value = "* STREET" Then

I'm positive that it's because the code is looking for an exact match on "*
STREET" and does not recognize the * variable.

Any suggestions would be appreciated.

Sounds like you want INSTR(). Then you can search for a string inside
of something and have your code respond accordingly.

 

[lbsstacey]

Can you show me how that would like in my IF statement example.

 

[pietlinden]

Can you show me how that would like in my IF statement example.

InStr() returns a number, the position of one string inside another.
If it's >0 then it's in there.

straight from the help file...

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".

' A textual comparison starting at position 4. Returns 6.
MyPos = Instr(4, SearchString, SearchChar, 1)

' A binary comparison starting at position 1. Returns 9.
MyPos = Instr(1, SearchString, SearchChar, 0)

' Comparison is binary by default (last argument is omitted).
MyPos = Instr(SearchString, SearchChar) ' Returns 9.

MyPos = Instr(1, SearchString, "W") ' Returns 0.

so you'd just have something like

IIf(Instr(1, SearchString, SearchChar,0)>0, "True Part", "False Part")