Prepend a fixed set of numbers to a sequence-CodePudding

Suppose I have the following variables:

Code = "XYZ123"
First = 13
Last = 27

I need to fill in the rows between First and Last in N column with

<fifth character in the code (i.e. '2')> & 
<fourth character in the code (i.e. '1')> & 
<sixth character in the code (i.e. '3')> & 
<sequence starting from '1' with a length of 'Last - First   1'>

So, my desired output for each cell in "N" column should be

N13: 2131
N14: 2132
N15: 2133
.
.
.
N27: 21315

P.S. It would be great if the VBA solution is not based on a for loop.

CodePudding user response：

Try below sub-

Sub FilSequence()
Dim code As String, tmp As String
Dim i As Long, First As Long, Last As Long

    code = "XYZ123"
    First = 13
    Last = 27
    
    tmp = Mid(code, 5, 1) & Mid(code, 4, 1) & Mid(code, 6, 1)
    
    For i = First To Last
        Cells(i, "N") = tmp & i - (First - 1)
    Next i
    
End Sub

CodePudding user response：

Still maybe a bit of a cheat, but you could try:

Sub test()

    Dim code As String, tmp As String
    Dim i As Long, First As Long, Last As Long, Rows As Long

    code = "XYZ123"
    First = 13
    Last = 27
    Rows = Last - First   1
    
    tmp = Mid(code, 5, 1) & Mid(code, 4, 1) & Mid(code, 6, 1)
    
    Range("N" & First).Resize(Rows, 1).Value = Evaluate(tmp & "&" & "sequence(" & Rows & ")")
      
End Sub

to avoid the loop. Apologies for recycling some code from @Harun24hr's answer, it does the job very nicely so no point in re-inventing the wheel. I don't believe there is any way of doing it without a loop natively in VBA.