CODEWITHSUNDEEP
excelvba
Brexlin
Brexlin

Reputation: 23

VBA- How to change value in cell range if met condition

I would like to ask how to change value in cell range.

I want to change value in cell range in column C to column Q if met condition in Column B If cell in Column B = FALSE then all values in column C to Q will change to "0"

enter image description here

the result will be like this..

enter image description here

I have tried with code but it takes so long time to run because I use so many "IF"

Upvotes: 0

Views: 2301

Answers (3)

Excel Hero
Excel Hero

Reputation: 14764

This will do it instantly from VBA...

Sub Brexlin()

    [c1:q5] = [if(c1:a5,c1:q5,)]

End Sub

Update

After your comments, please see the updated answer below...

Sub Brexlin()

    [c1:q5] = [if(b1:b5="DARK",c1:q5,)]

End Sub

Upvotes: 2

BZngr
BZngr

Reputation: 681

Not sure where/how you are getting your values, but by placing

=IF($B1 = FALSE, 0, "YourValueSource")

in C1 and copying thru to Qn (where n is last row number) will handle setting values to 0. Now you just need to add code to replace "YourValueSource" with the value or formula you want in each cell.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96763

Give this simple macro a try:

Sub ZeroMaker()
    Dim i As Long
    For i = 1 To 5
        If Cells(i, 2).Value = False Then Range(Cells(i, "C"), Cells(i, "Q")).Value = 0
    Next i
End Sub

Upvotes: 1

Related Questions