Comparing a vba variable to a range of cells
 

Howdy!

I'm having some difficulty figuring out how to do the following, or
even if it can be done.

I've got a variable value that's a text string that is determined by
user entry on a form. I need to compare that variable to a range of
cells and if it appears in ANY cell in that range return a message
box. The range of cells is assigned a name through the define name
function.

The primary problem I've got is this needs to be nested in an IF
statement.

This may look a little silly becuase its not a valid statement, but in
essence what I need is this:

IF NewCupSN1.value = Names("AllCups").ANY.value Then
blah blah blah........

Any guidance would be much appreciated.

Comparing a vba variable to a range of cells
 

Try

IF Application.Countif(Activesheet.Range("AllCups"), NewCupSN1.Value) Then
blah blah blah........


--
__________________________________
HTH

Bob

"ed" wrote in message
...
Howdy!

I'm having some difficulty figuring out how to do the following, or
even if it can be done.

I've got a variable value that's a text string that is determined by
user entry on a form. I need to compare that variable to a range of
cells and if it appears in ANY cell in that range return a message
box. The range of cells is assigned a name through the define name
function.

The primary problem I've got is this needs to be nested in an IF
statement.

This may look a little silly becuase its not a valid statement, but in
essence what I need is this:

IF NewCupSN1.value = Names("AllCups").ANY.value Then
blah blah blah........

Any guidance would be much appreciated.

Comparing a vba variable to a range of cells
 

dim cell as range
for each cell in range("rangename")
if cell.value = usertext then
'' do whatever eg
cell.select
msgbox "found one ! ",,"yee ha!"
end if
next

"ed" wrote:

Howdy!

I'm having some difficulty figuring out how to do the following, or
even if it can be done.

I've got a variable value that's a text string that is determined by
user entry on a form. I need to compare that variable to a range of
cells and if it appears in ANY cell in that range return a message
box. The range of cells is assigned a name through the define name
function.

The primary problem I've got is this needs to be nested in an IF
statement.

This may look a little silly becuase its not a valid statement, but in
essence what I need is this:

IF NewCupSN1.value = Names("AllCups").ANY.value Then
blah blah blah........

Any guidance would be much appreciated.

Comparing a vba variable to a range of cells
 

Hi,

Try this.

Sub Sonic()
Dim C As Range
For Each C In Range("AllCups")
If C.Value = NewCupSN1.value Then

'Do things here
End If
Next
End Sub

Mike

"ed" wrote:

Howdy!

I'm having some difficulty figuring out how to do the following, or
even if it can be done.

I've got a variable value that's a text string that is determined by
user entry on a form. I need to compare that variable to a range of
cells and if it appears in ANY cell in that range return a message
box. The range of cells is assigned a name through the define name
function.

The primary problem I've got is this needs to be nested in an IF
statement.

This may look a little silly becuase its not a valid statement, but in
essence what I need is this:

IF NewCupSN1.value = Names("AllCups").ANY.value Then
blah blah blah........

Any guidance would be much appreciated.

Comparing a vba variable to a range of cells
 

On Sep 15, 9:42*am, "Bob Phillips" wrote:
Try

IF Application.Countif(Activesheet.Range("AllCups"), NewCupSN1.Value) Then
blah blah blah........

--
__________________________________
HTH

Bob

"ed" wrote in message

...



Howdy!

I'm having some difficulty figuring out how to do the following, or
even if it can be done.

I've got a variable value that's a text string that is determined by
user entry on a form. *I need to compare that variable to a range of
cells and if it appears in ANY cell in that range return a message
box. *The range of cells is assigned a name through the define name
function.

The primary problem I've got is this needs to be nested in an IF
statement.

This may look a little silly becuase its not a valid statement, but in
essence what I need is this:

IF NewCupSN1.value = Names("AllCups").ANY.value Then
blah blah blah........

Any guidance would be much appreciated.- Hide quoted text -

- Show quoted text -



Thanks much! This worked like a charm. And thanks to the others for
replying as well. Cheers!

Comparing a vba variable to a range of cells
 

This should work...

If Not IsError(Range("AnyCups").Find(NewCupSN1.Value, _
LookAt:=xlPart, MatchCase:=False)) Then

where the xlPart tells the Find function to find the text within any single
cell within the AnyCups range even if the text is embedded within a larger
text string in that cell... use xlWhole if you want the match to be for a
cell's entire content.

--
Rick (MVP - Excel)


"ed" wrote in message
...
Howdy!

I'm having some difficulty figuring out how to do the following, or
even if it can be done.

I've got a variable value that's a text string that is determined by
user entry on a form. I need to compare that variable to a range of
cells and if it appears in ANY cell in that range return a message
box. The range of cells is assigned a name through the define name
function.

The primary problem I've got is this needs to be nested in an IF
statement.

This may look a little silly becuase its not a valid statement, but in
essence what I need is this:

IF NewCupSN1.value = Names("AllCups").ANY.value Then
blah blah blah........

Any guidance would be much appreciated.