CODEWITHSUNDEEP
excelvba
Ash
Ash

Reputation: 49

check for range of cells & append a cell with the data from corresponding col

check for range of cells[say C1:E5], if cell contains a value find its row [say row-4] append a cell [say B6] with cell's data and its corresponding rows data.

enter image description here

Upvotes: 0

Views: 50

Answers (2)

Mrig
Mrig

Reputation: 11702

Sub Demo()
    Dim str As String

    str = ""

    For Each cel In Range("C1:E5")
        If Not IsEmpty(cel) Then
            If str = "" Then
                str = Range("B" & cel.Row) & "=" & cel.Value
            Else
                str = str & ", " & Range("B" & cel.Row) & "=" & cel.Value
            End If
        End If
    Next

    If str <> "" Then Range("B6") = str
End Sub

Upvotes: 1

Harley B
Harley B

Reputation: 569

Sub test()

    Dim rng As Range
    Dim tmp As String

    Set rng = Range("C1:E5")

    For Each cell In rng
        If cell.Value <> "" Then
            tmp = tmp + " " + Cells(cell.Row, 2).Value + " = " + cell.Value
        End If
    Next cell

    Range("B6").Value = tmp

End Sub

Upvotes: 1

Related Questions