0

I have a (large) table with row and column headers of the following format: table with row and column headers

I'd like to set the cells marked with 'x' to a concatenation of the column header and the row header, separated by a comma. For example, cell B2 should be set to "c1_HEADER, r1_HEADER".

Is there a formula I can use to achieve this? At least by clicking the 'x' marked cells and applying the formula? I'd hate to take the manual route all the way :/.

TIA.

Shiv
  • 51
  • 7

1 Answers1

1

If we start with:

enter image description here

running this macro:

Sub luxation()
    Dim r As Range

    For Each r In Range("B2").CurrentRegion
        If r.Value = "x" Then
            r.Value = r.EntireColumn.Cells(1).Value & "," & r.EntireRow.Cells(1).Value
        End If
    Next r
End Sub

will produce:

enter image description here

Note:

in this macro Range("B2").CurrentRegion represents the block of cells that we are looping through. The variable r is a single-cell range that facilitates the loop.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Worked like a charm! I've never really used macros before, but I must say this was something beautiful. Thank you, good sir/ma'am. For my further knowledge, however, could you explain the terms: `Dim r As Range`; AND `Range("B2").CurrentRegion` ? – Shiv May 29 '16 at 17:41
  • @Shiv See my ***Note*** – Gary's Student May 29 '16 at 18:10