Trying to figure out which to use

[Henry]

I am trying to turn data from from crosstab like format into a columnar
format. I am having trouble figuring out whether to use a range property
to select items for copying or some other mechanism.. Here is a description
of the problem: I am given a matrix with a range of entities in columns A
and B, let's call them Widgets, and a range of Entities across the top,
let's call them Properties. At the interesections of the row and columns,
if there is an "X" in the cell that property belongs to the related Widget.

Properties ==> P1 P2 P3 P4 P5 P6 P7 P8 P9
Widgets
widget 1 X X X
widget 2 X X X X
widget 3 X
widget 4 X X
X X

I have to turn this into a columnar table

Widgets Properties
widget1 P1
P3
P5
widget2 P1
P2
P3
P4
ETC.

What I am working on right now is my loop that checks cells for an X in the
intersection and the copies the property
I am not sure how or if to use the range property along with offset to
select the property values. offset is relative, so
unless I keep track of how many rows I am from the top I cant' use that.
The Range property takes a cell argument.
I know the row will remain constant but how to build the cell address....
Can you concatentate within the formula?
Are there more appropriate solutions?

 

[Tom Ogilvy]

Assume properties are in row1, widget names in column A, starting in A3
for each cell in Range("A3:A20")
for i = 2 to 10
if cells(cell.row,i) = "X" then
msgbox cell.value & " has property " & cells(1,i).Value
end if
Next
Next

should give you some ideas.