Remove Characters from String Macro

[boldcode]

Hi,

The formula below works great when I use it in a cell on a worksheet:

=Left(RunInformation!E6, Find("_", Substitute(RunInformation!E6, "_", "_", 1)) - 1)

What it does, it removes all the characters after the first underscore and the underscore as well. I want to now use this formula with the following VBA code, but I get an error highlighting the word "Subsitute" and saying that the function is not defined. I really don't know how to fix this

Range("A3:" & Number2Char(LastColumn) & "3").Select
    ActiveCell.FormulaR1C1 = Left(RunInformation!E6, Find("_", Substitute(RunInformation!E6, "_", "_", 1)) - 1) & " Points"
    Selection.Font.Bold = True
    With Selection.Font

 

[kpark91]

    ActiveCell.FormulaR1C1 = "Left(RunInformation!E6, Find(""_"", Substitute(RunInformation!E6, ""_"", ""_"", 1)) - 1) & "" Points"""

 

[Weaver]

It's the double quotes. If you want to build a string with double quotes in, you have to repeat them

 

[boldcode]

kpark91,

Your suggested code is not working. When I look at the worksheet it shows me the following:

Left(RunInformation!E6, Find("_", Substitute(RunInformation!E6, "_", "_", 1)) - 1) & " Points"

It should show me:

2010 Points

 

[kpark91]

Try this again;;; I forgot the equal sign.

    ActiveCell.Formula = "=Left(RunInformation!E6, Find(""_"", Substitute(RunInformation!E6, ""_"", ""_"", 1)) - 1) & "" Points"""

Would this work?

 

[Scott Huish]

You shouldn't need the R1C1 if you are going to use A1 style references.

 

[Scott Huish]

It is also very rare that you need to Select anything in VBA you can work with ranges directly.

How are you determining the last column? You shouldn't need a UDF for that either.

With Range("A3:" & Number2Char(LastColumn) & "3")
    .Formula = "=Left(RunInformation!E6, Find(""_"", Substitute(RunInformation!E6, ""_"", ""_"", 1)) - 1) & "" Points"""
    .Font.Bold = True
End With

 

[boldcode]

kpark91 & HOTPEPPER,

You both solved it. Thank you both, I really appreciate it.

BC

 

[Rick Rothstein]

Let's go back to your original formula for a moment...

=Left(RunInformation!E6, Find("_", Substitute(RunInformation!E6, "_", "_", 1)) - 1)

Unless you have a type, this part...

Substitute(RunInformation!E6, "_", "_", 1)

appears to do nothing. Well, actually it does take the time to do something... it replace all underbars with underbar which is nothing more than the original contents of the cell. Perhaps you meant this instead...

=Left(RunInformation!E6, Find("_", RunInformation!E6&"_") - 1)

which guarantees that an underbar will be found even if E6 doesn't have one (thus returning the entire contents of E6 and preventing the FIND error when no underbar exists)?