Cells.Find doesnt work on columns other than A
 

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..

--
Message posted from http://www.ExcelForum.com

Cells.Find doesnt work on columns other than A
 

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

"bagsakan " wrote in message
...
Hi,

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

anyway all i want to do is to make sure that certain columns do not
have duplicate entries, and if there are then i want to notify the user
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 columns
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...


---
Message posted from http://www.ExcelForum.com/

Cells.Find doesnt work on columns other than A
 

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

--
Regards,
Tom Ogilvy


"bagsakan " wrote in message
...
Hi,

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

anyway all i want to do is to make sure that certain columns do not
have duplicate entries, and if there are then i want to notify the user
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 columns
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...


---
Message posted from http://www.ExcelForum.com/

Cells.Find doesnt work on columns other than A
 

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. :rolleyes:

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..

--
Message posted from http://www.ExcelForum.com

Cells.Find doesnt work on columns other than A
 

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..

--
Message posted from http://www.ExcelForum.com

Cells.Find doesnt work on columns other than A
 

Change

Lookat:=xlPart

to

Lookat:=xlWhole

HTH,
Bernie
MS Excel MVP

"bagsakan " wrote in message
...
im back. i have tried using your suggestions and they work to a certain
degree but how do i enable seach for whole words only? the only problem
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 with
snapple.

thanks again...

regards...


---
Message posted from http://www.ExcelForum.com/

Cells.Find doesnt work on columns other than A
 

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..

--
Message posted from http://www.ExcelForum.com

Cells.Find doesnt work on columns other than A
 

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 " wrote in message
...

Formula:
--------------------

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 certain
degree. the only problem is that it does not see subsequent
duplicates when the Found value was set previously. To illusrate, given
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 it
because when they are conjoined by an 'And' operator then VB still
expands Found.Row when Found is already equal to nothing. This causes a
set error which is wrong because it shouldnt have evaluated the second
part if the first part is already false.

anyway thanks for all the help...

regards...


---
Message posted from http://www.ExcelForum.com/

Cells.Find doesnt work on columns other than A
 

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.

--
Message posted from http://www.ExcelForum.com

Cells.Find doesnt work on columns other than A
 

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

"bagsakan " wrote in message
...
thanks bernie but here in my actual workbook it does not provide an
error prompt for the '2'.

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

thank you..


---
Message posted from http://www.ExcelForum.com/