Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
Hi all , and a good day
I have 2 ranges , with these values : myRange1 = (ROW) A B C 1 1 2 4 2 1 3 4 3 1 3 5 myRange2 = (ROW) F G H 1 1 3 4 2 2 3 5 3 1 4 5 I need a macro to compare every cell in myRange2 - first row !! , with every cell in myRange1 - first row !! , and , if tha value of every cell in myRange2 is equal with one of the cells of the first row of myRange1 , to change the value with "QQQ" . This must to be done in every row . After checking first row of myRange2 with first row of myRange1 , to go to next row in myRange2 , and compare with the values of the next row of myRange1 . To compare row to row . So , the results will became : myRange1 = (ROW) A B C 1 1 2 4 2 1 3 4 3 1 3 5 myRange2 = (ROW) F G H 1 QQQ 3 QQQ 2 2 QQQ 5 3 QQQ 4 QQQ Thank you in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
Sub dl()
Dim lr As Long Dim sh As Worksheet Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To lr For j = 1 To 3 If sh.Cells(i, j) = sh.Cells(i, j).Offset(0, 5) Then sh.Cells(i, j).Offset(0, 5) = "QQQ" End If Next Next End Sub "ytayta555" wrote in message ... Hi all , and a good day I have 2 ranges , with these values : myRange1 = (ROW) A B C 1 1 2 4 2 1 3 4 3 1 3 5 myRange2 = (ROW) F G H 1 1 3 4 2 2 3 5 3 1 4 5 I need a macro to compare every cell in myRange2 - first row !! , with every cell in myRange1 - first row !! , and , if tha value of every cell in myRange2 is equal with one of the cells of the first row of myRange1 , to change the value with "QQQ" . This must to be done in every row . After checking first row of myRange2 with first row of myRange1 , to go to next row in myRange2 , and compare with the values of the next row of myRange1 . To compare row to row . So , the results will became : myRange1 = (ROW) A B C 1 1 2 4 2 1 3 4 3 1 3 5 myRange2 = (ROW) F G H 1 QQQ 3 QQQ 2 2 QQQ 5 3 QQQ 4 QQQ Thank you in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
Try this:
Sub CellComparison() Dim range1 As Range Dim range2 As Range Dim cell1 As Range Dim cell2 As Range Dim myOffset As Byte Set range1 = Range("A1:C3") Set range2 = Range("F1:H3") myOffset = range2.Cells(1, 1).Column - range1.Cells(1, 1).Column For Each cell1 In range1 Set cell2 = cell1.Offset(0, myOffset) If cell1.Value = cell2.Value Then cell2.Value = "QQQ" Next End Sub Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
On 2 mar., 19:29, "JLGWhiz" wrote:
IT WORKS great . Thank you very much for your time . Have a good week |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
On 2 mar., 19:43, Scoops wrote:
Try this: I just have test it , and work great , too . Thank you Have a good week |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
On 2 mar., 19:43, Scoops wrote:
Try this: One ONLY question : how the code must be , if I have the second range bigger then first range : Eg : Set range1 = Range("A1:C3") Set range2 = Range("F1:K3") ??? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
On 2 mar., 19:57, ytayta555 wrote:
One ONLY question : how the code must be , if I have the second range bigger then first range : Eg : Set range1 = Range("A1:C3") Set range2 = Range("F1:K3") Please very much here , to help me to find the last solution to my problem . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
You could always run two separate "For Each" loops... one for range1 and the
second one for range2. -- Rick (MVP - Excel) "ytayta555" wrote in message ... On 2 mar., 19:57, ytayta555 wrote: One ONLY question : how the code must be , if I have the second range bigger then first range : Eg : Set range1 = Range("A1:C3") Set range2 = Range("F1:K3") Please very much here , to help me to find the last solution to my problem . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
On 2 mar., 21:39, "Rick Rothstein"
wrote: You could always run two separate "For Each" loops... one for range1 and the second one for range2. Maybe for the programmers is very easy , but I really don't know how .. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
You have this loop for range1...
For Each cell1 In range1 Set cell2 = cell1.Offset(0, myOffset) If cell1.Value = cell2.Value Then cell2.Value = "QQQ" Next Just put this loop for range2 immediately following it in your code... For Each cell1 In range2 Set cell2 = cell1.Offset(0, myOffset) If cell1.Value = cell2.Value Then cell2.Value = "QQQ" Next Of course, I'm assuming you want to assign "QQQ" to the cells located at the same offset distance (the myOffset variable) from range2's cells as you did for range1's cells. -- Rick (MVP - Excel) "ytayta555" wrote in message ... On 2 mar., 21:39, "Rick Rothstein" wrote: You could always run two separate "For Each" loops... one for range1 and the second one for range2. Maybe for the programmers is very easy , but I really don't know how .. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
On 2 mar., 22:37, "Rick Rothstein"
wrote: Of course, I'm assuming you want to assign "QQQ" to the cells located at the same offset distance (the myOffset variable) from range2's cells as you did for range1's cells. It was my mistake in description , and I apologise . The second range , myRange 2 is bigger in columns then myRange1 For eg : Set range1 = Range("A1:C3") Set range2 = Range("F1:K3") Every cell in myRange2 I need to compare with every cell in myRange1 , but NOT first cell from myRange2 with ALL cells from myRange1 , then second ... I mean , ALL cells from range2 compare with all cells from range 1 , BUT , ,,ROW BY ROW,, , with other words , in the same row . The cells are not located at the same offset distance , because range 2 is bigger in columns then range 1 . It's a little bit complicate . Please to assist me ..... |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
This is a little trickier than the other one:
Sub QQQ() Dim lr As Long, i As Long, j As Long, c As Range Dim sh As Worksheet, rng As Variant Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row rng = Array("A1:C1", "A2:C2", "A3:C3") For i = 0 To 2 For j = 1 To 6 For Each c In Range(rng(i)) If c = sh.Cells(c.Row, j).Offset(0, 5) Then sh.Cells(c.Row, j).Offset(0, 5) = "QQQ" End If Next Next Next End Sub "ytayta555" wrote in message ... On 2 mar., 22:37, "Rick Rothstein" wrote: Of course, I'm assuming you want to assign "QQQ" to the cells located at the same offset distance (the myOffset variable) from range2's cells as you did for range1's cells. It was my mistake in description , and I apologise . The second range , myRange 2 is bigger in columns then myRange1 For eg : Set range1 = Range("A1:C3") Set range2 = Range("F1:K3") Every cell in myRange2 I need to compare with every cell in myRange1 , but NOT first cell from myRange2 with ALL cells from myRange1 , then second ... I mean , ALL cells from range2 compare with all cells from range 1 , BUT , ,,ROW BY ROW,, , with other words , in the same row . The cells are not located at the same offset distance , because range 2 is bigger in columns then range 1 . It's a little bit complicate . Please to assist me ..... |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
Disregard the previous code and use this one;
Sub QQQ() Dim lr As Long, i As Long, j As Long, c As Range Dim sh As Worksheet, rng As Variant Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row rng = Array("A1:C1", "A2:C2", "A3:C3") For i = 0 To 2 For j = 6 To 12 For Each c In Range(rng(i)) If c = sh.Cells(c.Row, j) Then sh.Cells(c.Row, j) = "QQQ" End If Next Next Next End Sub The other one was comparing the wrong cells. "ytayta555" wrote in message ... On 2 mar., 22:37, "Rick Rothstein" wrote: Of course, I'm assuming you want to assign "QQQ" to the cells located at the same offset distance (the myOffset variable) from range2's cells as you did for range1's cells. It was my mistake in description , and I apologise . The second range , myRange 2 is bigger in columns then myRange1 For eg : Set range1 = Range("A1:C3") Set range2 = Range("F1:K3") Every cell in myRange2 I need to compare with every cell in myRange1 , but NOT first cell from myRange2 with ALL cells from myRange1 , then second ... I mean , ALL cells from range2 compare with all cells from range 1 , BUT , ,,ROW BY ROW,, , with other words , in the same row . The cells are not located at the same offset distance , because range 2 is bigger in columns then range 1 . It's a little bit complicate . Please to assist me ..... |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Ranges problem ,
On 3 mar., 04:48, "JLGWhiz" wrote:
Disregard the previous code and use this one; Sub QQQ() Dim lr As Long, i As Long, j As Long, c As Range I JUST TRYED IT , AND WORK REALLY OK . You are just GREAT . THANK YOU SO MUCH FOR HELP . All the best to you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.Match - Is it possible to use named ranges? | Excel Programming | |||
Application.Match and Ranges | Excel Programming | |||
Like match between 2 ranges | Excel Discussion (Misc queries) | |||
Problem w/ Match prop vs. Match method | Excel Programming |