sorting a range of cells in the same column by Address

I

Ironhydroxide

i am trying (in a macro) to sort a range of cells, from variables declared as
objects, that are in the same row.
My problem is that as i run the macro, the program thinks that cell $L$56
is larger than cell $L$125. is there any way to get around this?

This is a portion of the code i am using.

'Sort to determine the stop coordinates
replacelist = 0
Set replace = Nothing
Do
If branchAddress1 > branchAddress2 Then
replacelist = branchlist1
replacebranch = branchAddress1
branchlist1 = branchlist2
branchAddress1 = branchAddress2
branchlist2 = replacelist
branchAddress2 = replacebranch
Else
If branchAddress2 > branchAddress3 Then
replacelist = branchlist2
replacebranch = branchAddress2
branchlist2 = branchlist3
branchAddress2 = branchAddress3
branchlist3 = replacelist
branchAddress3 = replacebranch
Else
If branchAddress3 > branchAddress4 Then
replacelist = branchlist3
replacebranch = branchAddress3
branchlist3 = branchlist4
branchAddress3 = branchAddress4
branchlist4 = replacelist
branchAddress4 = replacebranch
Else
If branchAddress4 > branchAddress5 Then
replacelist = branchlist4
replacebranch = branchAddress4
branchlist4 = branchlist5
branchAddress4 = branchAddress5
branchlist5 = replacelist
branchAddress5 = replacebranch
End If
End If
End If
End If
Loop Until branchAddress1 <= branchAddress2 And branchAddress2 <=
branchAddress3 And branchAddress3 <= branchAddress4 And branchAddress4 <=
branchAddress5
 
O

Otto Moehrbach

What is branchAddress1 and what is branchAddress2? If these are cell
addresses then you need to work with the row and not the cell address, such
as:
Range(branchAddress1).Row
Does that work for you? HTH Otto
 
L

Leith Ross

Hello Ironhydroxide,

Place this code in a standard VBA module. Add the macro into code i
the example below. The macro function compares the first address wit
the second address. The results are less than = -1, equal = 0, greate
than = 1

EXAMPLE
============================================================
Replace the IF ...THEN code below...
If branchAddress1 > branchAddress2 Then

With this code...
If CmpAddresses( branchAddress1, branchAddress2) = 1 Then
============================================================

MACRO CODE
=========================================================
Function CmpAddresses(Address1 As Range, Address2 As Range) As Integer
'Results:
' Addx1 > Addx2 = 1
' Addx1 = Addx2 = 0
' Addx1 < Addx2 = -1

Dim Addx1 As String
Dim Addx2 As String

With Address1
Addx1 = Str(.Column * .Row)
End With

With Address2
Addx2 = Str(.Column * .Row)
End With

CmpAddresses = StrComp(Addx1, Addx2)

End Function
=========================================================

Sincerely,
Leith Ros
 
I

Ironhydroxide

Yes the branchAddress1 and ~2 are cell addresses.

i replaced the questionable code, and it works great now.

Thanks for the "hinweis".
 
Top