Cleaner VBA Code

[rzacek87]

I was wondering if there is a cleaner way to code this loop. Thanks in advance.

Sub A()
    Dim myLastRow As Long
    Dim i As Long
    Dim InvD As Date
        
    InvD = InputBox("Invoice Date mm/dd/yy")
        
    myLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 2 To myLastRow
        If Range("I" & i).Value Like "Sub-Producer" & "*" Then
            Range("I" & i).Value = "Sub-Producer Payment - " & Format(DateAdd("M", -1, Now), "MMMM YYYY")
            Range("V" & i).Value = 23140
            Range("E" & i).Value = InvD
            Range("F" & i).Value = "=R[0]C[-1]+45"
        End If
        If Range("I" & i).Value Like "Royalty" & "*" And InStr(1, Range("D" & i).Value, 7) = 7 Then
            Range("I" & i).Value = "Royalty Guarantee - " & Format(DateAdd("M", 0, Now), "MMMM YYYY")
            Range("V" & i).Value = 23150
            Range("E" & i).Value = InvD
            Range("F" & i).Value = "=R[0]C[-1]+45"
        End If
        If Range("I" & i).Value Like "Royalty" & "*" And InStr(1, Range("D" & i).Value, 7) <> 7 Then
            Range("I" & i).Value = "Royalty Payment - " & Format(DateAdd("M", 0, Now), "MMMM YYYY")
            Range("V" & i).Value = 23150
            Range("E" & i).Value = InvD
            Range("F" & i).Value = "=R[0]C[-1]+45)"
        End If
    Next
End Sub

 

[Joe4]

Can column I ever have a value that does not start with "Sub-Producer" or "Royalty"?

If "Yes", I think your code looks pretty good. If "No", then you could probably simplify to:

Sub A()
    Dim myLastRow As Long
    Dim i As Long
    Dim InvD As Date
        
    InvD = InputBox("Invoice Date mm/dd/yy")
        
    myLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For i = 2 To myLastRow
        Range("E" & i).Value = InvD
        Range("F" & i).Value = "=R[0]C[-1]+45"
        If Range("I" & i).Value Like "Sub-Producer" & "*" Then
            Range("I" & i).Value = "Sub-Producer Payment - " & Format(DateAdd("M", -1, Now), "MMMM YYYY")
            Range("V" & i).Value = 23140
        Else
            Range("V" & i).Value = 23150
            If Range("I" & i).Value Like "Royalty" & "*" And InStr(1, Range("D" & i).Value, 7) = 7 Then
                Range("I" & i).Value = "Royalty Guarantee - " & Format(DateAdd("M", 0, Now), "MMMM YYYY")
            Else
                Range("I" & i).Value = "Royalty Payment - " & Format(DateAdd("M", 0, Now), "MMMM YYYY")
            End If
        End If
    Next
End Sub

 

[6StringJazzer]

Added error checking, factored out commonality for Royalty, made ElseIf since conditions are mutually exclusive

Sub A()

   Dim myLastRow As Long
   Dim i As Long
   Dim InvD As Date
  
   On Error GoTo BadDate
   InvD = InputBox("Invoice Date mm/dd/yy")
   On Error GoTo 0
  
   myLastRow = Cells(Rows.Count, "B").End(xlUp).Row
  
   For i = 2 To myLastRow
  
      If Range("I" & i).Value Like "Sub-Producer" & "*" Then
     
         Range("I" & i).Value = "Sub-Producer Payment - " & Format(DateAdd("M", -1, Now), "MMMM YYYY")
         Range("V" & i).Value = 23140
         Range("E" & i).Value = InvD
         Range("F" & i).Value = "=R[0]C[-1]+45"
        
      ElseIf Range("I" & i).Value Like "Royalty" & "*" Then
     
         Range("V" & i).Value = 23150
         Range("E" & i).Value = InvD
         Range("F" & i).Value = "=R[0]C[-1]+45"
        
         If InStr(1, Range("D" & i).Value, 7) = 7 Then
            Range("I" & i).Value = "Royalty Guarantee - " & Format(DateAdd("M", 0, Now), "MMMM YYYY")
         Else
            Range("I" & i).Value = "Royalty Payment - " & Format(DateAdd("M", 0, Now), "MMMM YYYY")
         End If
     
      End If
  
   Next i
  
   Exit Sub
  
BadDate:
   MsgBox "Enter a valid date in the form mm/dd/yy"
   
End Sub

 

[rzacek87]

Thanks. I knew there would be way to combine the royalty one together but I always have issues with if statements in VBA.

 

[Joe4]

Thanks. I knew there would be way to combine the royalty one together but I always have issues with if statements in VBA.

Did you take a look at the code I posted in the very first reply? I think it is even shorter, as I removed a few more redundancies.