エクセルＶＢＡで数式を入力したいのですが、

質問

文字列（数値が他の文字と混在して入力されている）のセル内容に空白を見つけて、空白の前の文字（データによって１～４桁、実際は数字）を表示したいので、下記のようにしました。
結果、『SEARCH(" "』の空白を指定する『"』でエラーになってしまいます。
このエラーを回避する方法を教えて下さい。

Range("J12").FormulaR1C1 = "=mid(r[8]c[-5],SEARCH(" ",r[8]c[-5],6)-5),4)"

セルデータの例
  1: 52 ABCD EFGH(AAA)
  6: 823 JYUGF JKLM (B)
『:』までには半角空白が２個、次に半角数字が１桁、
次に半角空白があり、次の半角空白までに数字１桁から４桁があります。この数字を取り出したいのです。

宜しくお願いします。

回答No.1

bin-chan · ベストアンサー

["]を設定する際は　[""]で表記します。

Range("J12").FormulaR1C1 = "=mid(r[8]c[-5],SEARCH("" "",r[8]c[-5],6)-5),4)"

お礼

"" ""なのですか？
分かりました、ありがとうございます

回答No.3

imogasi

●他人のことをとやかく言うのも出すぎかも知れないし、それなりの理由があり意識的に使っておられるのかもしれないし、質問の回答と違うことなのですが、、R1C1形式の使用をやめるのはどうですか。
エクセルの過去４年のVBAの質問から推測しても、ほとんどA1形式を使用してるようです。
またCells（i,j)方式は便利です・
●またVBAでなら、
（１）セルに関数式を設定するFormulaを使わず
（２）ワークシート関数のSEARCH・Findを使わないで、
（３）Instrを使い、含まれてなければエラーにならず、値が０になるので判別できます。
●本質問では、数字がどうか調べの開始桁は固定しているようなので、その数字がある可能性のある桁から、４桁（または半角空白まで）、１桁ずつIsNumericかどうかをIfで聞くとかにする方法もあります。関数に頼らないVBらしい方法です。

お礼

はい、平常はＲ１Ｃ１形式は使用していないのですが、いろいろと試行錯誤でここのルーチンだけＲ１Ｃ１を使用してみようかなと思ったのですが、Ａ１形式でも考えます。
instrを使うことも考えてみます。ただ必ず半角の空白がいくつか、それに半角の数字が羅列されているその中からある部分の数字が欲しかったので、安直に流れたのかもしれません。
IsNumericの方法もあるのですね！これから考えて見ます。
ありがとうございます

回答No.2

Wendy02

こんにちは。

数式だったら、一般、Office で、VBAとは違うと思いますが、式を作って、記録マクロで、再度、Enter をすれば、取れると思います。

R1C1側は、コメントアウトしています。
VBAとしては、ちょっと可読性が落ちますね。

Sub testSample1()
Range("J12").FormulaLocal = "=MID(E20,FIND("":"",E20)+2,FIND(""^"",SUBSTITUTE(E20,"" "",""^"",4))-FIND("":"",E20)-2)"

'Range("J12").FormulaR1C1 = "=MID(R[8]C[-5],FIND("":"",R[8]C[-5])+2,FIND(""^"",SUBSTITUTE(R[8]C[-5],"" "",""^"",4))-FIND("":"",R[8]C[-5])-2)"
End Sub

しかし、VBAだったら、そのまま、VBAのコードで出力するか、以下のようなユーザー定義関数でよいと思います。

Function GetNumber(ByVal s As String) As Variant
Dim i As Integer
Dim buf As String
For i = 1 To Len(s)
　If IsNumeric(Mid(s, i, 1)) Then
　　buf = Trim$(Mid(s, i + 2))
　　buf = Mid$(buf, 1, InStr(buf, " "))
　　Exit For
　End If
Next i
　GetNumber = Trim$(buf)
End Function

お礼

なるほど
ユーザー関数を使うのですね。
ありがとうございます