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?

 

[new2vba]

Macro, you are a tremendous help. when i get to my other computer later i will post a picture with html maker...don't have it on my work pc

 

[Macropheliac]

I think I understand now. I'll post back in a few minutes.

 

[Macropheliac]

Okay, let's try this again:

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
        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]

this puts it in column a, however, the next column to meet the criteria must go in column b and etc.... i will have btw 50 and 60 columns, and a few will meet each criteria i have. you are on the right track though

 

[Macropheliac]

In my test, all columns are pasted next to each other. Does more than one column in your test match the criteria?

 

[new2vba]

yes i believe so...im not sure if maybe i misunderstood the cell references here:

If Tcell.Offset(5).Value > Tcell.Offset(2).Value

...i tried changing the (#)'s and ">" and still only one column appears. maybe you could explain the reference to see if i am on the same page.

 

[Macropheliac]

Sure!

Tcell.Offset(5,0).Value

In the previous line of code:

Tcell: Cell you are testing (Defined earlier in the code with Dim Statement)

.offset(5,0):Offset=Tells Excel to look at cell away from cell. 5=Number of Rows below the test cell. 0=Number of Columns to the right of the test cell.(Since we are not dealing with rows, we can omit the ,0.)

Therefore, if Tcell is Range C5, then Tcell.offset(5) is range C10 and Tcell.Offset(5,3) is Range F8.

I hope I have explained well enough. I've never tried to explain this before. If not, I will try again.

Mac

 

[new2vba]

i think i got it...and this may be where the problem lies...the 5 rows of numbers are in a static order, and i want to compare specific number relationships...it sounds like in your code, each number is looked at and then 2 numbers at a certain offset are compared. does this make sense?
if i have 50 columns of:
name
#1
#2
#3
#4
#5

i want to go through each column and see if #4>#3 for ex. i don't want to make relative comparisons to each cell, but fixed comparisons btw 2 numbers in each column.

 

[new2vba]

another way to put it: are you looping through each cell of each column? i am wanting to loop through each column and compare two specific numbers in each column.

 

[Macropheliac]

Okay:



Your data is formatted this way:
1
2
3
4
5

The following part of the code in question tests whether #5 is grater than #2 in each column. If it is, all of the data (Header, #1, #2, #3, #4, #5) are copied and pasted to the first available column beginning with Row 11.

If Tcell.Offset(5).Value > Tcell.Offset(2).Value Then

To change which numbers are tested, change the Offset part of the code.

For example, to test whether #4 is greater than #3, change to this:

If Tcell.Offset(4).Value > Tcell.Offset(3).Value Then

Does this help?

BTW: This code will test each column provided there are no blank columns between data. If there are blank columns, we have a new problem.