Search code examples
excelvbaincrement

How to increment cell values for rows, but to repeat them in columns?


I have a piece of code which works good, but I would like to increment these values by column. Let's say I have 60 cells in columns A-E, so I have ID_1 to ID_60 in col A, B...E. My code works the way B1 is ID_61

Any suggestion how to fix it?

EDIT: code with changed variable names:

Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    
Dim HeaderName As String

HeaderName = Join(Array("Country", "City", "X", "Y"))
      
Dim LastUsedCell As Range

        Set LastUsedCell = Nothing
        Set LastUsedCell = ws.UsedRange.Find("*", , , , xlByRows, xlPrevious)
        
        If Not LastUsedCell Is Nothing Then
        
            Dim HeaderRow As Range
            Set HeaderRow = ws.Range(ws.Cells(1, 1), ws.Cells(1, ws.Cells.Columns.Count).End(xlToLeft))
                     
            Indeks = 0
            
            For Each Header In HeaderRow
                If InStr(1, HeaderName, Header.Value, vbTextCompare) = 0 Then 
                
        Dim RangeToAnonimize As Range
        Set RangeToAnonimize = ws.Range(ws.Cells(2, Header.Column), ws.Cells(LastUsedCell.Row, Header.Column))
   
                    For Each cell In RangeToAnonimize
                        cell.Value2 = "ID_" & Indeks
                        Indeks = Indeks + 1
                    Next cell
                End If
            Next Header
        End If
    Next ws

End Sub

What the code does:

enter image description here

However, the intention is that one row should have the same ID.


Solution

  • Your Indeks = 0 should be just before For Each cell

    Indeks = 0
    For Each cell In RangeToAnonimize