Create a loop for changing headers

M_Gross

New Member
Joined
May 2, 2018
Messages
12
Hi everyone, hopefully I can explain the complexity of my problem and hope you can give me some advices.
I want to combine the code, I wrote for two different types of data and adopt a similar logic for both, to reduce my number of modules. However, I am struggling in renaming the headers of the data, since the table structure is different, the headers are different and the new names as well.
So there are two data sets, structured in a different manner.
Structure 1:


<tbody>
[TD="colspan: 6"] Header line 1.1

[/TD]
[TD="width: 50%, colspan: 6"] Header line 1.2

[/TD]

[TD="width: 25%, colspan: 3"] Header line 2

[/TD]
[TD="width: 25%, colspan: 3"] Header line 2

[/TD]
[TD="width: 25%, colspan: 3"] Header line 2

[/TD]
[TD="width: 25%, colspan: 3"] Header line 2

[/TD]

[TD="width: 8%"] H.l. 3

[/TD]
[TD="width: 9%"] H.l. 3

[/TD]
[TD="width: 9%"] H.l. 3

[/TD]
[TD="width: 9%"] H.l. 3

[/TD]
[TD="width: 9%"] H.l. 3

[/TD]
[TD="width: 8%"] H.l. 3

[/TD]
[TD="width: 8%"] H.l. 3

[/TD]
[TD="width: 8%"] H.l. 3

[/TD]
[TD="width: 8%"] H.l. 3

[/TD]
[TD="width: 8%"] H.l. 3

[/TD]
[TD="width: 8%"] H.l. 3

[/TD]
[TD="width: 8%"] H.l. 3.

[/TD]

</tbody>
Now I need to rename header line 1 and find this cell by the name of the header. Secondly, I need to rename a part of the names in header line 3.
I have a code, working for that Problem:

Code:
‘NameRange(r, k)=array("…0… ","…1…","…2…")
NameRange(0, 1) = Array("Line Number", "Old Header in line 1.1", "New Header in line 1.1") NameRange(0, 2) = Array("Line Number ", " New Header in line 1.1", "Header in line 1.2", "Old Header Part in line 3.1", "New Header Part in line 3.1")
I use arrays to find the headers and make the code more flexible for future adjustments. If I have to ‘change a header in future, I don’t have to do it in the whole code, but only in the array.
Code:
For k = 1 To 2       
            i = LBound(NameRange(r, k))      
            If NameRange(r, k)(0) = "6" Then
                .UsedRange.Replace NameRange(r, k)(1), NameRange(r, k)(2), xlPart

            Else
                .Range(.Cells.Find(What:=NameRange(r, k)(1)).Offset(2, 0), _
                       .Cells.Find(What:=NameRange(r, k)(2)).Offset(2, -1)) = _
                       Application.Substitute(.Range(.Cells.Find(What:=NameRange(r, k)(1)).Offset  
                       (2,0).Cells.Find(What:=NameRange(r,k)(2)).Offset(2,-1)),NameRange(r,k)(3),NameRange(r,k)(4))
End If

Else, I cannot define the range of the area by the real header names, because they are varying. ‘Therefore I find the start of the range by the Header line 1.1 and offset it by (2,0). Then I find the end by the using the the next header in header line 1.2 and offset by (2,-1). After that I replace the part of the headers, I want to replace by a new one.



Structure 2:

<tbody>
[TD="colspan: 9"] Header line 1

[/TD]
[TD="width: 50%, colspan: 7"] Header line 1.2

[/TD]

[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 6%"] H.l. 2

[/TD]
[TD="width: 5%"] H.l. 2

[/TD]
[TD="width: 5%"] H.l. 2

[/TD]
[TD="width: 5%"] H.l. 2

[/TD]
[TD="width: 5%"] H.l. 2

[/TD]
[TD="width: 5%"] H.l. 2

[/TD]
[TD="width: 5%"] H.l. 2

[/TD]
[TD="width: 20%"] H.l. 2

[/TD]

</tbody>
For the second structure I tried to use a similar method like in the second part of structure one. However, I have to apply a loop, since there are a lot of headers to find in line 1 and to change the name of the corresponding header in line 2.
Code:
NameRange(1, 1) = Array("7", "/FY", "/Q1", "/Q2", "/Q3", "/Q4", "-01", "-02", "-03", "-04", "-05", _ 
<dir><dir><dir><dir>"-06", "-07", "-08", "-09", "-10", "-11", "-12")
</dir></dir></dir></dir>For i = 0 to 17
.Range(.Cells.Find(What:=NameRange(r, k)(i)).Offset(1, 0), _
                       .Cells.Find(What:=NameRange(r, k)(i + 1)).Offset(1, -1)) = _
                       Application.Substitute(.Range(.Cells.Find(What:=NameRange(r, k)(i)).Offset(1, 0), _
                                                     .Cells.Find(What:=NameRange(r, k)(i + 1)).Offset(1, -1)), _
                                                      NameRange(r, k)(i).Offset(1, 0), NameRange(r, k)(i) & NameRange,_
<dir><dir><dir><dir><dir><dir>          (r,k)(i).Offset(1, 0))
</dir></dir></dir></dir></dir></dir>Next i
The idea is to use a loop that runs for every i in that array and always takes the header i in line 1 from the array, offset by (1,0) for the beginning of the range. After that searches the next header i+1 offset by (1,-1) for the end of the range. In the next step, we take this range and want to add to every header in header line one, the corresponding header i. This is not working, since I don’t know, how to do that.
So the problems are:

  1. Create a loop that goes through every i.
  2. Find a way to add the corresponding header in i to the header in line 2
  3. Implement this loop into the part above which is for the other data structure.
At first, important for me would be the first two points and if this is working, to solve the third point.

I appreciate every comment, even if you just want to tell me why my idea will not work. I just have to make it as flexible as possible, so not using a cell address
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top