How to return the first cell in a row?

[elmnas]

Hi guys

I use this code but I still dont get the first cell in the same column and row 1



see code below:


Sub Langauge_Combination()


For Each sht In ActiveWorkbook.Worksheets
Set Rng = sht.UsedRange

Set MyRange = Rng
For Each MyCol In MyRange.Columns
For Each MyCell In MyCol.Cells
'MsgBox ("Address: " & MyCell.Address & Chr(10) & "Value: " & MyCell.Value)

If MyCell.Interior.ColorIndex = 23 Then

'MsgBox MyCell.Text








' Put code here
'I tried to use this code but I dont get the first cell on the first row with this

MsgBox "" & MyCell.End(xlUp).Text 'I DONT GET THE FIRST ROW WHY?


End If
Next
Next
Next

End Sub

Could someone help me?
Thank you in advance

 

[RickXL]

Hi, I am not quite sure which first row you need so I have done both . See the Debug.Print statement.

Sub Langauge_Combination()

    For Each sht In ActiveWorkbook.Worksheets
        Set Rng = sht.UsedRange
        
        Set MyRange = Rng
        For Each MyCol In MyRange.Columns
            For Each MyCell In MyCol.Cells

                 If MyCell.Interior.ColorIndex = 23 Then
        
                     ' Put code here
                     'I tried to use this code but I dont get the first cell on the first row with this
                     Debug.Print "Address1: " & MyCol.Cells(1, 1).Address & " Address2: " & sht.Cells(1, MyCol.Column).Address
                     'Debug.Print "" & MyCell.End(xlUp).Text 'I DONT GET THE FIRST ROW WHY?
                     
                 End If
            Next
        Next
    Next

End Sub

 

[elmnas]

I want the first row in the sheet, (its a list item though. but it's still got only one word in the box)

How do I use it?

as MsgBox I Can't make it work.

 

[elmnas]

Hi, I am not quite sure which first row you need so I have done both . See the Debug.Print statement.

Sub Langauge_Combination()

    For Each sht In ActiveWorkbook.Worksheets
        Set Rng = sht.UsedRange
        
        Set MyRange = Rng
        For Each MyCol In MyRange.Columns
            For Each MyCell In MyCol.Cells

                 If MyCell.Interior.ColorIndex = 23 Then
        
                     ' Put code here
                     'I tried to use this code but I dont get the first cell on the first row with this
                     Debug.Print "Address1: " & MyCol.Cells(1, 1).Address & " Address2: " & sht.Cells(1, MyCol.Column).Address
                     'Debug.Print "" & MyCell.End(xlUp).Text 'I DONT GET THE FIRST ROW WHY?
                     
                 End If
            Next
        Next
    Next

End Sub

here is a picture

of the example doc

 

[RickXL]

Looking at your picture, are you saying that you want to find the text in column 1 of the row that has the blue cell in it.

So from your example, the For Next loops step to where myCell is C3 and you want the text from cell B3.

Try this. It finds the first column of myRange and it knows the row of myCell so it just has to look it up:

Sub Langauge_Combination()

    For Each sht In ActiveWorkbook.Worksheets
        Set Rng = sht.UsedRange
        
        Set MyRange = Rng
        For Each MyCol In MyRange.Columns
            For Each MyCell In MyCol.Cells

                If MyCell.Interior.ColorIndex = 23 Then
        
                     ' Put code here
                     'I tried to use this code but I dont get the first cell on the first row with this

                    MsgBox "" & sht.Cells(MyCell.Row, MyRange.Columns(1).Column).Text      'I DONT GET THE FIRST ROW WHY?
                     
                 End If
            Next
        Next
    Next

End Sub

 

[elmnas]

Solved thanks.