Macro to increase all cells with a value that have a yes next to them by a certain number

[tonywatsonhelp]

Hi Everyone,

I need a macro that when its run will do the following
with active sheet

ceck there is a number in cell AK28, if not message box "Please enter a Number of Days"
exit
if there is then

Look at the range AK32:AK47
with each cell check if they have a value in them and that the next column has "no" in it.
only if both of these are correct increase the value in the cell by the number in AK28

so for example

AK28 = 30


AK33 = 100 & AL33 = "No" so Increase AK33 by 30 so AK33 =130
do this for all cells in the range but only if they match the above criteria otherwise leave how they are.

please help

Thanks

Tony

 

[jasonb75]

Your thread title says increase cells that have "Yes" next to them, but the text of your question says "No", I've gone with "No" in the code so if it's wrong, that's all theat will need changing.

Option Explicit
Sub add_days()
Dim c As Range
If Range("AK28").Value <> "" Then
    For Each c In Range("AK32:AK47")
        If c.Value > 0 And c.Offset(, 1).Value = "No" Then c.Value = c.Value + Range("AK28").Value
    Next
Else
    MsgBox "Please enter number of days"
End If
End Sub

 

[tonywatsonhelp]

jasonb75 Thank you for your help,
I wanted "No" sorry for the confusion, all seam to work great thank you
Tony