Is there any way to change range's values more efficiently than looping?

[vonguyenphu]

I have a range of cells with values inside. The values have pattern such as '123-abc'. Now I need to change the value of selection to just 123.
I currently use left function to loop over the selection. But it's very slow because I have 1000 cells.

 

[Jaafar Tribak]

Is that in a macro ? if so, this for example, should do what you want without the need for looping.

Range("A1:A1000") = Evaluate("=LEFT(A1:A1000,3)")

Change range address as required.

 

[vonguyenphu]

That's awesome Tribak, but is there anyway to use Evaluate function for range variable and Selection?
eg: Selection: = Evaluate(..Selection)
Dim rng As Range
rng = Evaluate(...rng)

 

[Jaafar Tribak]

You mean like this :

Range("A1:A1000") = Evaluate("=LEFT(" & Range("A1:A1000").Address & ",3)")

Or amybe wrap the code in a seperate sub for more flexibility :

Sub LeftWithoutLoop(ByVal R As Range, ByVal Length As Long)
    R = Evaluate("=LEFT(" & R.Address & "," & Length & " )")
End Sub

Then you just pass to the above Sub the range of your choice and the number of characters you wish to extract :

Sub [B]Test1[/B]()
    Call LeftWithoutLoop(Range("A1:A1000"), 3)
End Sub

Or :

Sub [B]Test2[/B]()
    Call LeftWithoutLoop(Selection, 3)
End Sub

 

[vonguyenphu]

Hi Tribak, that is a complete solution. Thank you!
Do you know any guide or tutorial for Evaluate function, I already search online but didn't find a good one.

 

[Jaafar Tribak]

Hi Tribak, that is a complete solution. Thank you!

Do you know any guide or tutorial for Evaluate function, I already search online but didn't find a good one.

You are welcome.

I don't know of a tutorial but a quick search came up with this discussion :
https://www.ozgrid.com/forum/forum/...evaluate-most-powerful-command-in-vba?t=52372

I am sure, there should be more info about this useful function scattered over the net. I would just keep looking.
.
Good luck.

 

[vonguyenphu]

Hi Tribak,

After checking your solution would fail for multiple cells with different values. Do I miss anything?
Eg: A1="123-abc", A2="456-xyz"they wouldd be both 123
I just need A1="123" and A2="456"

 

[Rick Rothstein]

String functions do not natively process arrays, so it must be "induced". If there are no blanks within your range, these (from Jaafar's code in Message #4 ...

Range("A1:A10") = Evaluate("[B][COLOR="#FF0000"]IF({1},[/COLOR][/B]LEFT(" & Range("A1:A10").Address & ",3)[B][COLOR="#FF0000"])[/COLOR][/B]")

Sub LeftWithoutLoop(ByVal R As Range, ByVal Length As Long)
    R = Evaluate("[B][COLOR="#FF0000"]IF({1},[/COLOR][/B]LEFT(" & R.Address & "," & Length & ")[B][COLOR="#FF0000"])[/COLOR][/B]")
End Sub

If, on the other hand, there could be blanks within your data, we need to test for them or else they will become zeros...

Range("A1:A10") = Evaluate("[B][COLOR="#FF0000"]IF(" & Range("A1:A10").Address & "="""","""",[/COLOR][/B]LEFT(" & Range("A1:A10").Address & ",3)[B][COLOR="#FF0000"])[/COLOR][/B]")

Sub LeftWithoutLoop(ByVal R As Range, ByVal Length As Long)
    R = Evaluate([B][COLOR="#FF0000"]Replace([/COLOR][/B]"[B][COLOR="#FF0000"]IF(@="""","""",[/COLOR][/B]LEFT([B][COLOR="#FF0000"]@[/COLOR][/B]," & Length & "))"[B][COLOR="#FF0000"], "@", R.Address)[/COLOR][/B])
End Sub

Note: The above is untested, but I think I got it correct.

 

[vonguyenphu]

Hi Mr Rick,

All of them are tested successfully, your answers are more than I need. Can you explain why you put the If clause to induce array?
For the two final ones, can you provide cases to illustrate as I don't see any differences.

 

[Rick Rothstein]

Can you explain why you put the If clause to induce array?

I cannot give you a technical reason... it is just something I discovered when playing around with the Evaluate function when I first learned about it a few years back. The non-technical reason that I concluded after much testing is that text functions do not return arrays natively; however, if you embed them within a function that can process array (the IF function for example), then that seems to induce array processing in the text functions.

For the two final ones, can you provide cases to illustrate as I don't see any differences.

None is needed... I did nothing to change how they interact with the LeftWithoutLoop function, so just use them as Jaafar instructed.