how search EntireColumn to find value?

[Ian Elliott]

Thanks in advance.

I am having problems getting the value of a cell when
using EntireColumn.

I want to make a function that searches an entire column
for a search string (the function will return the row
number). I was doing this with vlookup, but having
problems if the string wasn't there and was getting error
messages (so I am trying to write my own)

So I have tried:
....
Dim cell As Range
Set workrange = Columns(1).EntireColumn
For Each cell In workrange
If cell.Value = "hello" Then 'will do search stuf later
cell.Value = "world"
End If
Next cell

but I get a type mismatch error on
if cell.value="hello" then
(I know this doesn't do a search thing but I just wanted
to see if I could use EntireColumn this way).

if I change workrange in the for line to range("b1:b24")
for example, it works fine.

I wanted to use something like UsedRange, but I don't want
to search the entire range, just the column (I plan on
searching column by column, going from left to right).

Any ideas?

Thanks again.

 

[Dave Peterson]

How about changing one line:

For Each cell In workRange.Cells

Excel was seeing your Cell variable as a whole column.