Random Results when sorting using VBA?

[RedOctoberKnight]

Good Morning.

I'm racking my brain trying to understand what is going on but I cant seem to figure it out.

I have the following code that I want to sort the range by Last Name in Alphabetical Order.

Sub sort_CSA()
Range("A6:T74").Sort KEY1:=Range("B6"), order1:=xlAscending, Header:=xlYes
End Sub

Last names are in column B starting in cell B7. I have a headers in row 6.

I cant figure out why but every time I run it, I get a different random order in Column B.

Anybody have any suggestions?

 

[Fluff]

Do you have a formula in col B?

 

[RedOctoberKnight]

I do...

 

[Fluff]

Does the formula refer to the name of the sheet it's on?

 

[RedOctoberKnight]

Does the formula refer to the name of the sheet it's on?

=INDEX('PHONE LIST'!$C$4:$C$72,MATCH(MASTER!A14,'PHONE LIST'!$B$4:$B$72,0))

 

[Fluff]

Is that on the "Master" sheet?

 

[RedOctoberKnight]

Is that on the "Master" sheet?

yes

 

[Fluff]

In that case use

=INDEX('PHONE LIST'!$C$4:$C$72,MATCH(A14,'PHONE LIST'!$B$4:$B$72,0))

you should never refer to the sheet name the formula is on.

 

[RedOctoberKnight]

wow, thank you! I had no idea that would cause such an issue! Would you mind explaining to me why that happens?

 

[Fluff]

I've no idea why it happens, just that it does.