Why does this code sometimes not work?

[tjak]

This code is not being correctly read on a consistent basis and I don't know why. Somedays the alpha split is correct, and somedays, "Barb" gets alpha starting at "J" and sometimes starting at "M". The first split ("Jill") consistently is correct. When "Barb" is wrong, it throws off "Julie".

Can anyone tell me why the "Barb" split is not getting correctly processed
every time?
Is it the code? Is my XL corrupted? Or?

Range("d2").Select
ActiveCell.Formula = "=If(g2>2500,""BETTY"",If(a2<""G"",""JILL"",If(A2>""M"",""BARB"",""JULIE"")))"
Range("d2").Select
Selection.AutoFill Destination:=Range("D2:d" & LastRow)

Thanx.

 

[Andrew Poulsom]

I think you had better tell us what's in column A when it goes wrong.

 

[tjak]

Column A is name (last, first). Everyday.

 

[Andrew Poulsom]

Please give the actual entry in column A when Barb and Julie are incorrect.

 

[tjak]

I can't - they're customer names. Privacy laws.
I'm not sure what you're looking for, so maybe some background will do?

I download the report each day. I've never noticed that any of the names start with a blank space (they all look aligned). All caps. They would look like: SMITH,JOHNR.

Today, "Barb" started at the M's. Yesterday, the split started with the J's. The previous 6 days, all was correct. That's when I started with this module.

Does any of this help?

 

[Andrew Poulsom]

There must be something at the start of the cell that's greater than M. A space isn't.

If you aren't willing to share the problem name, what does

=CODE(LEFT(TheCellWithTheProblemName))

return?

 

[tjak]

"77" ,when the name in the cell starts "MABL....." (This from today's report.)
This signifies what?

 

[Andrew Poulsom]

In that case your formula should return "BARB" - it did for me.

 

[tjak]

The alpha split should be:
A - F "Jill"
G - M "Julie"
N - Z "Barb"

That's the rub - until 2 days ago, I got the correct alpha split from the code. Now something has gone wrong, but I haven't changed the code. Barb shouldn't start until "N".

 

[Andrew Poulsom]

Well if you put MABL in A1 and M in A2 and sort A1:A2 ascending, the order will be reversed. So "MABL" is greater than "M".

What you probably need is:

If(LEFT(A2)>""M"",""BARB"",""JULIE"")