Reading a list
 

I want to create a macro that will read in a range of cells (ex: i2 -
as2)
and then look at each value in the cells. If that value matches a
certain
value I would like it to print out a certain phrase. But if it doesn't
then I
would like it to not do anything and just move on until it finds the
equal
value. Look through a row to find a value and for each case found,
print out
a phrase in column.

Example:
If you have table,
...x y z
a 1 1 1
b 0 0 1
c 1 1 0

abc are names while xyz and certain topics. if a has a 1 value for any
of
the xyz i want it to print out something like:
a.....x
.......y
.......z

b.....z

c.....x
.......y

If I'm not making clear just let me know and I'll try to clarify. Also

remember that I'm new to macros so I might need some additional
guidance.


--
asianmike
------------------------------------------------------------------------
asianmike's Profile: http://www.excelforum.com/member.php...o&userid=30973
View this thread: http://www.excelforum.com/showthread...hreadid=507654

Reading a list
 

Assume B1 contains "X" and A2 contains "A"
Sub ABCD()
Dim rw as Long, cell as Range
rw = 1
for each cell in Range("B2:D4")
if cell.Value = 1 then
cells(rw,"M") = cells(cell.row,"A")
cells(rw,"N") = cells(1,cell.column)
rw = rw + 1
end if
Next
for r = rw to 2 step -1
if cells(r,"M") = cells(r-1,"M") then
cells(r,"M").ClearContents
end if
Next
End if

--
Regards,
Tom Ogilvy

"asianmike" wrote
in message ...

I want to create a macro that will read in a range of cells (ex: i2 -
as2)
and then look at each value in the cells. If that value matches a
certain
value I would like it to print out a certain phrase. But if it doesn't
then I
would like it to not do anything and just move on until it finds the
equal
value. Look through a row to find a value and for each case found,
print out
a phrase in column.

Example:
If you have table,
..x y z
a 1 1 1
b 0 0 1
c 1 1 0

abc are names while xyz and certain topics. if a has a 1 value for any
of
the xyz i want it to print out something like:
a.....x
......y
......z

b.....z

c.....x
......y

If I'm not making clear just let me know and I'll try to clarify. Also

remember that I'm new to macros so I might need some additional
guidance.


--
asianmike
------------------------------------------------------------------------
asianmike's Profile:
http://www.excelforum.com/member.php...o&userid=30973
View this thread: http://www.excelforum.com/showthread...hreadid=507654

Reading a list
 

So I create a new macro. Now what? I run it and it doesn't seem to d
anything. Suppose I wanted to input from sheet1 and then output o
sheet2?
Sorry, really new to this

--
asianmik
-----------------------------------------------------------------------
asianmike's Profile: http://www.excelforum.com/member.php...fo&userid=3097
View this thread: http://www.excelforum.com/showthread.php?threadid=50765

Reading a list
 

I put your sample data in a worksheet. To orient, X was in B1 and A was in
A2.

I ran the macro and it produced the results you show starting in column M of
the same page. I didn't put a blank line between rows, but I will add that.

If you want them to appear starting in A1 of Sheet2 with your data as above
on sheet1

Sub ABCD2()
Dim rw As Long, cell As Range
Dim sh1 As Worksheet
Dim sh2 As Worksheet
rw = 1

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
For Each cell In sh1.Range("B2:D4")
If cell.Value = 1 Then
sh2.Cells(rw, "A") = sh1.Cells(cell.Row, "A")
sh2.Cells(rw, "B") = sh1.Cells(1, cell.Column)
rw = rw + 1
End If
Next
For r = rw To 2 Step -1
If Not IsEmpty(sh2.Cells(r + 1, "A")) Then
sh2.Cells(r + 1, "A").Resize(1, 2).Insert _
Shift:=xlShiftDown
End If
If sh2.Cells(r, "A") = sh2.Cells(r - 1, "A") Then
sh2.Cells(r, "A").ClearContents
End If

Next
End Sub

--
Regards,
Tom Ogilvy


"asianmike" wrote
in message ...

So I create a new macro. Now what? I run it and it doesn't seem to do
anything. Suppose I wanted to input from sheet1 and then output on
sheet2?
Sorry, really new to this.


--
asianmike
------------------------------------------------------------------------
asianmike's Profile:
http://www.excelforum.com/member.php...o&userid=30973
View this thread: http://www.excelforum.com/showthread...hreadid=507654

Reading a list
 

This is perfect. Exactly what I needed, thanks for all the help! One
last trivial question though. Say for instance I had my X, Y, Z in
different color boxes. Is there anyway to keep the colors when the new
list is made. This is merely for aesthetic purposes. Thanks again for
all your help! :)


--
asianmike
------------------------------------------------------------------------
asianmike's Profile: http://www.excelforum.com/member.php...o&userid=30973
View this thread: http://www.excelforum.com/showthread...hreadid=507654