Translating excel formula into VBA - remove characters after last comma.

[twanbiz]

Hello,

I have some code that works in excel but I am trying to get the formula into VBA - I am getting a compile error for invalid character on the "@". The values are in column A and would be output in column B of sheet2. Any help would be great.

Here is the excel formula that works:

=IFERROR(LEFT(A1,FIND("@",SUBSTITUTE(A1,",","@",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1),"")

Below is what I am trying with VBA:

Set calcsheet = Sheets("sheet2")
With calcsheet.Range("b1:B100000")
.FormulaR1C1 = _
"=Left(RC1, Find("@", Substitute(RC1, ",", "@", Len(RC1) - Len(Substitute(RC1, ",", "")))) - 1)"
.Value = .Value
End With

[Norie]

Try this.

Please Login or Register  to view this content.

[jolivanes]

For future reference:
Enter the formula in a cell
Select the cell
Following depends on Excel Version but you get the idea.
Tools, Macro, Record (Yes to Macro Name)
F2, Enter
Your formula is now in VBA format as code. (Check it!)

Just for the heck of it, without a formula.
Might be considerably slower because of the looping. Let us know if you try it.

Please Login or Register  to view this content.

[twanbiz]

Hello all, thank you for the posts! I didn't try the loop since I have almost a million rows of data, it runs pretty quick using the excel formulas. Thanks again!