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]

Are you running it exactly as it is posted. You should get the same result as I do.

 

[new2vba]

yeah, i dont get it...

 

[Macropheliac]

I am intrigued. Can you click the E-Mail button at the botton of this post and send me the workbook or a harmless copy of it?

 

[new2vba]

THX

 

[Macropheliac]

You're going to laugh at this one.

Scroll all the way to the right of your workbook. You'll see a lot of data there (Probably from our earlier, failed attempts). Delete this and the code will run fine.

Let me know if it works.

Mac

 

[new2vba]

yeah that figures :x
you've been a great help...im going to play with this for a while and try to add some criteria to what youve written.

thanks,

jon

 

[Macropheliac]

Thanks for letting me know. Post again if you need further assistance.


Thanks,
Mac

 

[new2vba]

mac,

im trying to change the code as such - i want to copy the columns that meet the criteria - side by side - just like we did, but i want it to start copying to column "A" every time it is run. now it keeps adding on from the next available column each time it is run. to explain better - the numbers will keep changing, and different names will meet and unmeet certain criteria. the sub will keep running and update the met lists - copying over what was there previously - make sense?

 

[Macropheliac]

Does this work?

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

Range("A11:IV65536").ClearContents

For Each Tcell In Range("A1", Range("A1").End(xlToRight))
    If Tcell.Offset(5).Value < Tcell.Offset(3).Value Then
        Select Case Range("A1").Offset(10).Value
                Case ""
                    Set Mycell = Range("A1").Offset(10)
                Case Is <> ""
                    Set Mycell = Range("IV11").End(xlToLeft).Offset(0, 1)
        End Select

        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]

yes...man you are good