Replace some header strings in Excel with a count value in a given interval VBA

Question

Here is a minimal example of my Excel sheet:

GOAL: Column 1 and 2 will always be respectively "Number" and "Stand". I'm trying to rename the header in column C to the column left to the "TOTAL-column" (which can be in different positions). The names in the aforementioned range should be a counter, Such that the resulting header in the above example would be: Number, Stand, 1, 2, 3, 4, 5, 6, TOTAL.

ATTEMPT: This is what I got so far, however it isn't very efficient, to manually copy the replacement-line for each column, and it doesn't add a header in the empty columns

Sub Rename()
Dim WS As Worksheet
Dim MatchCase As Boolean
For Each WS In Worksheets
WS.Cells.Replace What:="1,", Replacement:="1", LookAt:=xlPart, MatchCase:=True
WS.Cells.Replace What:="2,", Replacement:="2", LookAt:=xlPart, MatchCase:=True
Next
End Sub

Hooded 0ne · Accepted Answer · 2020-10-27 01:07:14Z

0

I'm curios why you are trying to do this with VBA. You could make the cell a formula that says =column()-2 or =column()-2&",18"

If you really wanted a vba solution, you could use vba to search for the "total" cell, and then make all cells to the left of it the above formula.

answered Oct 27, 2020 at 1:07

Hooded 0ne

9971 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

User123456789 Over a year ago

It's a small part of a larger VBA. The position of the TOTAL column is unknown and the values in column C to H in the above example can have varying number of decimals but is formatted as a string.

Collectives™ on Stack Overflow

Replace some header strings in Excel with a count value in a given interval VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related