Hi Yasser, how are you?
YasserKhalil wrote: Why using different versions results in different results?
Microsoft recently introduced the new Dynamic Arrays to some users and some versions: this was a very bad idea in my opinion. It will frequently “throw a spanner in the works” as a result of different version compatibility. (
http://www.excelfox.com/forum/showthrea ... -CSE-Entry" onclick="window.open(this.href);return false; )
Evaluate Range things like your code line, frequently require a “coercion” to get the array results out which are available. The changes introduced by the new Dynamic Array may, I guess, effect how these things work. I am not sure. I only have up to Office 2010.
In Excel 2003, 2007 and 2010, I also get the same results of
Hans in all rows. This is what I would usually expect.
Hans.JPG :
https://imgur.com/Sq3P6dT" onclick="window.open(this.href);return false;
Hans.JPG
There are a few tricks that can coerce a code line like yours into giving back the array of results
This one is the one I most use
If({1}, __________ )
Applied to your code line it then gives me all three results
HansYasserSalem.JPG :
https://imgur.com/eM4nzbr" onclick="window.open(this.href);return false;
HansYasserSalem.JPG
Code: Select all
Sub Test()
Dim rng As Range: Set rng = Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
rng.Offset(, -1).Value = Evaluate("LEFT(" & rng.Address(0, 0) & ",SEARCH(""_""," & rng.Address(0, 0) & ",1)-1)")
' If({1}, _____ )
rng.Offset(, -1).Value = Evaluate("If({1},LEFT(" & rng.Address(0, 0) & ",SEARCH(""_""," & rng.Address(0, 0) & ",1)-1))")
End Sub
Alan
https://www.mrexcel.com/board/threads/r ... st-5401671" onclick="window.open(this.href);return false;
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also