0

I'm a VBA newbie, but feel that it can offer me some great functionality within Excel, without having to resort to mega complicated formulae.

I have a table, and within that table I identify where a combination of the column and header exists.

What I want to do is concatenate each column and row header into a single column.

Example (There could be any number of column/row headings):

      10CAT  20CAT  30CAT
EAR      x      x
CAR             x      x
GBR      x      x      x

Where I have an 'x' I want to concatenate into the single column, as shown below:

10CATEAR
20CATEAR
20CATCAR
30CATCAR
10CATGBR
20CATGBR
30CATGBR
Community
  • 1
  • 1
S8Tony
  • 85
  • 1
  • 1
  • 9
  • If you know the logic then I would recommend, writing the code and then showing us what have you tried and where are you stuck. And then we can take it from there? – Siddharth Rout Sep 16 '14 at 15:05
  • This, I guess, is where I have the problem Siddharth, I'm not sure where to start – S8Tony Sep 16 '14 at 15:06
  • 1
    I am sorry Tony but I doubt anyone would write the complete code for you unless someone is looking for a quick way to make some points :D. My recommendation is to read online/books/record macros on how VBA works :) Anyways this should get you started. Logic: `1` Loop through rows `2` Loop through columns `3` Check if cell has "X" – Siddharth Rout Sep 16 '14 at 15:08
  • Thanks pnuts, that I can do easily, but I'm trying to automate the process a little more, as I want to move each value into a single column, without having to copy, paste, and then amend the formula to account for the repetition of row references – S8Tony Sep 16 '14 at 16:14
  • Except that I want to move everything into a single column. So in this instance, it would have to be amended to =IF(C2="x",$B$2&A2","") in order to ensure that the column header is concatenated with the appropriate row header. In addition, I would have to copy this, and amend it, to accommodate the 2nd, and 3rd columns to select the appropriate column headings for them. – S8Tony Sep 16 '14 at 21:40

1 Answers1

0

"I'm not sure where to start" I know how you feel. I've been there. Here is the code for what you're looking for:

Option Explicit

Sub myMacro()

    ' offsets used to find X
    Dim rOffset As Integer, cOffset As Integer
        rOffset = 0
        cOffset = 0

    ' row numbers for the source and destination
    Dim rowDest As Integer, rowSour As Integer
        rowDest = 1
        rowSour = 2

    ' loop through all entries in column A
    Do While Range("A" & rowSour).Value <> ""

        ' concatenation proccess when X is found
        If Range("B2").offset(rOffset, cOffset).Value = "X" Then

            ' concatenation proccess
            Range("T" & rowDest).Value = Range("B1").offset(0, cOffset).Value + Range("A" & rowSour).Value

            ' next concatenation
            rowDest = rowDest + 1

        End If

        ' next column for X
        cOffset = cOffset + 1

        ' when looped through all columns, prepare variables for the next run
        If Range("B1").offset(0, cOffset).Value = "" Then

            ' next row in column A
            rowSour = rowSour + 1

            ' next row offset for X
            rOffset = rOffset + 1

            ' reset column offset for X
            cOffset = 0

        End If

    Loop

End Sub

Try to step through the program and understand why things are done the way they are. Have a note book with you and for each step right the values of the variables. This program is written in a logical way. No fancy functions or structures are used. It is written as "how you think the procedure should go".

Understand the syntax. THEN, once you figured out the algorithm (how was the solution done), try to make the program more efficient. There are 10s of ways to right a program.

Good luck,

Grendizer
  • 292
  • 1
  • 3
  • 10
  • Thank you. I have done it manually now, but will look through your code, as it will come up again and again :) – S8Tony Sep 16 '14 at 21:41