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]

ok, that's what i want...so what happens is the first column that meets the criteria is copied to a10 - that's it. in my test data, the very next column also satisfies, plus another - these two aren't copied

 

[Macropheliac]

Well, I don't know what's going on. Can you either post your worksheet with the HTML maker or send it to me?

 

[new2vba]

sure, ill try later...thanks.

 

[new2vba]

Well, I don't know what's going on.

in stepping through your code:

Range("A1").Offset(10).Value
          
Mycell = Range("A1").Offset(10)
          
Mycell = Range("IV11").End(xlToLeft).Offset(0, 1)

all of these values remain the first name to meet the criteria, even if a susequent name meets the criteria.

 

[new2vba]

sample

columntry.xls
	A	B	C	D	E	F	G	H
1	name1	name2	name3	name4	name5	name6	name7	name8
2	0.61	0.43	0.75	0.77	0.65	0.85	0.67	0.67
3	0.61	0.43	0.74	0.79	0.64	0.84	0.67	0.67
4	0.62	0.44	0.75	0.82	0.65	0.85	0.68	0.68
5	0.6	0.42	0.73	0.81	0.64	0.85	0.67	0.66
6	0.62	0.43	0.74	0.82	0.65	0.85	0.68	0.67
7
Sheet1

[/code]

 

[new2vba]

sample sub

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

after sub run

columntry.xls
	A	B	C	D	E	F	G	H
1	name1	name2	name3	name4	name5	name6	name7	name8
2	0.61	0.43	0.75	0.77	0.65	0.85	0.67	0.67
3	0.61	0.43	0.74	0.79	0.64	0.84	0.67	0.67
4	0.62	0.44	0.75	0.82	0.65	0.85	0.68	0.68
5	0.6	0.42	0.73	0.81	0.64	0.85	0.67	0.66
6	0.62	0.43	0.74	0.82	0.65	0.85	0.68	0.67
7
8
9
10
11	name2
12	0.42
13	0.43
14	0.43
15	0.43
16	0.44
17
Sheet1

 

[new2vba]

Should look like this:

columntry.xls
	A	B	C	D	E	F	G	H
1	name1	name2	name3	name4	name5	name6	name7	name8
2	0.61	0.43	0.75	0.77	0.65	0.85	0.67	0.67
3	0.61	0.43	0.74	0.79	0.64	0.84	0.67	0.67
4	0.62	0.44	0.75	0.82	0.65	0.85	0.68	0.68
5	0.6	0.42	0.73	0.81	0.64	0.85	0.67	0.66
6	0.62	0.43	0.74	0.82	0.65	0.85	0.68	0.67
7
8
9
10
11	name2	name3	name8
12	0.42	0.73	0.66
13	0.43	0.74	0.67
14	0.43	0.74	0.67
15	0.43	0.75	0.67
16	0.44	0.75	0.68
Sheet1

 

[Macropheliac]

That's funny. I used the same code on the same data and the result was:

Book1
	A	B	C	D	E	F	G	H
1	name1	name2	name3	name4	name5	name6	name7	name8
2	0.61	0.43	0.75	0.77	0.65	0.85	0.67	0.67
3	0.61	0.43	0.74	0.79	0.64	0.84	0.67	0.67
4	0.62	0.44	0.75	0.82	0.65	0.85	0.68	0.68
5	0.6	0.42	0.73	0.81	0.64	0.85	0.67	0.66
6	0.62	0.43	0.74	0.82	0.65	0.85	0.68	0.67
7
8
9
10
11	name2	name3	name8
12	0.42	0.73	0.66
13	0.43	0.74	0.67
14	0.43	0.74	0.67
15	0.43	0.75	0.67
16	0.44	0.75	0.68
Sheet1

How, exactly, are you running the code?

 

[new2vba]

i am manually running the macro for now