CODEWITHSUNDEEP
excelvbaexcel-2007
BlackCat
BlackCat

Reputation: 2044

How to find values for 1st cell of same row for a target in Excel VBA?

To Retrieve target cells Column Header and Row Header(In this case, it is the 1st value of the row) I am using below code in VBA:-

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim ColumnHeader As String
    Dim RowHeader As String

' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:G106")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."
        ColumnHeader = Cells(1, Target.Column)
        RowHeader = Cells(1, Target.Row)
        MsgBox ColumnHeader
        MsgBox RowHeader

End If
End Sub

I am getting value for ColumnHeader only. Am I missing something while retrieving RowHeader Value?

Example:

enter image description here

If I change $C3, it should get values 'LINE2' and '222' respectively

Upvotes: 1

Views: 1046

Answers (1)

Naresh
Naresh

Reputation: 3034

Try this..

replace RowHeader = Cells(1, Target.Row) with RowHeader = Cells(Target.Row,1) ... Cells are referred to as Cells(RowNumber,ColumnsNumber)

Upvotes: 2

Related Questions