Column names with Column Nos not able to display in Msg Box from different worksheets

[SamDsouza]

Hello

I am trying to get values(Names) of a single row and with its respective columns from different worksheets
I am not able to get part of msg in Msg box as it only displays that part of Last sheet ie data of first few sheets with names and column numbers and not able to display but displays the same in last sheet

Private Sub CommandButton1_Click()

Dim wks As Worksheet
Dim cl As Range, msg As String, RowNumber as Long
Dim FirstColNumberSngRow As Integer, ColValStr  As String, j As Integer

For Each wks In Worksheets
RowNumber = 3

Set cl = wks.Cells.Find(What:="*", _
                    After:=Cells(RowNumber, Columns.Count), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)

FirstColNumberSngRow = Val(cl.Columns(1).Column)

msg = "Name of Sheet: " & wks.Name & "   " & vbCrLf
j = -1
For Each x In wks.Rows(RowNumber).Cells
    If x.Value = "" Then Exit For
    j = j + 1
    ReDim Preserve heading(j) As String
    heading(j) = x.Value & " " & x.Column & vbCrLf
    ColValStr = ColValStr & heading(j)
 
Next x
msg = msg & ColValStr
MsgBox msg

Next wks

End Sub

Regds
SamD
124

 

[SamDsouza]

Peter_SSs

Thank you so much for the beautiful Explanation.
Yes. Realised the wrong logic of using ForEachNext as per the requirement.

Thanks Once Again

SamD
130

 

[Peter_SSs]

Cheers.

 

[SamDsouza]

Hello
Somehow i really missed the idea of merged cells in the header row. With the below code repeated (As per post#8). Approx. it helped me to check 35 odd worksheets with what i desired to display header names with column nos. suddenly one Worksheet appeared with header row with merged cells of 4 columns and another sheet with blank cells in header row after each header name.

Case 1 : Found there were merged cells in header Row which gave me its address but next column after the merged cells in header row with header name was not displayed.
Case 2: There were blank cells after each header name in the header row so naturally it did not display the header names of other column because of this syntax
If wks.Cells(RowNumber, x).Value = "" Then Exit For

Can anyone help me to correct the below code with above two issues. code below is repeated for your reference

Private Sub CommandButton1_Click()
  Dim wks As Worksheet
  Dim cl As Long, RowNumber As Long, x As Long
  Dim ColValStr As String, msg As String

  RowNumber = 3

  For Each wks In Worksheets
    ColValStr = ""
    msg = "Name of Sheet: " & wks.Name
 
    cl = wks.Rows(RowNumber).Find(What:="*", _
                        After:=wks.Cells(RowNumber, Columns.Count), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False).Column
 
    For x = cl To Columns.Count
      If wks.Cells(RowNumber, x).Value = "" Then Exit For
      ColValStr = ColValStr & vbCrLf & wks.Cells(RowNumber, x).Value & " " & x
    Next x
    MsgBox msg & ColValStr
  Next wks
End Sub

Your guidance will be appreciated
SamD
132

 

[Peter_SSs]

Try this version.

Private Sub CommandButton1_Click()
  Dim wks As Worksheet
  Dim cl As Long, RowNumber As Long, x As Long, lastcol As Long
  Dim ColValStr As String, msg As String
  
  RowNumber = 3
  
  For Each wks In Worksheets
    ColValStr = ""
    msg = "Name of Sheet: " & wks.Name
    
    cl = wks.Rows(RowNumber).Find(What:="*", _
                        After:=wks.Cells(RowNumber, Columns.Count), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False).Column
    lastcol = wks.Cells(RowNumber, Columns.Count).End(xlToLeft).Column
    For x = cl To lastcol
      If wks.Cells(RowNumber, x).Value <> "" Then ColValStr = ColValStr & vbCrLf & wks.Cells(RowNumber, x).Value & " " & x
    Next x
    MsgBox msg & ColValStr
  Next wks
End Sub

 

[SamDsouza]

Peter_SSs

I thought i had to incorporate syntax of mergeArea but you have resolved without implementing it. Thankx. Amazing.

Just a query what if there are contents in cells and empty Cells or range above HeaderRow. Will your code as per# 14 still execute or something needs to be changed.
See below
Header Row = 3 only

	A	B	C	D	E	F	G	H	I
1	Billing Name	Caroline Martin ,
2	& Address	2007 Excel Road, Los Angeles, CA 87457
3	SR. No	SAC NO	MSR	Unit No	Floor Type	Carpet Area	Tax After Capping	User Code	HG No

Another Eg

	A	B	C	D	E	F	G	H	I
1	Billing Name & Address	Caroline Martin , 2007 Excel Road, Los Angeles, CA 87457
2
3	SR. No	SAC NO	MSR	Unit No	Floor Type	Carpet Area	Tax After Capping	User Code	HG No

SamD
133

 

[Peter_SSs]

what if there are contents in cells and empty Cells or range above HeaderRow. Will your code as per# 14 still execute or something needs to be changed.

Try it. Then you tell me if it meets your requirements or not.

 

[SamDsouza]

Sorry for late reply . I checked it . Yes it works as of now. Thanks

Only questions remains if the header row position is changed in all different worksheets. In this case is the row is 3 what if in different sheets changes the row may be
10, 7, 15, 5 etc.

Thanks
SamD
134

 

[Peter_SSs]

Only questions remains if the header row position is changed in all different worksheets. In this case is the row is 3 what if in different sheets changes the row may be
10, 7, 15, 5 etc.

Then either ..
a) There needs to be something on each sheet that would allow the code to identify which row is the header row, or
b) You would need to feed that information into the macro as you did with the "3".

Examples of a) might be
A particular heading that occurs on every sheet,
or a word that appears as part of one of the headers in every sheet
or the header row is the the row after the first blank cell in column A on every sheet
etc

 

[SamDsouza]

Then either ..
a) There needs to be something on each sheet that would allow the code to identify which row is the header row, or
b) You would need to feed that information into the macro as you did with the "3".

Examples of a) might be
A particular heading that occurs on every sheet,
or a word that appears as part of one of the headers in every sheet
or the header row is the the row after the first blank cell in column A on every sheet
etc

? The above will work only if above conditions are met

But with more few cases which came accross it became difficult in the sense that Particular heading does not occur on every sheet niether the word re-appears in the header in every sheet nor the header row is first blank cell in column A on every sheet. Then what to do ?

SamD
135

 

[Peter_SSs]

If there is nothing on each sheet that enables automatic detection of the header row, then you would need to either
- put something on each sheet that would allow that or else
- supply the information in the macro like you did with your original "RowNumber = 3"

One way might be like this, listing each sheet name and the row that its headers are on

Dim HeaderInfo(1 To 4) As String

HeaderInfo(1) = "Data/3"
HeaderInfo(2) = "Area South/12"
HeaderInfo(3) = "Area North/2"
HeaderInfo(4) = "Summary/7"