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

Can anyone guide me what went wrong why the values are displaying of Last sheet and why not for first few sheets ?

SamD
125

 

[Peter_SSs]

What happens if you add this?

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

 

[SamDsouza]

Peter_SSs

First of all thank you for the response.
As per your suggestion with Red Marked "wks". I tried but Nothing happens or i get the same result as per #1 and #2

SamD
126

 

[SamDsouza]

Peter SSs

I realised one thing that Row no3 with Column A filled on all the sheets ie A3 then values are displayed for all the sheets. But if the columns in diffrent sheets began from or data filled in F3 or G3 then nothing happens. Why ?

SamD
127

 

[Peter_SSs]

Sorry, I am struggling to understand your description of exactly what the problem is or what you are trying to do.

 

[SamDsouza]

Ok the clarifications are as belows

1. There is the RowNumber 3 as Header Row for this which are in all the Different sheets
2. No matter in different sheets the column starts any where and not necessraily to begin from A3
it can be D3 or E3 or G3 I3, with addtion 5 to 10 or 15 or 20 columns next to each other.
3. So what i want is to get the Header Names of diffrent colums whith its rescpective column Names or numbers
4. So when pressed on command button Each msg for Each sheet it should show the header names and its columns numbers

HTClarifies

SamD
128

 

[Peter_SSs]

HTClarifies

It has helped (I think). Try this

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

 

[SamDsouza]

Peter_SSs

Yes. This is it.

Thank you so much for the wonderful solution. But what made you change the For Next loop and Why my For Each Next did not give the desired result.

SamD
129

 

[Peter_SSs]

Thank you so much for the wonderful solution.

You're welcome!

Why my For Each Next did not give the desired result.

Suppose your sheet looked like this:

Because of this part of your code ..

For Each x In wks.Rows(RowNumber).Cells
    If x.Value = "" Then Exit For

.. says for each cell in row 3, if the cell is empty then exit the loop. Since the first cell in row 3 is empty, the loop is exited and the loop never gets to check any other columns in that sheet.

That is why this happened:

I realised one thing that Row no3 with Column A filled on all the sheets ie A3 then values are displayed for all the sheets. But if the columns in diffrent sheets began from or data filled in F3 or G3 then nothing happens.