Search code examples
excelvbaperformancecell

Insert Multiple Values into Multiple different cells


I have the below code which updates the relevant cell with the relevant value. Even though there is only 11, I was wondering if there is a much better way, for the purpose of helping to speed up the code & also incase there is ever a need to update 000's of cells.

Range("E1").Value = "Export Date"
Range("G1").Value = "Amended Start Date"
Range("H1").Value = "Ticket Age (Working Days)"
Range("J1").Value = "Overdue (1=Yes, 0=No)"
Range("U1").Value = "TicketEntity1"
Range("V1").Value = "TicketEntity2"
Range("W1").Value = "TicketEntity3"
Range("X1").Value = "TicketEntity4"
Range("Y1").Value = "TicketEntity5"
Range("Z1").Value = "TicketEntity6"

Solution

  • Sub Test()
    
    Dim CurrentColumn As Long
    Dim i As Long
    
    CurrentColumn = 20
    
    With wsTest
        For i = 1 To 5 '<= Use the correct number according your requirements
            .Cells(1, CurrentColumn + 1).Value = "TicketEntity" & i
            CurrentColumn = CurrentColumn + 1
        Next i
    End With
    
    End Sub