# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  How to check if range contains certain value

## Juhanen II

Hi all,

Is there a way to test if let's say range a1:a10 contains a value of cell b1.
I would be thankful to get the VBA code to do that

Thanks,

-Juhanen-

----------


## ravishankar

Hi
c1= if(iserror(match(B1,A1:A10,0),"No","yes")
Ravi

----------


## Juhanen II

Thanks Ravi, you already helped me. btw. your formula was missing only one ")". Below is the correct version. 




```
Please Login or Register  to view this content.
```


=IF(ISERROR(MATCH(D4,range_2,0)),"No","yes")


```
Please Login or Register  to view this content.
```


However, I would like to have aswell a VBA code to do the same trick. because i need to make a function to do the same test many times.

-Juhanen-

----------


## VBA Noob

Maybe




```
Please Login or Register  to view this content.
```


Use a formula like




> =FindVal(B1,A1:A10)



VBA Noob

----------


## Juhanen II

Thanks,

That's what i was looking for

-Juhanen-

----------


## carddards

I'm not sure if this is helpful anymore but here goes:



```
Please Login or Register  to view this content.
```

----------


## Juhanen II

Hi, 

I faced a new problem:

So now I have made this VBA function (which works well):




```
Please Login or Register  to view this content.
```


When I type this to let's say cell F15, it works well:



```
Please Login or Register  to view this content.
```


But when I use this VBA code, it doesn't work corretly anymore:



```
Please Login or Register  to view this content.
```


So is there something wrong with the last code?

Thanks

-Juhanen-

----------


## royUK

> I'm not sure if this is helpful anymore but here goes:
> 
> Sub search()
> 
> lastrow = Range("A65536").End(xlUp).Row
> 
> For i = 2 To lastrow
> 
>     If Cells(i, 1) = Cells(1, 2) Then
> ...



carddards, please read the Forum Rules & use Code Tags in future.

----------


## Richard Schollar

When you say "it doesn't work correctly anymore" what does it do?  Does it go to debug, or return an incorrect value or what?

----------


## Juhanen II

Hi,

Sorry I finally found mistake by myself.
Below is the correct code, replaced "d4" by (Range("d4").Value




```
Please Login or Register  to view this content.
```


Thanks to all the helpers

-Juhanen-

----------

