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:
However, the intention is that one row should have the same ID.
Your Indeks = 0
should be just before For Each cell
Indeks = 0
For Each cell In RangeToAnonimize