Making two columns equal using double for each

[Moonbeam111]

I have a code that isn't working. I'm trying to make the two ranges match each other IF there is a difference then it needs to make that cell equal to the other one.

cell1 = Range("D22:D25")
cell2 = Range("D135:D138")
For Each cell2 In Range("D135:D138")
For Each cell1 In Range("D22:D25")
If cell1 <> cell2 Then
cell1 = cell2
End If
Next cell1
Next cell2

Please assist.

 

[Cubist]

If you want to always match range1 then you can just make range2=range1. Don't need to check.

 

[Moonbeam111]

Yes this is what i did.

If Range("D22") <> Range("D135") Then
Range("D135") = Range("D22")
End If

If Range("D23") <> Range("D136") Then
Range("D136") = Range("D23")
End If


If Range("D24") <> Range("D137") Then
Range("D137") = Range("D22")
End If

If Range("D25") <> Range("D138") Then
Range("D138") = Range("D25")
End If

 

[Alex Blakenburg]

My first reaction was the same a that of Cubist. If you are setting the first range = to the second range you don't need to the IF. There is no harm in replacing the value when they are equal since you won't see any difference anyway.
So @Cubist suggestion was actually this:

Sub SetRangesEqual()
    Range("D22:D25").Value = Range("D135:D138").Value
End Sub

There are a number of ways to change what you have into a Loop, here is one:

Sub LoopingThroughRange()
    Dim rng1 As Range, rng2 As Range
    Dim i As Long
    
    Set rng1 = Range("D22:D25")
    Set rng2 = Range("D135:D138")
    
    For i = 1 To rng1.Cells.Count
        If rng1.Cells(i, 1).Value <> rng2.Cells(i, 1).Value Then
            ' Do something
            rng1.Cells(i, 1).Value = rng2.Cells(i, 1).Value
        End If
    Next i

End Sub

 

[MARK858]

@Moonbeam111 just

Range("D135:D138").Value = Range("D22:D25").Value

Edit: Oops I see Alex has just posted the same for what @Cubist suggested

 

[Alex Blakenburg]

Oops my first suggestion is overlaps with @MARK858's. Although I am reading your replacement as being the other way around.

 

[MARK858]

Oops my first suggestion is overlaps with @MARK858's. Although I am reading your replacement as being the other way around.

You were 1st by I don't know how many seconds

Although I am reading your replacement as being the other way around.

Correct, I'll edit, Edit again: actually no, see post 3

 

[Alex Blakenburg]

@Moonbeam111 you are replacing in the opposite direction Post #1 vs Post #3. Which one is correct ?

 

[Moonbeam111]

Post #3 is the correct one, my bad.

 

[Alex Blakenburg]

Post #3 is the correct one, my bad.

In that case for the 1 line version use what Mark posted, for the looping version here it is reversed.
Note: I could have left the If statement unchanged but I think its easier to follow if they are both the same way around.
Same goes for the For loop I changed it to use rng2 as the primary.

Sub LoopingThroughRange()
    Dim rng1 As Range, rng2 As Range
    Dim i As Long
 
    Set rng1 = Range("D22:D25")
    Set rng2 = Range("D135:D138")
 
    For i = 1 To rng2.Cells.Count
        If rng2.Cells(i, 1).Value <> rng1.Cells(i, 1).Value Then
            ' Do something
            rng2.Cells(i, 1).Value = rng1.Cells(i, 1).Value
        End If
    Next i

End Sub