Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I match data from two separate files in excel | Excel Discussion (Misc queries) | |||
Using Index & Match functions to find data on separate worksheet. | Excel Worksheet Functions | |||
Separate data into two columns | Excel Discussion (Misc queries) | |||
Can I use Excel to match text data from 2 separate columns ? | Excel Worksheet Functions | |||
Match data of two identical strings in two separate spreadhseets | Excel Discussion (Misc queries) |