Creating a more efficient VBA

[RPM7]

I've created a macro to increase a range of cells incrementally based on the value of another cell.
I have very limited VBA skills so it is quite cumbersome.
All works fine, but it seems to be very slow at updating.

Is there a cleaner way of writing this code?

NB: I will have to repeat this code again in 10 other places.
i.e N15 will become Q15, T15 etc...
L15 & M15 will remain the same as will F13.

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
If Range("L15").Value <= Range("F13").Value Or Range("M15").Value <= Range("F13").Value Then Range("N15").Value = Range("N15").Value + 1
If Range("L16").Value <= Range("F13").Value Or Range("M16").Value <= Range("F13").Value Then Range("N16").Value = Range("N16").Value + 1
If Range("L17").Value <= Range("F13").Value Or Range("M17").Value <= Range("F13").Value Then Range("N17").Value = Range("N17").Value + 1
If Range("L18").Value <= Range("F13").Value Or Range("M18").Value <= Range("F13").Value Then Range("N18").Value = Range("N18").Value + 1
If Range("L19").Value <= Range("F13").Value Or Range("M19").Value <= Range("F13").Value Then Range("N19").Value = Range("N19").Value + 1
If Range("L20").Value <= Range("F13").Value Or Range("M20").Value <= Range("F13").Value Then Range("N20").Value = Range("N20").Value + 1
If Range("L21").Value <= Range("F13").Value Or Range("M21").Value <= Range("F13").Value Then Range("N21").Value = Range("N21").Value + 1
If Range("L22").Value <= Range("F13").Value Or Range("M22").Value <= Range("F13").Value Then Range("N22").Value = Range("N22").Value + 1
If Range("L23").Value <= Range("F13").Value Or Range("M23").Value <= Range("F13").Value Then Range("N23").Value = Range("N23").Value + 1
If Range("L24").Value <= Range("F13").Value Or Range("M24").Value <= Range("F13").Value Then Range("N24").Value = Range("N24").Value + 1
If Range("L25").Value <= Range("F13").Value Or Range("M25").Value <= Range("F13").Value Then Range("N25").Value = Range("N25").Value + 1
If Range("L26").Value <= Range("F13").Value Or Range("M26").Value <= Range("F13").Value Then Range("N26").Value = Range("N26").Value + 1
If Range("L27").Value <= Range("F13").Value Or Range("M27").Value <= Range("F13").Value Then Range("N27").Value = Range("N27").Value + 1
If Range("L28").Value <= Range("F13").Value Or Range("M28").Value <= Range("F13").Value Then Range("N28").Value = Range("N28").Value + 1
If Range("L29").Value <= Range("F13").Value Or Range("M29").Value <= Range("F13").Value Then Range("N29").Value = Range("N29").Value + 1
If Range("L30").Value <= Range("F13").Value Or Range("M30").Value <= Range("F13").Value Then Range("N30").Value = Range("N30").Value + 1
Application.ScreenUpdating = True
Exit Sub

End Sub

 

[Jonmo1]

You could write that like this

Dim i As Long
For i = 15 To 30
    If Range("L" & i).Value <= Range("F13").Value Or Range("M" & i).Value <= Range("F13").Value Then Range("N" & i).Value = Range("N" & i).Value + 1
Next I

Not sure that would be any more efficient, but it's simpler.

 

[RPM7]

Thanks Jonmo1. That's definitely more refined and easier to copy over for the other cells.

I was making a modification to include an ElseIf statement but its now returned an error.
Do you know what I've done wrong?

Private Sub CommandButton11B_Click()
Application.ScreenUpdating = False

Dim i As Long
For i = 15 To 30
If Range("N" & i).Value = 0 Then Range("N" & i).Value = Range("N" & i).Value + 1
ElseIf Range("L" & i).Value <= Range("F13").Value Or Range("M" & i).Value <= Range("F13").Value Then Range("N" & i).Value = Range("N" & i).Value + 1
Next i
End If

Application.ScreenUpdating = True
Exit Sub
End Sub

Basically, If N Range and all the other ranges Q, T, W, etc.. contain a 0, The formula in L & M returns an error.
I wanted to be able to create a 1 at the N Range in this instance.

 

[Michael M]

Maybe this.....UNTESTED
Yuo had the End If outside the next loop

Private Sub CommandButton11B_Click()
Application.ScreenUpdating = False

Dim i As Long
For i = 15 To 30
If Range("N" & i).Value = 0 Then
Range("N" & i).Value = Range("N" & i).Value + 1
ElseIf Range("L" & i).Value <= Range("F13").Value Or Range("M" & i).Value <= Range("F13").Value Then Range("N" & i).Value = Range("N" & i).Value + 1
End If
Next i

Application.ScreenUpdating = True
Exit Sub
End Sub

 

[RPM7]

That worked perfectly, thanks Michael M.

 

[Jonmo1]

Could also add it as another condition to the original OR

Dim i As Long
For i = 15 To 30
    If Range("L" & i).Value <= Range("F13").Value Or Range("M" & i).Value <= Range("F13").Value Or Range("N" & i).Value = 0 Then Range("N" & i).Value = Range("N" & i).Value + 1
Next I

 

[RPM7]

Thanks for all the feedback guys.

I made a couple tweaks but it seems to be crashing on the elseif statement.

Private Sub CommandButton11A_Click()
Application.ScreenUpdating = False
Dim i As Long
For i = 15 To 30
If Range("O7").Value = "Manual" And Range("N" & i).Value > 0 Then Range("N" & i).Value = Range("N" & i).Value - 1

ElseIf Range("O7").Value <> "Manual" And Range("L" & i).Value <= Range("F13").Value Then Range("N" & i).Value = Range("N" & i).Value - 1

ElseIf Range("O7").Value <> "Manual" And Range("M" & i).Value <= Range("F13").Value Then Range("N" & i).Value = Range("N" & i).Value - 1

End If
Next i
Application.ScreenUpdating = True
End Sub

There's probably a cleaner way of merging the last two statements but I don't know how to.

 

[RPM7]

Got it sorted now. Not sure what I did though. HA!

Private Sub CommandButton11A_Click()
Application.ScreenUpdating = False
Dim i As Long
For i = 15 To 30

If Range("O7").Value = "Manual" And Range("N" & i).Value > 0 Then
Range("N" & i).Value = Range("N" & i).Value - 1

ElseIf Range("O7").Value <> "Manual" And Range("L" & i).Value <= Range("F13").Value Then
Range("N" & i).Value = Range("N" & i).Value - 1

ElseIf Range("O7").Value <> "Manual" And Range("M" & i).Value <= Range("F13").Value Then
Range("N" & i).Value = Range("N" & i).Value - 1
End If

Next i
Application.ScreenUpdating = True
End Sub

 

[Michael M]

Private Sub CommandButton11A_Click()
Application.ScreenUpdating = False
Dim i As Long
For i = 15 To 30

If Range("O7").Value = "Manual" And Range("N" & i).Value > 0 Then
Range("N" & i).Value = Range("N" & i).Value - 1

ElseIf Range("O7").Value <> "Manual" And Range("L" & i).Value <= Range("F13").Value [color=red][b]Or [/color][/b]Range("M" & i).Value <= Range("F13").Value Then
Range("N" & i).Value = Range("N" & i).Value - 1
End If

Next i
Application.ScreenUpdating = True
End Sub