MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Compare two ranges of cells and return the value into one of the ranges


Posted by frame on October 16, 2000 6:50 PM

Hi,
I'm trying to do a comparison between two ranges of cells and I want to return a value into the second range.
For example:
My first range1: A1:IS1
My second range2: A2:IS20
I need to compare the value for each row in range2 with the value in range1 and return the value into range2.
My IF condition is like this,
If Range1(cell1) = Range2(cell1) Then Range2(cell1) = 1
If Range1(cell2) = Range2(cell2) then Range2(cell2) = 1
and so on (until end of line).

I've been trying to do this but I didn't able to get it solved. Please help.. I'm new in VBA.

Regards,
Frame


Posted by Celia on October 17, 2000 8:11 AM


Frame

Dim range1 As Range, cell As Range, cell2 As Range
Set range1 = Range("A1:IS1")
For Each cell In range1
Set cell2 = cell.Offset(1, 0)
If cell.Value = cell2.Value Then cell2.Value = 1
Next

Celia

Posted by Frame on October 17, 2000 6:30 PM

Hi Celia,
I've tried the code, but it actually compare the Range1 with the first row of range 2 only. How can I loop it until the next 20 rows of range 2?

Thanks.

Frame

Posted by Celia on October 18, 2000 3:04 AM


Frame

Dim range1 As Range, cell As Range, cell2 As Range
Dim x%
Set range1 = Range("A1:IS1")
x = 1
Do Until x = 20
For Each cell In range1
Set cell2 = cell.Offset(x, 0)
If cell.Value = cell2.Value Then cell2.Value = 1
Next
x = x + 1
Loop

Celia


Posted by Celia on October 18, 2000 3:32 AM

A bit quicker


This should be a bit quicker :-

Dim range1 As Range, cell As Range, cell2 As Range
Dim match As Range, x%
Set range1 = Range("A1:IS1")
x = 1
Do Until x = 20
For Each cell In range1
Set cell2 = cell.Offset(x, 0)
If cell.Value = cell2.Value Then
If match Is Nothing Then
Set match = cell2
Else
Set match = Union(match, cell2)
End If
End If
Next
x = x + 1
Loop
match.Formula = "1"

Celia


Posted by Frame on October 19, 2000 12:56 AM

Re: A bit quicker

Hi Celia,
Thanks so much. It works. I used the 2nd code that you've given me.

Thanks!

Frame