Excel VBA Check if current cell is a substring of cells in same column 1

[knifey]

Hi,
I'm still very new to this and would appreciate all the help and advice I can get!
I have a huge Excel VBA project to try to find every error a user could possibly make in a sheet with 12 or so columns.
I need a way to read a non-numeric cell and then compare it to every other row in the same column to see if it is a substring of any of them.
I came up with this (inside the main loop). But it's not working properly (syntax). Can anyone point me in the right direction?

If Not IsNumeric(Columns("M")Rows(i1).Value Then
For i2 = 3 to rowCount
If InStr(1, iPolRef(i2.Value), iPolRef) > 1 Then
GoSub substringcomment
End If
Exit For
End If

Thanks, Roy

 

[SkipVought]

Hi,

Take a look at the Find function.

dim r as range
set r = sheets("sheet1").cells.find(strFindVal)
if no r is nothing then
  'found it
else 
  'not
end if

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Bong]

Skip is right, of course. However, other than being very slow, your logic ought to work.

I don't know if you just typed it in wrong but

If Not IsNumeric(Columns("M")Rows(i1).Value Then

should be

If Not IsNumeric(Columns("M")[red].[/red]Rows(i1)[red])[/red].Value Then

for starters.

Then

If InStr(1, iPolRef(i2.Value), iPolRef) > 1 Then

has some problems, too.

iPolRef can't be both a scalar and a vector. That is, either iPolRef(i2.value) makes sense, or iPolRef does, but not both. If I understand what you're trying to do, it would be

testval = columns("m").rows(i1).value
if not isnumeric(testval) then
  For i2 = 3 to rowCount
    If InStr(1, columns("m").rows(i2).value, testval) > 1 Then
       GoSub substringcomment
    End If
  Exit For
end if

But Skip's method is way better.

_________________
Bob Rashkin