compare string to column
 

I have a form that has a log number (i.e. E29029) that gets approved and and
the first character gets removed and the new number replaces the one in the
actual data log (i.e. E29029 becomes 29029). How would I go about verifying
that there is not a duplicate in the data log prior to changing the cell
(i.e. if EOTemp = cells (X,y) or EONo = cells(x,y) then msgbox"Duplication
error, press ok to open the log for editing"
else
cells(x,y) = EONo.

Thanks in advance

compare string to column
 

Dim res1, res2, res3
res1 = application.Match(EOTemp,Columns(2),0)
res2 = application.Match(cStr(EONo),Columns(2),0)
res3 = application.Match(clng(EONo),Columns(2),0)
if not iserror(res1) or not iserror(res2) or not iserror(res3) then
msgbox"Duplication error, press ok to open the log for editing"
Else
cells(rows.count,2).End(xlup)(2).Value = EONo
End if

--
Regards,
Tom Ogilvy


"asmenut" wrote in message
...
I have a form that has a log number (i.e. E29029) that gets approved and
and
the first character gets removed and the new number replaces the one in
the
actual data log (i.e. E29029 becomes 29029). How would I go about
verifying
that there is not a duplicate in the data log prior to changing the cell
(i.e. if EOTemp = cells (X,y) or EONo = cells(x,y) then msgbox"Duplication
error, press ok to open the log for editing"
else
cells(x,y) = EONo.

Thanks in advance

compare string to column
 

Thanks Tom. You da man

"Tom Ogilvy" wrote:

Dim res1, res2, res3
res1 = application.Match(EOTemp,Columns(2),0)
res2 = application.Match(cStr(EONo),Columns(2),0)
res3 = application.Match(clng(EONo),Columns(2),0)
if not iserror(res1) or not iserror(res2) or not iserror(res3) then
msgbox"Duplication error, press ok to open the log for editing"
Else
cells(rows.count,2).End(xlup)(2).Value = EONo
End if

--
Regards,
Tom Ogilvy


"asmenut" wrote in message
...
I have a form that has a log number (i.e. E29029) that gets approved and
and
the first character gets removed and the new number replaces the one in
the
actual data log (i.e. E29029 becomes 29029). How would I go about
verifying
that there is not a duplicate in the data log prior to changing the cell
(i.e. if EOTemp = cells (X,y) or EONo = cells(x,y) then msgbox"Duplication
error, press ok to open the log for editing"
else
cells(x,y) = EONo.

Thanks in advance