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.

 

[footoo]

Could also do with the Replace method :

[A:A].Replace what:="-*", replacement:="", lookat:=xlPart

Or :

[A:A].Replace "-*", "", xlPart

 

[vonguyenphu]

Thank you Mr Rick,

I often work with such problems and find your usage of evaluate function fantastic so I really want to learn how it works.
I have the same problem with another issue. I want to split cell into 2 cells by seperator "-". Can you help me with the same evaluate function?
eg:
$A1="abcd-1" => $A1="abcd" and $B1="1"
$A2="xyz-123" => $A2="xyz" and $B2="123"

 

[Rick Rothstein]

First off, if you have not seen it yet, I would direct you to footoo's solution (Message #11 ) for your original problem as it is much better than the Evaluate method Jaafar gave you even with the addition I provided.

As to the question you raised in Message #12 ... I would not use Evaluate for that, rather, I would use the TextToColumns method instead.

Sub SplitOnDash()
  Columns("A").TextToColumns Range("A1"), xlDelimited, , , False, False, False, False, True, "-"
End Sub

 

[Jaafar Tribak]

Hi Rick, Well spotted !

I didn't think about possible empty entries.

@vonguyenphu

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

I always search Rick Rothstein's posts when I have vba formulaes\string manipulations issues. I would resommend that you look at his valuable contributions on the subject in this forum. I am sure you will learn alot.

As for footoo's soultion, it is obviously simpler and most likely quicker. Thank you.

Regards.

 

[Rick Rothstein]

I always search Rick Rothstein's posts when I have vba formulas\string manipulations issues.

You do? I am honored... and speechless at the thought!

I would recommend that you look at his valuable contributions on the subject in this forum. I am sure you will learn a lot.

Thank you so much for your kind words above and for this recommendation... you are far too kind.

 

[vonguyenphu]

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.

Hi Rick,

I just recently wanted to reuse your code but now it threw syntax error. Can you help me?

Compile Error: list seperator or )

 

[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.

Hi Rick,

I just recently wanted to reuse your code but now it threw syntax error. Can you help me?

Compile Error: list seperator or )

That code was posted before the forum changed to its new format. I notice that the color coding I used in those code module is there in HTML format, so if you copied what you have quoted above, it won't work. Here are the code modules with the color coding implemented so that you can copy/paste directly from the code shown below.

Range("A1:A10") = Evaluate("IF({1},LEFT(" & Range("A1:A10").Address & ",3))")

Sub LeftWithoutLoop(ByVal R As Range, ByVal Length As Long)
    R = Evaluate("IF({1},LEFT(" & R.Address & "," & Length & "))")
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("IF(" & Range("A1:A10").Address & "="""","""",LEFT(" & Range("A1:A10").Address & ",3))")

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

 

[vonguyenphu]

That code was posted before the forum changed to its new format. I notice that the color coding I used in those code module is there in HTML format, so if you copied what you have quoted above, it won't work. Here are the code modules with the color coding implemented so that you can copy/paste directly from the code shown below.

Range("A1:A10") = Evaluate("IF({1},LEFT(" & Range("A1:A10").Address & ",3))")

Sub LeftWithoutLoop(ByVal R As Range, ByVal Length As Long)
    R = Evaluate("IF({1},LEFT(" & R.Address & "," & Length & "))")
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("IF(" & Range("A1:A10").Address & "="""","""",LEFT(" & Range("A1:A10").Address & ",3))")

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

Thank you for your quick reply. Now after looking back your code, I understand a little more.