Help cleaning up some code if you please

[EssKayKay]

Hello,
This is no biggie here - really just wondering if there is a more efficient way to code this (my programming skills are very limited to say the least).

I have three routines that basically do the same thing to three sets of cells. I simply copied the code from the first set; then edit it for the next two. It appears to be doing what I want. However, I suspect there is a better way to achieve this.

I don’t expect anyone to spend much time on this but if it looks easy enough to clean up, I would be interested.

Here’s my code –

‘- - - 1st One-Time - - -
     If Range("K8:K9").Locked = False And Range("K9") = "" Then
          MsgBox " Missing One-Time Amount" & vbNewLine & " Re-enter Payment details.", , " One-Time Pmt."
          Range("K8,K9").Select
          Selection.ClearContents
          Range("K9").Select
          Selection.Locked = True
          With Selection.Interior
              .Color = 15984868
         End With
         Range("K8").Select
         Exit Sub
End If


‘- - - 2nd One-Time - - -
     If Range("K10:K11").Locked = False And Range("K11") = "" Then
          MsgBox " Missing One-Time Amount" & vbNewLine & " Re-enter Payment details.", , " One-Time Pmt."
          Range("K10,K11").Select
          Selection.ClearContents
          Range("K11").Select
          Selection.Locked = True
          With Selection.Interior
              .Color = 15984868
          End With
          Range("K10").Select
          Exit Sub
     End If

‘- - - 3rd One-Time - - -
     If Range("K12:K13").Locked = False And Range("K13") = "" Then
          MsgBox " Missing One-Time Amount" & vbNewLine & " Re-enter Payment details.", , " One-Time Pmt."
          Range("K12,K13").Select
          Selection.ClearContents
          Range("K13").Select
          Selection.Locked = True
          With Selection.Interior
              .Color = 15984868
          End With
          Range("K12").Select
     Exit Sub
End If

Thanks for viewing,
Steve

 

[EssKayKay]

I had a similar solution to mumps (and I stole a few simplifications as well), but it allows you to set the first row (myRowBase). I did add some extra variables (myCell1 and myCell2) just for clarity and troubleshooting.

Sub test()
Dim myRowBase As Integer
Dim myCell1 As String
Dim myCell2 As String

myRowBase = 8
For i = 0 To 2
    myCell1 = "K" & myRowBase + 2 * i
    myCell2 = "K" & myRowBase + 2 * i + 1
    If Range(myCell1 & ":" & myCell2).Locked = False And Range(myCell2) = "" Then
        MsgBox " Missing One-Time Amount" & vbNewLine & " Re-enter Payment details.", , " One-Time Pmt."
        Range(myCell1 & ":" & myCell2).ClearContents
        With Range(myCell2)
            .Locked = True
            .Interior.Color = 15984868
        End With
        Range(myCell1).Select
        Exit Sub
    End If
Next i

End Sub

Thank you Nate for your suggestion. I installed the code into my PmtMade sub. I receive a Compile Error - Variable not defined.
Any other suggestions would be appreciated.

Here's the error message:

 

[mumps]

Have you tried the code I suggested in Post #10?

 

[NateSC]

Oops. I did this quick and didn't have explicit turned on.

Add the following in the declarations:

Dim i as integer

You can also try @mumps code in Post #10. I used the logic you had already written. I believe he was trying to build a more robust logic. Or try both and see which one runs faster.

 

[EssKayKay]

Have you tried the code I suggested in Post #10?

Yes Mumps I tried your revised code. Thanks for getting back to me.
When both cells (K8 & K9, or K10 & K11, or K12 & K13) are populated and I run the code it clears all cells (K8:K13).

 

[EssKayKay]

Oops. I did this quick and didn't have explicit turned on.

Add the following in the declarations:

Dim i as integer

You can also try @mumps code in Post #10. I used the logic you had already written. I believe he was trying to build a more robust logic. Or try both and see which one runs faster.

Nate,
That did it. It appears to be working as I wish. I will have to do more testing but for now, I think we are on the right track. If I notice anything else, I will be back, but for now I am very pleased.

Again, my sincere thanks to you and all others who weighed in on this. You guys are truly helpful, considerate, and appreciated.
Steve K.

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 8 To 12 Step 2
        If Range("K" & x & ":K" & x + 1).Locked = False And Range("K" & x + 1) = "" Then
            MsgBox " Missing One-Time Amount" & vbNewLine & " Re-enter Payment details.", , " One-Time Pmt."
            Range("K" & x & ":K" & x + 1).ClearContents
            Range("K" & x).Select
            With Range("K" & x + 1)
                .Locked = True
                .Interior.Color = 15984868
            End With
            Exit Sub
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[EssKayKay]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 8 To 12 Step 2
        If Range("K" & x & ":K" & x + 1).Locked = False And Range("K" & x + 1) = "" Then
            MsgBox " Missing One-Time Amount" & vbNewLine & " Re-enter Payment details.", , " One-Time Pmt."
            Range("K" & x & ":K" & x + 1).ClearContents
            Range("K" & x).Select
            With Range("K" & x + 1)
                .Locked = True
                .Interior.Color = 15984868
            End With
            Exit Sub
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

That works perfectly Mumps. I thank for your time and consideration. As noted, I am so very pleased with you all (particularly having to deal with dummies like me).

Once again Mumps, thank you so very much,
Steve

 

[NateSC]

If you need a little more speed, add the application.screenupdating off and on like @mumps has.

 

[mumps]

Glad we could help.

 

[Joe4]

EssKayKay,

When marking a post as the solution, please mark the original post that actually contains the solution, not your own post acknowledging that some other post is the solution.
So please mark the reply that best answered your question. Thank you.