Simple Excel macro

[madbunny]

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

 

[Justin Philips]

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

 

[Guest]

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

 

[madbunny]

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

 

[Justin Philips]

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

 

[Tom Ogilvy]

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.

 

[Justin Philips]

Yes Tom's does work. Don't forget to add:

Dim cell as Range

You can decide which serves your purposes better.

-Justin

 

[Tom Ogilvy]

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

 

[Justin Philips]

oh yeah...thanks!