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

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
205
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
VBA Code:
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
 
Last edited:
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
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
VBA Code:
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
 
Upvote 0
Try this version.

VBA Code:
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
 
Upvote 0
Peter_SSs

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

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
ABCDEFGHI
1Billing NameCaroline Martin ,
2& Address2007 Excel Road, Los Angeles, CA 87457
3SR. NoSAC NOMSRUnit NoFloor TypeCarpet AreaTax After CappingUser CodeHG No

Another Eg
ABCDEFGHI
1Billing Name & AddressCaroline Martin , 2007 Excel Road, Los Angeles, CA 87457
2
3SR. NoSAC NOMSRUnit NoFloor TypeCarpet AreaTax After CappingUser CodeHG No

SamD
133
 
Upvote 0
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. ;)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
VBA Code:
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"
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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