Name of column Headers

[Eric Penfold]

I need to list the names of my headers I`ve attempted to but no success?
Here is the Header loop. It shows blank?
Many Thanks in advance

    For Each X In Rows(1).Cells
        If X.Value = "" Then Exit For
        Y = Y + 1
        ReDim Preserve heading(i) As String
        heading(Y) = X.Value
    Next X

End With

Sub vLookupAnotherWorkbook()

    Dim Src As Workbook
    Dim Des As Workbook
    Dim SASheet As Worksheet
    Dim PASheet As Worksheet
    Dim MBefore As Integer
    Dim MName As String
    Dim ColNames As Object
    Dim WSArray, i
    ReDim WSArray(1 To Sheets.Count)
    Dim heading() As String, X As Variant, Y as Variant
    Dim LRow As Long

   
    ReDim ColArray(1 To Columns.Count)
  
   
    Dim FileToOpen As Variant
  
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    FileToOpen = ("\\DC01\Company\PURCHASING\Forecasting\Brett Martin Template for Vlookup.xlsm")
    Workbooks.Open FileToOpen
    Set Src = Workbooks("Brett Martin Template for Vlookup.xlsm")
    Set Des = Workbooks("Brett Martin Forecast 2022.xlsm")
    Set SASheet = Src.Sheets("Sales Analysis")
    LRow = SASheet.Range("A1").CurrentRegion.Rows.Count


    MBefore = Format(DateAdd("m", -1, Date), "mm")
  
    MName = MonthName(MBefore)
   
    With Des
   
    For i = 1 To .Sheets.Count
    WSArray(i) = .Sheets(i).Name
    Next
   
    End With
   
   
    With SASheet
   
    For Each X In Rows(1).Cells
        If X.Value = "" Then Exit For
        Y = Y + 1
        ReDim Preserve heading(i) As String
        heading(Y) = x.Value
    Next x
       
    End With

   

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
   
   
'  Workbooks("Src").Close _
'  SaveChanges:=False
 
     End Sub

 

[Fluff]

You need to use

For Each X In .Rows(1).Cells

(notice the . before Rows) otherwise your with statement is doing nothing.

 

[Alex Blakenburg]

I probably wouldn't do it the way you are doing it but in this line shouldn't the "i" be a "Y" ?

        ReDim Preserve heading(i) As String

 

[Eric Penfold]

How would you do it. x is still saying empty?

 

[Fluff]

Are your headers on row 1 of the Sales Analysis sheet?

 

[Eric Penfold]

Yes they are

 

[Alex Blakenburg]

How would you do it. x is still saying empty?

It is probably personal preference but I don't like having a redim preserve inside a loop. I also find it easier to work with a 2 dimensional array.
This might look longer but should be more efficient.

    With SASheet
        heading = .Range(.Cells(1, "A"), .Cells(1, SASheet.Columns.Count).End(xlToLeft))
    End With
   
    Dim hdgTemp As String
    ' handle empty row and single cell heading
    If Not IsArray(heading) Then
        If heading = "" Then
            Exit Sub
        Else
            hdgTemp = heading
            ReDim heading(1 To 1, 1 To 1) As String
            heading(1, 1) = hdgTemp
        End If
    End If

PS:Assuming you want to write out the headings it might look like this

Worksheets("Sheet3").Cells(1, 1).Resize(1, UBound(heading, 2)).Value = heading

 

[Fluff]

In that case it should work, although I would use

    With SASheet
         i = .Cells(1, .Columns.Count).End(xlToLeft).Column
         ReDim heading(1 To i)
         For X = 1 To i
            heading(X) = .Cells(1, X).Value
         Next X
       
    End With

 

[Eric Penfold]

Thanks
This works but i need a array of names rather than figures

With SASheet
i = .Cells(1, .Columns.Count).End(xlToLeft).Column
ReDim heading(1 To i)
For X = 1 To i
heading(X) = .Cells(1, X).Value
Next X

End With

 

[Fluff]

What do you mean, you need names?