1

I currently have a piece of VBA code that looks for the first two characters on the left which is:

Range("EB5").Select
ActiveCell.FormulaR1C1 = "=LEFT(Input!R[-2]C[-92],2)"
Range("EB5").Select
Selection.AutoFill Destination:=Range("EB5:EB" & rownumfinal)

I however want this to find the characters on the left until there is a space.

Could anyone advise on how to do this?

David Zemens
  • 53,033
  • 11
  • 81
  • 130
JDRows
  • 17
  • 7

1 Answers1

5

Instead of hard-coding 2 as the second argument for the LEFT function, use the FIND function:

"=LEFT(Input!R[-2]C[-92],FIND("" "",Input!R[-2]C[-92])-1)"

Also, learn to avoid Select. It's often unnecessary, and a main cause of errors.

Your code can be simplified I think to a single statement:

Range("EB5:EB" & rownumfinal).FormulaR1C1 = _
    "=LEFT(Input!R[-2]C[-92],FIND("" "",Input!R[-2]C[-92])-1)"
Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • You need to double up the quotes within the formula. ;) – Rory Dec 22 '15 at 14:43
  • 1
    Also, I'd probably use `"=LEFT(Input!R[-2]C[-92]&"" "",FIND("" "",Input!R[-2]C[-92])-1)"` in case there isn't always a space in the original text. :) – Rory Dec 22 '15 at 14:46
  • Hi, Thank you for your response. This is causing a Compile error: Expected: end of statementafter the find(" Any thoughts? – JDRows Dec 22 '15 at 14:47
  • @JDRows did you note the revision where we had to double the quotes? – David Zemens Dec 22 '15 at 14:49
  • Sorry to be a pain how do I make it so that if there is no space to return the value of the cell? – JDRows Dec 22 '15 at 15:00
  • I am reasonably certain that if you play with your formula on a worksheet, you wlil be able to figure it out. You'll need to enclose your formula within an `IFERROR` formula... – David Zemens Dec 22 '15 at 15:12
  • SOmething like `=IFERROR(FIND(" ",Input!R[-2]C[-92]),Input!R[-2]C[-92], __the rest of your fomula__)` – David Zemens Dec 22 '15 at 15:17
  • @JDRows You could use what I put in my second comment. – Rory Dec 22 '15 at 15:39