Replace Specific Columns with Text Variable-CodePudding

i have a list which is imported, all Datablocks are in A underneath. Every block starts with Block=1 then Block=2 and so on. For the Export in needs to be replaced to [Block1] for the whole document. so code should be like:

The imported Data is like this: and after the Import it looks like this:

Only the first row must be changed.

THE FOLLOWING CODE ISNT WORKING AT ALL; ITS JUST MY IDEA HOW IT WOULD WORK BUT I DONT KNOW THE NEEDED COMANDS:

Sub Replace()

Dim XC As Range

For Each XC In Worksheets("Export").UsedRange

If XC.Text = "Block=X" Then
  XC.Replace to [Block1] 
 ' But it needs to be in the next Block: [Block2] and so on.

End If


End Sub

Thanks if anybody can help.

I don't know how to please with a variable.

CodePudding user response：

Add a counter inside your loop. Initialize as 1 outside and then increase it. Something like this may work:

Dim XC As Range
Dim i As Long

i = 1
For Each XC In Worksheets("Export").UsedRange
    If XC.Text = "Block=X" Then
        XC.Replace "Block=X", "Block=" & i
        i = i   1
    End If
Next XC

CodePudding user response：

This would give you the exact expected output, based on your example input.

Sub test()
    'this is built on the assumption the table is top left of the sheet
    Dim no_of_cols as Long, last_row as Long, output_row as Long, rw as Long, cl as Long
    With ActiveSheet
        no_of_cols = .Cells(1, .Columns.Count).End(xlToLeft).Column
        last_row = .Cells(.Rows.Count, "A").End(xlUp).Row
        output_row = last_row   2
        For rw = 2 To last_row
            For cl = 1 To no_of_cols
                .Cells(output_row, 1) = .Cells(1, cl).Value & "=" & .Cells(rw, cl).Value
                output_row = output_row   1
            Next
            output_row = output_row   1 ' inserts blank line
        Next
    End With
End Sub