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]

I just looked at your file. Apparantly you missed the changes I made. Delete the code you have and paste this one. It worked fine in your workbook.

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:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
        
    End If 
Next Tcell 

End Sub

Should work.

Mac

 

[new2vba]

done for the day...thanks again for your help

 

[new2vba]

i dont know how i missed your changes , but i think this is working. thanks again - have a great night

 

[Macropheliac]

One more thing. If you want to maintain your formatting use this one:

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.PasteSpecial xlPasteValues
        Mycell.Offset(1).Resize(6).Sort Key1:=Mycell.Offset(1), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
    End If
Next Tcell

End Sub