help with column looping

[new2vba]

i have multiple columns with a name followed by five #'s as such:

Name
#1
#2
#3
#4
#5

i'm want to loop through each column, and when criteria are met (ex:#4>#1), copy that column to another location and sort the numbers. i have a problem thinking in vba code, and i can't figure out how to code this logic:

for each column b to e, if row 5>row2 then....

the copy and sort which follows i think i got. Any ideas?

 

[Macropheliac]

ahh, the copy entire column gets in the way of pasting below the data...

So change:

.EntireColumn

to:

.Offset(1).Resize (5)

 

[Macropheliac]

I rewrote the code to loop through all columns and copy the ones that match the criteria to row 10. I hope this is what you're looking for:

Sub Compare_Entries()
Dim Tcell As Range
Dim Mycell As Range


For Each Tcell In Range("A1", Range("A1").End(xlToRight))
    If Tcell.Offset(5).Value > Tcell.Offset(2).Value Then
        Set Mycell = Tcell.Offset(9)
        Tcell.Resize(6).Copy Mycell
        Mycell.Offset(1).Resize(6).Sort Key1:=Mycell.Offset(1), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End If
Next Tcell

End Sub

 

[new2vba]

anyways Mac....thanks for the help you gave me. i'll see if i can do anything with it.

 

[new2vba]

Macro...
your rewrite does nothing...however, if i put something in b10, then

Sub Compare_Entries()
Dim Tcell As Range
Dim Mycell As Range

Set Mycell = Range("a10").End(xlToRight).Offset(0, 1)

For Each Tcell In Range("A1:E1")
        If Tcell.Offset(5).Value > Tcell.Offset(2).Value Then
          Tcell.Offset(1).Resize(5).Copy Mycell
          Mycell.Offset(1).Resize(5).Sort Key1:=Mycell.Offset(1), Order1:=xlAscending, Header:=xlGuess, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        End If
Next Tcell

End Sub

this code is starting to look like what i want

 

[new2vba]

you've helped a bunch...let me see if i can decipher your code and play around with it to get what i want. the rewrite you did looks more like what i am trying to do, just need to figure out why it isnt working. thanks again.

 

[new2vba]

figured out the rewrite....now if i can combine that idea with the next avaible column idea from before...

 

[Macropheliac]

Can you tell me exactly where you want to paste the copied data?

 

[new2vba]

ok Macro... here is what i'm using:

Sub Compare_Entries()
Dim Tcell As Range
Dim Mycell As Range


For Each Tcell In Range("a1", Range("a1").End(xlToRight))
    If Tcell.Offset(5).Value > Tcell.Offset(2).Value Then
        Set Mycell = Range("a10")
        Tcell.Resize(6).Copy Mycell
        Mycell.Offset(1).Resize(6).Sort Key1:=Mycell.Offset(1), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End If
Next Tcell

End Sub

now when i add columns - say 50 - and more than one column meets the criteria, i dont want them to write over the previous one in a10. also, i've tried experimenting with criteria by changing Tcel.Offset (#), and have found it isnt working like i want it to. maybe i dont understand what the number represents, or perhaps you didn't get what i am trying to do.

 

[Macropheliac]

Okay, once the data is copied, where, in relation to it's position do you want it to be copied? Do you want it to be copied beneath it's current position? To the next available column beneath the existing data? To the next available column beside the existing data? Somewhere else?

I promise you we will get this worked out. :wink:

Mac

 

[new2vba]

Can you tell me exactly where you want to paste the copied data?

below and left justified. if i have 50 or 60 columns and 2 meet a criteria, i want to see those two and only those 2 (i wont be looking at the columns at the top). next i will have another criteria - maybe 5 columns meet - they would be below the last set and left justified.