Copy If statement from single cell to all cells in a column

i have been able to use the below code in order to input either "Print Documents" or "E-Delivery" into cell K2 using an If/Else statement. I am looking to try to extend this same statement for all of Column K (i.e K3 searching O3 and returning the correct value). Any help would be much appreciated. many thanks, Michael

Sub InsertType()

Dim score As Integer, result As String
score = Range("O2").Value

If score > 0 Then
    result = "Print Documents"
Else
    result = "E-Delivery"
End If

Range("K2").Value = result

End Sub

1 answer

  • answered 2020-06-01 19:11 Wizhi

    You can loop your VBA code :)

    Code:

    Sub InsertType()
    
    Dim ws As Worksheet
    Dim score As Integer, result As String
    Dim i As Long
    Dim lRow As Long
    
    Set ws = ActiveWorkbook.Worksheets("Sheet1") 'Name of worksheet
    
    lRow = ws.Cells(Rows.Count, "O").End(xlUp).Row 'Find the last row in column O
    
    
    
    For i = 1 To lRow 'Loop from row 1 to last row
    score = ws.Range(ws.Cells(i, "O"), ws.Cells(i, "O")).Value 'Take value from row i and column O
    
    If score > 0 Then
        result = "Print Documents"
    Else
        result = "E-Delivery"
    End If
    
    ws.Range(ws.Cells(i, "K"), ws.Cells(i, "K")).Value = result 'Print Result in Row i and column K
    Next i
    
    End Sub
    

    Result:

    enter image description here