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]

The following assumes 5 columns (A:E) and pastes the copied columns to the next available column after G.

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

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

For Each Tcell In Range("A1:E1")
        If Tcell.Offset(5).Value > Tcell.Offset(2).Value Then
                Tcell.EntireColumn.Copy Mycell
        End If
Next Tcell
        
End Sub

Perhaps you can adapt it.

Mac

 

[new2vba]

thanks... the line:

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

produces an error

 

[Macropheliac]

I neglected to add the Sort Method:

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

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

For Each Tcell In Range("A1:E1")
        If Tcell.Offset(5).Value > Tcell.Offset(2).Value Then
          Tcell.EntireColumn.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

 

[Macropheliac]

thanks... the line:

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

produces an error

This will produce an error if there is no value in G1. Change the range to whatever range you're using.

 

[new2vba]

This will produce an error if there is no value in G1. Change the range to whatever range you're using.

dont quite understand what you mean here...we're assuming columns a to e have data, so f is the first available column... you say it produces an error if there is "nothing" in g - what would be in g?

 

[Macropheliac]

Where would you like to paste the data after it's copied?

 

[new2vba]

Mac...
is there a way i can adjust the copy to location? i figured out the error part - i'd like to copy the data to say a10

 

[Macropheliac]

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

Set Mycell = Range("A10") 

For Each Tcell In Range("A1:E1") 
        If Tcell.Offset(5).Value > Tcell.Offset(2).Value Then 
          Tcell.EntireColumn.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

 

[new2vba]

ahh, the copy entire column gets in the way of pasting below the data...the number of rows is fixed - how would i make the adjustment to copy a fixed number of rows instead of the entire column?