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
