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]

Hello Peter_SSs
Extremely sorry for late reply. But during this duration as when different sheets were opened header row were different.

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"

RowNumber = 3 and defining header Rows can't be the case always

after going through your suggestion i used the code from FindHeaderRow-By Mielk
In Simplest way I wanted the Header Row ref: and and their respective Header names in textbox.

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

For Each wks In Worksheets
      ColValStr = ""
      msg = "Name of Sheet: " & wks.Name
      RowNumber = Val(findHeaderRow(wks))
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 & vbCrLf & ColValStr & vbCrLf & "Header Row Number: " & RowNumber
Next wks

End Sub

After implementing the above code in UF and coding in a module from the Link following observations were made.
Case1

	A	B	C	D	E	F	G	H	I	J
1	Name	Std	Subject
2
3	Fruits	Vegetables	Amount
4

In Case 1 : Header Row Displayed as No 1 and all values from A – C Displayed

Case2

	A	B	C	D	E	F	G	H	I	J
1	Name	Std	Subject
2
3	Fruits	Vegetables	Amount	Delivery
4

In Case 2 : Header Row Displayed as No 3 and all values from A – D Displayed

Case3

	A	B	C	D	E	F	G	H	I	J
1	Name	Std	Subject
2
3					Fruits	Vegetables	Amount
4

In Case 3 : Header Row Displayed as No 3 and all values from E – G Displayed
Though in Case 1 and Case 3 equal no of columns used Why did it not display Row 1 ?

Case4

	A	B	C	D	E	F	G	H	I	J
1	Name	Std	Subject	Marks
2
3					Fruits	Vegetables	Amount
4

In Case 4 : Header Row Displayed as No 3 and all values from E – G Displayed
Though added one more column of Marks in row 1 Why did it not display Row 1 ?

Case5

	A	B	C	D	E	F	G	H	I	J
1	Name	Std	Subject	Marks	Grade
2
3					Fruits	Vegetables	Amount
4

In Case 5 : also Header Row Displayed as No 3 and all values from E – G Displayed
Though added one more column (E) in row 1 Why did it not display Row 1 ?

Now a major confussion as to how to pre-define the Header Row ?

As per the above observations the header Row is predefined on the basis of Last new column NOT USED added as per the current coding.
ie For eg in CASE 5 if i add value in col H row 3 which is not used then that hedaer row 3 wiil be shown instead of row 1

I’ve decided that it should be on the basis of More Columns of 1 Particular Row anywhere in the worksheet and not on the basis of Last New NOT USED column Added Then what changes are required in Module code or in UF code.

Regards
SamD
136

 

[Peter_SSs]

I'm afraid that you have lost me.

 

[SamDsouza]

I'm afraid that you have lost me.

?
SamD
137