Mark
Here is a rewrite of your macro as I described before. This macro is
based on there being 3 sheets named One, Two, and Three. The objective is
to find all values that appear in all 3 sheets. Each sheet has a list
running from A1 down. The end product is a list in Column C of the One
sheet, starting with C2, of all the items that appear in all 3 sheets. HTH
Otto
Sub FindDups()
Dim rOne As Range
Dim rTwo As Range
Dim rThree As Range
Dim i As Range
Dim Dest As Range
Set Dest = Sheets("One").Range("C2")
Set rOne = Sheets("One").Range("A1", Sheets("One").Range("A" &
Rows.Count).End(xlUp))
Set rTwo = Sheets("Two").Range("A1", Sheets("Two").Range("A" &
Rows.Count).End(xlUp))
Set rThree = Sheets("Three").Range("A1", Sheets("Three").Range("A" &
Rows.Count).End(xlUp))
For Each i In rOne
If Not rTwo.Find(What:=i.Value, Lookat:=xlWhole) Is Nothing Then
If Not rThree.Find(What:=i.Value, Lookat:=xlWhole) Is
Nothing Then
Dest.Value = i.Value
Set Dest = Dest.Offset(1)
End If
End If
Next i
End Sub
"Mark" wrote in message
...
Hi, Thanks for the help. However, I'm getting a syntax error message at
the
line:
If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value
Then
Not sure why it doing that? Any ideas?
Thanks.
"ryguy7272" wrote:
This does a nice job of comparing two sheets:
Sub FindDupes() 'assuming both sheets are in same book and book is open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)
sht1.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht1 = ActiveCell.Row
sht2.Activate
sht2.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht2 = ActiveCell.Row
sht1.Activate
For rowSht1 = 1 To LastRowSht1
If sht1.Cells(rowSht1, 1) = "" Then Exit Sub
For rowSht2 = 1 To LastRowSht2
If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2,
1).Value
Then
sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3
sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3
End If
Next
Next
sht1.Cells(1, 1).Select
End Sub
Regards,
Ryan---
PS, same answer in your other post too...not sure which you will look
at...
--
RyGuy
"Mark" wrote:
Hi,
Thanks for the advice. This is my file if you want a look.
(http://www.freefilehosting.net/download/3ffj5) I've included my macro
code.
It start to work but then crashed.
Any help with a rewrite would be much appreciated.
Mark
"Otto Moehrbach" wrote:
Mark
I was looking at the thread you have in the Programming
newsgroup. It
looks like your code, after making the suggested corrections, is
pretty much
what you want. I would rewrite the code to eliminate all but one of
the
"For" loop statements and use the "Find" statement to do all the
work, but
what you have will work. Come back if you're interested in a rewrite
of
what you have. HTH Otto
"Mark" wrote in message
...
Not all the lists. For example, I want to compare list 1 with lists
2 and
3.
I want to see which of the genes in list 1 appear in lists 2 and 3,
etc.
I was looking at colour coding the genes that appear, but it could
be
easier
to put into a seperate sheet?
"Otto Moehrbach" wrote:
Do you want to find only those genes that appear in all 3 lists?
Not
just 2
lists? What/where do you want to place the list of genes that
appear in
all
3 lists? In 2 lists only? HTH Otto
"Mark" wrote in message
...
Hi,
I'm looking for some advice as to how to cross reference 3 lists
of
genes
that I am researching.
I have these lists in a single workbook, with each list in its
own
worksheet. The lists start in Cell A1 and vary in size from 337
genes
in
the
smaller list and upto 1489 genes in the largest.
Basically, each list refers to the genes of interest in a
particular
region.
I'm therefore looking for a way to cross reference these lists
to show
which
genes appear in all three regions.
Hope someone can help!
Thanks
Mark