Fill each cell in a row of cells with text

[ThomasOES]

I have code that works, but it feels crude. I'm filling a row of cells with chemical element symbols. Is there a better way than the code below?

'FILL TOP ROW WITH ELEMENTS IN CLASSICAL ORDER
Range("A1").Select
Range("A1") = "Standard"
Range("B1") = "C"
Range("C1") = "Mn"
Range("D1") = "P"
Range("E1") = "S"
Range("F1") = "Si"
Range("G1") = "Cu"
Range("H1") = "Ni"
Range("I1") = "Cr"
Range("J1") = "V"
Range("K1") = "Mo"
Range("L1") = "W"
Range("M1") = "Co"
Range("N1") = "Ti"
Range("O1") = "As"
Range("P1") = "Sn"
Range("Q1") = "Al"
Range("R1") = "Nb"
Range("S1") = "Ta"
Range("T1") = "B"
Range("U1") = "Pb"
Range("V1") = "Zr"
Range("W1") = "Sb"
Range("X1") = "Ca"
Range("Y1") = "Mg"
Range("Z1") = "La"
Range("AA1") = "Zn"
Range("AB1") = "Be"
Range("AC1") = "N"
Range("AD1") = "Fe"
Range("AE1") = "END"

Thanks for any tips

Tom

 

[Joe4]

Since there is no formula or pattern to use to generate an entry from the previous one, probably not (if your goal it to avoid listing all the elements in your code).

You could store all of the chemical symbols in one long array, and then cycle through it to populate all the cells. But that means you still have to enter all the symbols somewhere (I don't see how you can get away from that unless you import the data from a file or web site).

 

[Joe4]

Here is an example of what the array method would look like.
To do it for your whole list, just keep adding the elements to the array in the proper order (so the number of lines in the code won't increase, just the length of the array).

Sub MyElements()

    Dim arr
    Dim i As Long
    
'   Store elements in array in order
    arr = Array("Standard", "C", "Mn", "P", "S", "Si", "END")
    
'   Insert all elements across row 1
    For i = LBound(arr) To UBound(arr)
        Cells(1, i + 1) = arr(i)
    Next i
        
End Sub

 

[Fluff]

Or, without the loop

Sub MyElements()

    Dim arr
    Dim i As Long
    
'   Store elements in array in order
    arr = Array("Standard", "C", "Mn", "P", "S", "Si", "END")
    
'   Insert all elements across row 1
   Range("A1").Resize(, UBound(arr) + 1).Value = arr
End Sub

 

[ThomasOES]

Thanks Fluff
Works perfect and much easier to edit element list.
Tom

 

[Fluff]

Glad we could help & thanks for the feedback.