help with column looping

new2vba

Board Regular
Joined
Aug 15, 2006
Messages
146
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?
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
The following assumes 5 columns (A:E) and pastes the copied columns to the next available column after G.
Code:
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

Board Regular
Joined
Aug 15, 2006
Messages
146
thanks... the line:
Code:
Set Mycell = Range("G1").End(xlToRight).Offset(0, 1)
produces an error
 

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
I neglected to add the Sort Method:
Code:
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

Board Regular
Joined
Aug 26, 2005
Messages
165

ADVERTISEMENT

thanks... the line:
Code:
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

Board Regular
Joined
Aug 15, 2006
Messages
146
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

Board Regular
Joined
Aug 26, 2005
Messages
165

ADVERTISEMENT

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

new2vba

Board Regular
Joined
Aug 15, 2006
Messages
146
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

Board Regular
Joined
Aug 26, 2005
Messages
165
Code:
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

Board Regular
Joined
Aug 15, 2006
Messages
146
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,675
Messages
5,549,363
Members
410,911
Latest member
AniEx
Top