Cells.Find doesnt work on columns other than A

[bagsakan]

Hi,

im new at excel+vba scripting because the person maintaining ou
scripts just packed up and left.

anyway all i want to do is to make sure that certain columns do no
have duplicate entries, and if there are then i want to notify the use
of their locations.

basically here is what i got

For counter = 1 To LastRow(ThisWorkbook.ActiveSheet)
Dim found As Long
st1 = Range("A" & counter)
found = Cells.Find(What:=st1, _
After:=Range("A" & counter), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Dim message1 As String
If found >= 0 And found <> counter Then
message1 = "Found " & st1 & " in " & Str(found)
Else
message1 = st1 & " of row " & Str(counter) & " not found"
End If
Cells(counter, 3).Value = message1
Next

i am currently doing this on a test workbook. if i move the column
to be checked to another column (say column B) and update the 'After
parameter to reflect the change then the macro doesnt work anymore.

can somebody point out what i am doing wrong? thanks.

regards..

 

[Bernie Deitrick]

This will work on column B:

Sub TryNow()
Dim Counter As Integer
For Counter = 1 To Range("B65536").End(xlUp).Row
Dim Found As Range
Dim St1 As Variant

St1 = Range("B" & Counter).Value
Set Found = Range(Range("B" & Counter), _
Range("B65536").End(xlUp)).Find(What:=St1, _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
MatchCase:=False)
Dim message1 As String
If Not Found Is Nothing And Found.Row <> Counter Then
message1 = "Found " & St1 & " in " & Str(Found.Row)
Else
message1 = St1 & " of row " & Str(Counter) & " not found"
End If
Cells(Counter, 3).Value = message1
Next
End Sub

However, if you would describe what you really want to do, it may be easier
another way, like a formula.....

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Dim found As Range
Dim message1 As String
Dim fAddr as STring
set found = Cells.Find(What:=st1, _
After:=Range("IV65536"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not found is nothing then
fAddr = found.Address
do
message1 = message1 & "Found " & st1 & " in " _
& found.Address & vbCrLf
set found = cells.FindNext(found)
Loop while found.Address <> fAddr
Else
message1 = st1 & " of row " & Str(counter) & " not found"
End If
Cells(1, 3).Value = message1

 

[bagsakan]

sorry for the confusing post. i was pressed for time and did not had th
time to review it. i only realized now that my question and my cod
doesnt add up.

anyway, the following pseudo-logic was what i was planning to do:

lastrow = last row of column Z
for x=1 to lastrow do
curcell = value of cell Zx
duplicate=cell.find ( what:=curcell, after:=Zx,...).row

if duplicate <> 0 and duplicate <> x then
error out that a duplicate was found
end if
next

i need this to be a macro unless somebody can teach me how to do th
following in a formula:

- the whole column Z is locked for user editing
- whenever a new row is added (other columns than Z has bee
edited), column Z of new row will have the value of the previous row'
Z column+1
- optional, seek gaps in teh values column Z and give new rows tha
number, sorting them afterwards.

thank you very much for all replies. i will try your suggestions an
post the result afterwards.

regards..

 

[bagsakan]

im back. i have tried using your suggestions and they work to a certai
degree but how do i enable seach for whole words only? the only proble
with the solutions presented is that given the following rows

app
apple
snapple

the code will match app with apple as well as snapple, and apple wit
snapple.

thanks again...

regards..

 

[Bernie Deitrick]

Change

Lookat:=xlPart

to

Lookat:=xlWhole

HTH,
Bernie
MS Excel MVP

 

[bagsakan]

PHP code
-------------------

Function CheckDuplicates(column As String)

Dim lastrow As Long

Dim st As String

st = column & 2

lastrow = Range(column & "65536").End(xlUp).Row

For counter = 3 To lastrow
Dim Found As Range
Dim searchee As Variant

searchee = Range(column & counter).Value
Dim xx As Range
'Set xx =
Set Found = Range(Range(column & counter), _
Range(column & "65536").End(xlUp)).Find(What:=searchee, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)

Dim message1 As String
If Not Found Is Nothing Then
If Found.Row <> counter Then
message1 = "Duplicate primary key in column " & column & _
" with value " & searchee & " in rows " & _
counter & "," & Str(Found.Row)
MsgBox message1, vbCritical, "ERROR"
CheckDuplicates = True
End If
End If

Next

End Function

-------------------


above is the current code i currently have which works to a certai
degree. the only problem is that it does not see subsequen
duplicates when the Found value was set previously. To illusrate, give
the following rows

1
2
3
4
1
2

the code will show the '1' duplicates but not the '2' duplicates.

and one more quirk. notice the laddered if statements? i had to do i
because when they are conjoined by an 'And' operator then VB stil
expands Found.Row when Found is already equal to nothing. This causes
set error which is wrong because it shouldnt have evaluated the secon
part if the first part is already false.

anyway thanks for all the help...

regards..

 

[Bernie Deitrick]

bagsakan,

With the code that you posted and your example numbers, all duplicate values
were found.

I will send you a working workbook with the code if you reply to me
privately.

HTH,
Bernie
MS Excel MVP

 

[bagsakan]

thanks bernie but here in my actual workbook it does not provide a
error prompt for the '2'.

anyway, i cant see how i can contact you directly because i cannot se
a pm button on your profile. you can send it over at bagsakan a
softhome dot net.

thank you.

 

[Bernie Deitrick]

I don't have a profile - I read from and post to the news-swerver directly.
No web interfaces for most of us - just use Outlook Express, or other
newsreader of your choice.

The file was sent moments ago....

HTH,
Bernie
MS Excel MVP