Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading a list of names to update their records | Excel Discussion (Misc queries) | |||
Reading from comma seperated List | Excel Programming | |||
Reading data from a filtered list | Excel Programming | |||
Reading List Box Value | Excel Programming | |||
Reading List Box Value | Excel Programming |