Changing cells in existing range according to data in other range

user621895 0
Viewed 38

Quick question, new to VBA, how do you change cell data in a certain range according to data filter in other range?

For the Code as follows I want to first identify if values from E11 to G13 are 0. If they are, I want to change the corresponding cells in Range O9 to Q11 to 0 as well. If the values from E11 to G13 are not 0, keep the original values in range O9 to Q11.

Need help. Thanks!

Sub If_loop2()
    For Each Cell In Range("O9:Q11")
        If Range("E11:G13").Value = 0 Then
            Cell.Value = 0
        Else
            Cell.Value = Cell.Value
        End If
    Next Cell

End Sub
vba excel
1 Answers

I'm sure there are many ways to solve this, but since each cell is referenced another cell at a specific offset from it (2 rows up and 10 rows to the left), we can use Offset to check the relevant cell and do the needful:

Sub If_loop2()
    For Each c In Range("O9:Q11")
        If c.Offset(2, -10) = 0 Then
            c.Value = 0
        End If
    Next c
End Sub

Without using Offset, we can loop over one of the ranges in order using a cells property of the range (it will loop from left to right, like reading a book). Then compare to the cell in the other range, with the same index:

Sub If_loop3()

Dim r1 As Range
Dim r2 As Range
    
    Set r1 = Range("O9:Q11")
    Set r2 = Range("E11:G13")
    
    For i = 1 To r1.Count
        If r2.Cells(i) = 0 Then
            r1.Cells(i) = 0
        End If
    Next i
            
End Sub
P.S. a non-VBA solution

It can often be preferable to use a non-VBA solution, as non-VBA solutions are easy to understand and maintain. So to that end, simply create a third range with some standard IF() formulas.

For example, in cell O13 use the formula =IF(E11=0,0,O9) and copy this formula across two more cells and down two cells. The output will also agree with the desired results (i.e., if E11 is zero, return 0, otherwise return the value of O9). Now you can work in the ranges E11:G13 and O9:Q11 as much as you want, and the "output" range in O13:Q15 will keep track of these comparisons.

topsail0
Related