Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
If anyone can help me with this problem I'd appreciate it. I'm trying to make what I'd assume is a simple excel macro but I canno find any useful FAQs or website with actual useful help on making exce macros. Basically I want to select a column, let's call it column G, and I wan the macro to check every field in that column from say 5 to 100 and i there's a 1 in that field, then get the information in column C in th same row, and put it into the windows clipboard so I can paste i later. I'm using Selection.Cells(x,1).value in a loop and incrementing x t find the value in the selected column, but how do I then select colum C in the same row if it's a 1? Also I'm unsure how to add thi information to windows clipboard. I want to be able to ctrl-V once I'v run the macro and simple paste the list of information that wa retrieved. If anyone can help me with this or simply point me to a useful onlin resource I'd appreciate it -- madbunn ----------------------------------------------------------------------- madbunny's Profile: http://www.excelforum.com/member.php...fo&userid=3254 View this thread: http://www.excelforum.com/showthread.php?threadid=52325 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
as far as I know you cannot copy multiple cells to the clipboard. A
better solution would be to copy the cells you choose to a new column. Sub Copy() Dim x As Single, y As Single x = 1 y = 1 Do While Range("G" & x).Value < "" If Range("G" & x).Value = "1" Then Range("C" & x).Select Range("C" & x).Copy Range("H" & y).PasteSpecial y = y + 1 End If x = x + 1 Loop End Sub You can replace the Range("H") for wherever you would like the data to be sent to...I dont know if this helps but it does work! -Justin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
sub addtoclipboard()
Dim rng as Range for each cell in Range("G5:G100") if cell.value = 1 then if rng is nothing then set rng = cell else set rng = union(rng,cell) end if end if Next if not rng is nothing then set rng = Intersect(rng.entireRow,Columns(3)) rng.select rng.copy else msgbox "Nothing to copy" end if End Sub Just remember that there are many actions that will clear the clipboard -- Regards, Tom Ogilvy "madbunny" wrote: If anyone can help me with this problem I'd appreciate it. I'm trying to make what I'd assume is a simple excel macro but I cannot find any useful FAQs or website with actual useful help on making excel macros. Basically I want to select a column, let's call it column G, and I want the macro to check every field in that column from say 5 to 100 and if there's a 1 in that field, then get the information in column C in the same row, and put it into the windows clipboard so I can paste it later. I'm using Selection.Cells(x,1).value in a loop and incrementing x to find the value in the selected column, but how do I then select column C in the same row if it's a 1? Also I'm unsure how to add this information to windows clipboard. I want to be able to ctrl-V once I've run the macro and simple paste the list of information that was retrieved. If anyone can help me with this or simply point me to a useful online resource I'd appreciate it. -- madbunny ------------------------------------------------------------------------ madbunny's Profile: http://www.excelforum.com/member.php...o&userid=32541 View this thread: http://www.excelforum.com/showthread...hreadid=523253 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
Tom I appreciate the help but Justin was right, although that select only the individual fields in Column C, when I copy and paste it copy the entire column. Justin that macro you posted does pretty much what I wanted, I can jus copy column H (which I changed to B) But I have another question. Can change: Do While Range("G" & x).Value < "" If Range("G" & x).Value = "1" to use the column I currently have selected instead of manually havin to change G to a different column? there's about 50 different columns have to run this macro for -- madbunn ----------------------------------------------------------------------- madbunny's Profile: http://www.excelforum.com/member.php...fo&userid=3254 View this thread: http://www.excelforum.com/showthread.php?threadid=52325 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
try this:
Sub Copy() Dim x, y, c As Single x = 1 y = 1 c = ActiveCell.Column Do While Cells(x, c).Value < "" If Cells(x, c).Value = "1" Then Range("C" & x).Select Range("C" & x).Copy Range("B" & y).PasteSpecial y = y + 1 End If x = x + 1 Loop End Sub so will you be running this macro fifty times? you could have it run through all fifty columns and paste everything into a new sheet. I dont know what your needs are. HTH -Justin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
What I provided does what you ask.
If you wanted to replace the 1's in column G with the corresponding value in column C, then that certainly isn't what you stated. -- Regards, Tom Ogilvy "madbunny" wrote in message ... Tom I appreciate the help but Justin was right, although that selects only the individual fields in Column C, when I copy and paste it copys the entire column. Justin that macro you posted does pretty much what I wanted, I can just copy column H (which I changed to B) But I have another question. Can I change: Do While Range("G" & x).Value < "" If Range("G" & x).Value = "1" to use the column I currently have selected instead of manually having to change G to a different column? there's about 50 different columns I have to run this macro for. -- madbunny ------------------------------------------------------------------------ madbunny's Profile: http://www.excelforum.com/member.php...o&userid=32541 View this thread: http://www.excelforum.com/showthread...hreadid=523253 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
Yes Tom's does work. Don't forget to add:
Dim cell as Range You can decide which serves your purposes better. -Justin |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
Thanks for the note. To continue the helping:
Dim x, y, c As Single x is variant y is variant c is single is that what you intended? Most would do Dim x as Long, y as Long, c as Long -- Regards, Tom Ogilvy "Justin Philips" wrote in message oups.com... Yes Tom's does work. Don't forget to add: Dim cell as Range You can decide which serves your purposes better. -Justin |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Excel macro
oh yeah...thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Excel Macro | New Users to Excel | |||
simple excel macro Q | Excel Programming | |||
New Excel user needs help with simple Macro... | New Users to Excel | |||
simple excel vba macro | Excel Programming | |||
Simple Excel Macro - Please Help | Excel Programming |