Match Ranges problem ,

[ytayta555]

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

 

[JLGWhiz]

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

 

[Scoops]

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

 

[ytayta555]

IT WORKS great . Thank you very much
for your time . Have a good week

 

[ytayta555]

Try this:

I just have test it , and work great , too . Thank you
Have a good week

 

[ytayta555]

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")

???

 

[ytayta555]

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 .

 

[Rick Rothstein]

You could always run two separate "For Each" loops... one for range1 and the
second one for range2.

 

[ytayta555]

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

 

[Rick Rothstein]

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.

 

[ytayta555]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

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]

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