Match data in separate columns!
 

Hi can someone please give me a little advice on the following,
hopefully if is a simple problem. I have a range of data but am only
interested in rows where the cells in two specific columns are equal.
Therefore I want a macro which will looks through the data and
highlights any rows which have the same value in columns 'B' and 'H',
then copies and pastes this data in another worksheet.

Any help would be very much appreciated,
Thanks kindly,
Andrew


--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230
View this thread: http://www.excelforum.com/showthread...hreadid=479065

Match data in separate columns!
 

Private Sub CommandButton1_Click()
Dim j As Integer, row As Integer
row = 1
For j = 1 To 15
If Cells(j, 2).Formula = Cells(j, 8).Formula Then
Cells(j, 2).Interior.Color = vbCyan
Cells(j, 8).Interior.Color = vbCyan
Worksheets("Sheet2").Cells(row, 1) = Cells(j, 1).Formula
' repeat this for all required columns
row = row + 1
End If
Next j
End Sub

Hope this helps.

Regards
Ken


"Shandy720" wrote:


Hi can someone please give me a little advice on the following,
hopefully if is a simple problem. I have a range of data but am only
interested in rows where the cells in two specific columns are equal.
Therefore I want a macro which will looks through the data and
highlights any rows which have the same value in columns 'B' and 'H',
then copies and pastes this data in another worksheet.

Any help would be very much appreciated,
Thanks kindly,
Andrew


--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230
View this thread: http://www.excelforum.com/showthread...hreadid=479065

Match data in separate columns!
 

For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
If Cells(I,"B").Value = Cells(i,"H").Value Then
If rng Is Nothing Then
set rng = Rows(i)
Else
Set rng = Union(rng,Rows(i))
End If
End iF
Next i

If Not rng Is Nothing Then
rng.copy Worksheets("Sheet2").Range("A1")
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shandy720" wrote
in message ...

Hi can someone please give me a little advice on the following,
hopefully if is a simple problem. I have a range of data but am only
interested in rows where the cells in two specific columns are equal.
Therefore I want a macro which will looks through the data and
highlights any rows which have the same value in columns 'B' and 'H',
then copies and pastes this data in another worksheet.

Any help would be very much appreciated,
Thanks kindly,
Andrew


--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
View this thread: http://www.excelforum.com/showthread...hreadid=479065

Match data in separate columns!
 

Option Explicit

Sub FindMatches()
Dim Rng As Range
Dim oCell As Range
Dim lRow As Long


Set Rng = Sheets(1).Range("B2:B10") 'Adjust as needed

For Each oCell In Rng
If oCell < "" And oCell.Value = oCell.Offset(0, 6).Value Then
lRow = Sheets(2).Range("b65536").End(xlUp).Row + 1
oCell.EntireRow.Copy _
Destination:=Sheets(2).Cells(lRow, 1)
End If
Next oCell

End Sub

"Shandy720" wrote:


Hi can someone please give me a little advice on the following,
hopefully if is a simple problem. I have a range of data but am only
interested in rows where the cells in two specific columns are equal.
Therefore I want a macro which will looks through the data and
highlights any rows which have the same value in columns 'B' and 'H',
then copies and pastes this data in another worksheet.

Any help would be very much appreciated,
Thanks kindly,
Andrew


--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...o&userid=26230
View this thread: http://www.excelforum.com/showthread...hreadid=479065

Match data in separate columns!
 

Hi thanks for your help Bob but i get the following message when i tr
to run your macro;
'Run time error 424
Object required',

and the follwong line of your code is highlighted
'If Not rng Is Nothing Then'


Bob Phillips Wrote:
For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
If Cells(I,"B").Value = Cells(i,"H").Value Then
If rng Is Nothing Then
set rng = Rows(i)
Else
Set rng = Union(rng,Rows(i))
End If
End iF
Next i

If Not rng Is Nothing Then
rng.copy Worksheets("Sheet2").Range("A1")
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shandy720"
wrote
in messag
...

Hi can someone please give me a little advice on the following,
hopefully if is a simple problem. I have a range of data but a
only
interested in rows where the cells in two specific columns ar
equal.
Therefore I want a macro which will looks through the data and
highlights any rows which have the same value in columns 'B' an
'H',
then copies and pastes this data in another worksheet.

Any help would be very much appreciated,
Thanks kindly,
Andrew


--
Shandy720

------------------------------------------------------------------------
Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
View this thread
http://www.excelforum.com/showthread...hreadid=479065


--
Shandy72
-----------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...fo&userid=2623
View this thread: http://www.excelforum.com/showthread.php?threadid=47906

Match data in separate columns!
 

Have you declared the variable 'rng'? If not, you must.

Dim Rng As Range

If you put 'Option Explicit' at the top of your module, you will
be required to declare all your variables, and you'll not have
problems like this one.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Shandy720"
wrote in
message
...

Hi thanks for your help Bob but i get the following message
when i try
to run your macro;
'Run time error 424
Object required',

and the follwong line of your code is highlighted
'If Not rng Is Nothing Then'


Bob Phillips Wrote:
For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
If Cells(I,"B").Value = Cells(i,"H").Value Then
If rng Is Nothing Then
set rng = Rows(i)
Else
Set rng = Union(rng,Rows(i))
End If
End iF
Next i

If Not rng Is Nothing Then
rng.copy Worksheets("Sheet2").Range("A1")
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shandy720"

wrote
in message
...

Hi can someone please give me a little advice on the
following,
hopefully if is a simple problem. I have a range of data
but am
only
interested in rows where the cells in two specific columns
are
equal.
Therefore I want a macro which will looks through the data
and
highlights any rows which have the same value in columns 'B'
and
'H',
then copies and pastes this data in another worksheet.

Any help would be very much appreciated,
Thanks kindly,
Andrew


--
Shandy720

------------------------------------------------------------------------
Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
View this thread:
http://www.excelforum.com/showthread...hreadid=479065



--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
View this thread:
http://www.excelforum.com/showthread...hreadid=479065

Match data in separate columns!
 

Thanks fot that Cush, it works fine, really useful info.

:

--
Shandy72
-----------------------------------------------------------------------
Shandy720's Profile: http://www.excelforum.com/member.php...fo&userid=2623
View this thread: http://www.excelforum.com/showthread.php?threadid=47906

Match data in separate columns!
 

Whilst I am not disagreeing with Chip's advice, it is the better way to go,
you can force the rng variable to be classed as Variant/Object/Range by
adding this statement at the start of the code

Set rng = Nothing


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shandy720" wrote
in message ...

Hi thanks for your help Bob but i get the following message when i try
to run your macro;
'Run time error 424
Object required',

and the follwong line of your code is highlighted
'If Not rng Is Nothing Then'


Bob Phillips Wrote:
For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
If Cells(I,"B").Value = Cells(i,"H").Value Then
If rng Is Nothing Then
set rng = Rows(i)
Else
Set rng = Union(rng,Rows(i))
End If
End iF
Next i

If Not rng Is Nothing Then
rng.copy Worksheets("Sheet2").Range("A1")
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shandy720"
wrote
in message
...

Hi can someone please give me a little advice on the following,
hopefully if is a simple problem. I have a range of data but am
only
interested in rows where the cells in two specific columns are
equal.
Therefore I want a macro which will looks through the data and
highlights any rows which have the same value in columns 'B' and
'H',
then copies and pastes this data in another worksheet.

Any help would be very much appreciated,
Thanks kindly,
Andrew


--
Shandy720

------------------------------------------------------------------------
Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
View this thread:
http://www.excelforum.com/showthread...hreadid=479065



--
Shandy720
------------------------------------------------------------------------
Shandy720's Profile:
http://www.excelforum.com/member.php...o&userid=26230
View this thread: http://www.excelforum.com/showthread...hreadid=479065