Need to change value to all cells after C1-BB1 to match B1 if it has a value-CodePudding

Need help with this one. New here. If there is any value in cell C1 thru BB1 then it = Cell B1. But then loop thru the rest of sheet to do the same but value changes to B2 then B3 then B4 and so on. This is what i have so far.

Private Sub AddingValue()
    For Each cell In Range("C1:BB1")
        If cell.Value > 0 Then
            cell.Value = Range("B1").Value
        End If
    Next 
End Sub

CodePudding user response：

You have to iterate over the usedrange of the sheet:

Public Sub AddingValue()
Dim ws As Worksheet
Set ws = ActiveSheet    '--> adjust this to your needs

Dim c As Range, r As Range
Dim BValue As Variant

For Each r In ws.UsedRange.Rows 'look into each row
    BValue = r.Cells(, 2)   'store value of column B

   'resize the row range to start at C
    Set r = r.Offset(, 2).Resize(1, r.Columns.Count - 2) 

    For Each c In r.Cells
        If Not IsEmpty(c.Value) Then
            c.Value = BValue
        End If
    Next
Next

End Sub