Add an increasing Number to each instance of a String

[AGALLEGOS]

I have a project that imports several text files into a spreadsheet, and I am in need of a way to identify the beginning lines of each file. Doing this will allow me to separate the bunch into separate columns later on.

So, Each file begins with the word "Sample" and I would like to change Sample to Title1, while increasing the number next to Title by 1 in each instance.

I would like to use VBA to scan through Column A and change each "Sample" to "Title1", next instance of Sample changes to Title2 and so on.

Sample

data

END

Sample

data

END

Sample

data

END

<tbody>
</tbody>

the end result would look like this

Title1

data

End

Title2

data

End

Title3

data

End

<tbody>
</tbody>

I have experimented with some loops and different methods, but I cannot seem to nail this one down.

any help or direction would be greatly appreciated.

--A

 

[Domenic]

Is the data consistent? In other words, does the text "Sample" occur every 3rd cell? Or does it vary?

 

[AGALLEGOS]

It varies

 

[thatoneguy650]

Sub mrExcelTitle()
Dim iterator As Integer
Dim LastRow As Integer
LastRow = Range("A1").End(xlDown).Row
iterator = 1
For i = 1 To LastRow
    If Cells(i, 1).Value = "Sample" Then
        Cells(i, 1).Value = "Title" & iterator
        iterator = iterator + 1
    End If
Next
End Sub

This works for me

 

[Domenic]

The following macro assumes the following...

1) The sheet containing the data is the active sheet.

2) Column A contains the data.

3) The data starts at Row 2.

4) The find/replace doesn't need to be case-sensitive.

Option Explicit

Sub AddTitles()

    Dim Cnt         As Long
    Dim LastRow     As Long
    Dim i           As Long
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Cnt = 0
    For i = 2 To LastRow
        If UCase(Cells(i, "A").Value) = "SAMPLE" Then
            Cnt = Cnt + 1
            Cells(i, "A").Value = "Title" & Cnt
        End If
    Next I

    MsgBox "Completed...", vbExclamation
    
End Sub

 

[Rick Rothstein]

Here is another macro to consider...

Sub SampleToNumberedTitle()
  Dim LastRow As Long
  If Range("A1").Value = "Sample" Then Range("A1").Value = "Title1"
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Replace "Sample", "#N/A", xlWhole
    .SpecialCells(xlConstants, xlErrors).FormulaR1C1 = "=""Title""&COUNTIF(R1C:R[-1]C,""Title*"")+1"
    .Value = .Value
  End With
End Sub

 

[AGALLEGOS]

Works like a charm. I was unable to figure it out previously because I was testing it with a different value instead of Sample. This was causing an error. But I have resolved it thanks to your help.

A