VBA for Excel 2007 different from Excel 2010?

[zakynthos]

Hi,

I've copied and tried the following code and it keeps looping around the
'mainloop' when I step through and check the 'local's window. It seems to run in Excel 2010 in the video I'm watching but I can't determine what's wrong when I run it in Excel 2007. One error I got was 'For without Next' - after 'For mainloop = 0 To UBound(names)' - but even though I input 'Next mainloop', it still doesn't run correctly.

Where is it going wrong?

__________________________________________________________

Sub sortnames()

Dim names(4) As String
Dim mainloop As Integer
Dim compareloop As Integer
Dim temp As String

names(0) = "bob"
names(1) = "BoB"
names(2) = "JOe"
names(3) = "Sally"
names(4) = "fRED"

For mainloop = 0 To UBound(names)

Next

For compareloop = mainloop To UBound(names)
If UCase(names(mainloop)) < UCase(names(compareloop)) Then

temp = names(mainloop)
names(mainloop) = names(compareloop)
names(compareloop) = temp

End If

Next compareloop


End Sub

________________________________________________


Thanks

 

[VoG]

Shouldn't the loops be nested?

Sub sortnames()

Dim names(4) As String
Dim mainloop As Integer
Dim compareloop As Integer
Dim temp As String

names(0) = "bob"
names(1) = "BoB"
names(2) = "JOe"
names(3) = "Sally"
names(4) = "fRED"

For mainloop = 0 To UBound(names)


    For compareloop = mainloop To UBound(names)
        If UCase(names(mainloop)) < UCase(names(compareloop)) Then
        
            temp = names(mainloop)
            names(mainloop) = names(compareloop)
            names(compareloop) = temp
        
        End If
    
    Next compareloop
Next mainloop


End Sub

 

[zakynthos]

Thanks, I don't know, should they?

Can you give me an example of how to 'nest' the loops?

 

[VoG]

Can you give me an example of how to 'nest' the loops?

See my post above!

 

[zakynthos]

Sorry, I didn't see the amendment you made before, but have just tried it and now get a 'Next without For' error when stepping through, so I guess nesting doesn't work!

 

[GTO]

I just ran Peter's code; it is without flaw.

 

[zakynthos]

Thanks, so how come I get an error message when i run the identical code - could I have a setting somewhere in options etc that is causing error messages?

 

[zakynthos]

Correction,very sorry!!

I've tried copying and pasting this over mine and it now runs perfectly.

Many thanks for your advice

 

[zakynthos]

One last question - if I want this to run on the names listed say, in Column A what do I need to add to the code to get it to do the sort - I've tried 'Columns("A:A").Select' but while that selects Column A, the names don't get sorted.

Thanks

 

[VoG]

Maybe like this

Sub sortnames()

Dim names() As String
Dim mainloop As Integer
Dim compareloop As Integer
Dim temp As String
Dim LR As Long, i As Long

LR = Range("A" & Rows.Count).End(xlUp).Row
ReDim names(0 To LR - 1)
For i = LBound(names) To UBound(names)
    names(i) = Range("A" & i + 1).Value
Next i

For mainloop = 0 To UBound(names)


    For compareloop = mainloop To UBound(names)
        If UCase(names(mainloop)) < UCase(names(compareloop)) Then
        
            temp = names(mainloop)
            names(mainloop) = names(compareloop)
            names(compareloop) = temp
        
        End If
    
    Next compareloop
Next mainloop
For i = LBound(names) To UBound(names)
    Range("A" & i + 1).Value = names(i)
Next i


End Sub