[VBA] Enter a value in a neighboring cell based on cell occupancy

[Martin_H]

Hi Team,

let there be a specific range:

Union(Range("E14:E19,E21:E26,E28:E33,E35:E40,E42:E47,E49:E54,E56:E61,E63:E68,E70:E75,E77:E82,E84:E89,E91:E96,E98:E103,E105:E110,E112:E117,E119:E124,E126:E131,E133:E138,E140:E145,E147:E152"), Range("E154:E159,E161:E166,E168:E173,E175:E180,E182:E187,E189:E194,E196:E201,E203:E208,E210:E215,E217:E222,E224:E229,E231:E236,E238:E243,E245:E250,E252:E257"))

For each occupied E in my range (↑), I want to enter the value 10 in the same row as E, but in the AC column.

For example:
Cell E14 is occupied with text that is formatted as General, macro should insert value 10 in the cell AC14.
The macro should check the entire range and enter a value 10 in the neighboring AC cell for each occupied E.

Thank you for help.

 

[Alex Blakenburg]

No problem. Glad I could help.

 

[bebo021999]

Since you range from row 14 to 257, exclude {20,27,34,...} (from 20 with step 7) :

Sub test()
Dim i&, u As Range
Set u = Union(Range("E20"), Range("E27"))
For i = 34 To 251 Step 7
    Set u = Union(u, Cells(i, "E"))
Next
For i = 14 To 257
    If Intersect(u, Cells(i, "E")) Is Nothing And Not IsEmpty(Cells(i, "E")) Then
        With Range("AC" & i)
            .Value = 10
            .AddComment "Hello"
        End With
    End If
Next
End Sub