Copy cell text in a range until specific text

[IreneFoncillas]

Hi all,

I am not able to write a vba code for a specific action I would like to do in excel. I attach two screenshots to explain myself better with an example.

Screenshot_1: I want to write text "Level_1" in column A, until in column B the text "Level_2" is found. And then, do the same but with text "Level_2" until text "Level_3" is found. In this example, there are 5 rows between levels, but sometimes there are more or less rows, so it is not a fixed number.

Screenshot_2: This is the result I want to get, once macro has been run.

Thank you very much!!

 

[Fluff]

Are all the cells between the Levels numbers?

 

[GWteB]

Something like this perhaps

Sub DoLevel()

    Dim rng     As Range
    Dim c       As Range
    Dim sLev    As String
    
    With ThisWorkbook.Sheets("Sheet1")
        Set rng = Application.Intersect(.UsedRange, .Columns(2))
    End With

    For Each c In rng
        If StrComp(Left(c.Value2, 5), "level", vbTextCompare) = 0 Then
            sLev = c.Value2
            c.Offset(0, -1).Value = ""
            c.Offset(1, -1).Value = c.Value
        Else
            c.Offset(1, -1).Value = sLev
        End If
    Next c
End Sub

 

[Fluff]

If the values between the levels are numbers, you could also use

Sub IreneFoncillas()
   Dim Rng As Range
   For Each Rng In Range("B1", Range("B" & Rows.Count)).SpecialCells(xlConstants, xlNumbers).Areas
      Rng.Offset(, -1).Value = Rng.Offset(-1).Resize(1)
   Next Rng
End Sub

 

[IreneFoncillas]

Thank you both for your responses. I have changed some text in the code you provided to match my data, and they work!!

 

[Fluff]

Glad we could help & thanks for the feedback.