excel - InStr to extract all text before "[" if some cells contain "[" and some do not

Question

I have the following piece of code that returns a

Run-time error '5'

as soon as it comes across a cell that do not contain [

Cells(i + 2, 11) = Left(Cells(i * 2 + 1, 12), InStr(1, Cells(i * 2 + 1, 12), "[") - 1)

In this example:
Cheese[1] is ok and returns Cheese.
Marmite(2)[13] is ok and returns Marmite(2).
Spreadable butter(13) doesn't work.

What is the fix please

---

Thank you, so following on from the help below how do I incorporate and apply two IF statements into the following?

For i = 1 To 4
   If Cells(i * 2 + 2, 14) <> "" Then
      Cells(i + 2, 3) = Left(Right(GetURL(Cells(i * 2 + 2, 17)), 12), 7)
      Cells(i + 2, 11) = Left(Cells(i * 2 + 1, 12), InStr(1, Cells(i * 2 + 1, 12), "[") - 1)
      Cells(i + 2, 12) = Left(Cells(i * 2 + 1, 13), InStr(1, Cells(i * 2 + 1, 13), "[") - 1)
   End If
Next i

I am only a beginner at VBA.

标签： excelvba

Answer 1

您可以检查它是否包含[ first...

Instr()如果找不到您要查找的字符串，则该函数返回 0，因此如果没有If首先检查该字符串的语句，您最终会得到一个 0，并且您的代码从零中减去 1 得到 -1，即不是该Left函数的有效参数，因此您会收到错误消息。

If InStr(1, Cells(i * 2 + 1, 12), "[") > -1 Then
  Cells(i + 2, 11) = Left(Cells(i * 2 + 1, 12), InStr(1, Cells(i * 2 + 1, 12), "[") - 1)
End If

这假设单元格实际上永远不会以[

如果它是第一个字符，为避免出现错误：

If InStr(1, Cells(i * 2 + 1, 12), "[") > 0 Then
  Cells(i + 2, 11) = Left(Cells(i * 2 + 1, 12), InStr(1, Cells(i * 2 + 1, 12), "[") - 1)
End If