Changing the col. header

[Guinaba]

Hi guys,

I need to change the col. header from col.G to the last populated col. from first day of the week 01/06/2006 to Week Number/Year as shown below. Not sure why my code is not working. Any suggestion?

Before running the code	1/06/2020	8/06/2020	15/06/2020
After running the code	23/2020	24/2020	25/2020

Dim LastCol As Integer
Dim Header As Variant
Dim i As Integer
'Starting counting from col. G and row 11
LastCol = Sheets("Report").cells(11, Columns.Count).End(xlToLeft).Column
Dim rng As Range: Set rng = Application.Range(cells(11, 7), cells(11, LastCol))

   For i = 1 To rng.Columns.Count
         Header = rng.cells(11, i).Value
         Header = Sheets(1).Application.WorksheetFunction.WeekNum(Header, vbMonday) & "/" & Year(Header)
    Next

 

[Snakehips]

@Guinaba
Try...

For i = 1 To rng.Columns.Count
         Header = rng.cells(1, i).Value. '<<<<<<<<<<<<<<<<<<<<. 1 not 11
         Header = Sheets(1).Application.WorksheetFunction.WeekNum(Header, vbMonday) & "/" & Year(Header)
    Next

 

[Guinaba]

Thanks @Snakehips!! Didn't realize that!

 

[Snakehips]

Thanks @Snakehips!! Didn't realize that!

11 is the row of the the sheet that you are using to set range rng

rng then only has one row. !!!