LEFT RIGHT関数のことで教えてください

質問

いつもお世話になっております
LEFT RIGHT関数のことで教えてください

Private Sub Worksheet_Change(ByVal Target As Range)内のマクロで
セルに式を書き込んでいます

Range("D" & Target.Row).FormulaR1C1 = "=LEFT(RC[-1],5)"
セルＤ16には機器名5と書いてあります
この時
Range("D" & Target.Row).FormulaR1C1 = "=LEFT(RC[-1],5)"
の5は、セルD16の機器名5の右から1文字目の5なのですが
式ではどう表現したらいいのでしょうか？

回答No.4

RandenSai · ベストアンサー

回答No.1です。

> 以下のIF分ですが、そもそも、D16の右端が数値でないと＃ＮＡＭＥ？のエラーになってしまいます

それをするのにぴったりなのが、IsNumeric関数です。この関数は、引数が数字かどうかを調べるものですね。これを使った判定は、こんな感じでどうでしょう。

Dim rno As String
If Not IsNumeric(Right(Range("D16").Value, 1)) Then
rno = "5" '数値ではないので、5ということにする
Else If CInt(Right(Range("D16").Value, 1)) = 0 Then
rno = "5" '数値だがゼロなので、5として扱う
Else
rno = Right(Range("D16").Value, 1)
End If
Range("D" & Target.Row).FormulaR1C1 = "=LEFT(RC[-1]," & rno & ")"

お礼

何度も教えていただきまして恐縮です
IF文の書き方大変参考になります

そっくり使わせていただきます
ありがとうございました

補足

お礼です
お陰様でエラーのでない完ぺきな文字列操作がマクロでできました
時々、教えてもらってもよく理解できないケースがありますが
今回はすべて理解できます
また一歩進歩できました
ありがとうございました

回答No.5

WindFaller

こんにちは。

#3の回答者です。
せっかくですから、私のお返事を寄せておきます。
>シートは保護せずに、自由に行の削除ができるようにしてあるので
>式が削除された時の対応でマクロで式を書き込んでいます

一般的には、自由に行の削除ができるようにしている場合は、ワークシートは、[テーブル]という範囲に設定を換えてあげると、連続した数式の場合、そのスタイルを保ったままにしてくれます。一度、試してみたらいかがかと思います。

それから、マクロ側では、行の削除に伴うマクロは、二種類あります。
ひとつは、行の削除コマンドに、インスタンスを設ける方法と、もうひとつは、SetTimer という、API関数を使って、新たなイベントを設ける方法の両方があります。私は、前者の方法を使っています。残念ながら、Worksheet_Changeは使いません。
最初から、その旨を教えていただいていたら、別の方法も考えることが出来ましたが、私にとっては、とても残念な結果になってしまいました。

お礼

お世話になっております
メールは昼間に拝見しましたが、会社のＰＣではｇｏｏのような掲示板にアクセスできません
仕事の都合で対応がこの時間になってしまいました

丁寧なアドバイスいただきましてありがとうございます
また、こちらから一方的に締め切ってしまいました失礼をお許しください

なにせ、マクロの勉強を始めたのはこの一か月程度で、エクセルも簡単な式を入れる程度の実力です
この年で新たに覚えるのは大変ですが、嫌いではないので毎日こつこつやっております

教えてｇｏｏの皆さんのお蔭で、日々進歩はしていますが、素人の域をでません

今、一番困っているのは、R1C1で、RC[-2]*RC[-6]のようにマクロで式を書いているので
シートのスタイルを変えるたびに（列が増減するたびに）、式を書き換えなくてはいけないことです

テーブル、インスタンス等の用語も初めてしりました
アドバイスいただいた内容で検索して勉強してみます

親切で丁寧なアドバイスありがとうございました

回答No.3

WindFaller

#2の回答者です。

>D16は住所5として、D17以下の行に左の列の住所の5文字を取り出して表示する

D16に入力されている「住所5？（前回は機器名5)」の文字列定数の、一番右側の数値「5」を取り出す。
その数値を使って、C列にある（イベントが発生した)行のデータを、左から5文字取り出す、
ということらしいですが、不足している部分があります。

それは、Worksheet_Change型のイベント・ドリブン型で、値の何に対してイベントにしているのか書かれていません。

イベント・ドリブン型で相対参照の数式を書くようなマクロは、長い間の経験でも、ほとんど書いたことがないと思います。一応、こちらの回答は載せては置きますが、こちらが、理解していないものは、たぶん、回答にはなっていないと思います。

'//
Private Sub Worksheet_Change(ByVal Target As Range)
　Dim myData As Variant
　Dim i As Variant
　If Target.Row <= 16 Then Exit Sub '16行以下では起動しない
　If Target.Count > 1 Then Exit Sub　'セルが2個以上選択は不可
　If Target.Column <> 4 Then Exit Sub　'D列以外では起動しない
　If Target.Offset(, -1).Value = "" Then Exit Sub　'C列にデータがなければ起動しない

　myData = Range("D16").Value
　i = Right(myData, 1)
　If Len(myData) < 2 Or IsNumeric(i) = False Or i < 1 Then Exit Sub '文字数が1文字、右端が数値でない、取り出した数値が、0以下の場合不可

　Application.EnableEvents = False
　Cells(Target.Row, 4).FormulaLocal = "=LEFT(RC[-1]," & i & ")"
　Application.EnableEvents = True
End Sub
'//

でも、こんなことをしなくても、
　マクロで、こういう数式をおくか"=IF(ISNUMBER(RIGHT(R16C4,1)*1),LEFT(RC[-1],RIGHT(R16C4,1)),"""")"

R1C1方式
　=IF(ISNUMBER(RIGHT(R16C4,1)*1),LEFT(RC[-1],RIGHT(R16C4,1)),"")
A1方式
　=IF(ISNUMBER(RIGHT($D$16,1)*1),LEFT(C17,RIGHT($D$16,1)),"")

で、セルにおいて、フィルハンドル・コピーしてもよいのではないでしょうか？

お礼

返信が遅くなりました

＞イベント・ドリブン型で相対参照の数式を書くようなマクロは、長い間の経験でも、ほとんど書いたことがないと思います。

上の件ですが
シートは保護せずに、自由に行の削除ができるようにしてあるので
式が削除された時の対応でマクロで式を書き込んでいます

補足

お世話になっております
大変参考になることをいっぱい書いていただきましたので、今のマクロを手直し中です
また後ほど、お礼と説明をさせてください

私のエクセルの実力は、簡単な式を書いて計算する程度です
マクロは１カ月ほど前から作り始めたもので、自分でも、頭の中がごちゃごちゃになっています

以下の部分を使わせていただきます

If Target.Row <= 16 Then Exit Sub '16行以下では起動しない
If Target.Count > 1 Then Exit Sub　'セルが2個以上選択は不可
If Target.Column <> 4 Then Exit Sub　'D列以外では起動しない

回答No.2

WindFaller

こんにちは。

これは、VBAの問題とは違うような気がします。
それに、
> Range("D" & Target.Row).FormulaR1C1 = "=LEFT(RC[-1],5)"
>セルＤ16には[機器名5]と書いてあります
D列に文字があって、数式をD列に上書きしたら、文字はなくなってしまいます。

例えば、以下のようになっている場合、
　C列　　
a機器名5bcde
で、5を取り出すために、D列に数式を書くのでしたら、
"=RIGHT(LEFT(RC[-1],5),1)"

ですが、……。
これは、
"=MID(RC[-1],5,1)"
と同じ意味です。

目的が違うようでしたら、もう一度、全体的な流れから、正確に質問内容を書いたほうがよいです。

補足

親切に教えていただきましてありがとうございます
私の質問に説明不足がありました

D16は項目名のようなもので、D17以下の行には、C17以下の行の左端の文字列を取り込んで表示したいと
思っています

その際に、いつでも取り出す文字数が変更できるように、項目名の最後に取り出す文字数を加えて表示します
C17以下の行が住所だったら、D16は住所5として、D17以下の行に左の列の住所の5文字を取り出して
表示するというものです

回答No.1

RandenSai

その、5を引っ張ってくるセルはD16で不変ですか？であればこういう手はどうでしょう。要はRangeでセルD16の中身の一番右から1文字取り出して、それと式を結合しているだけです。

Range("D" & Target.Row).FormulaR1C1 = "=LEFT(RC[-1]," & Right(Range("D16").Value,1) & ")"

これはベタ書きしてるので、見にくいと思う場合は変数に取り出してから記述すればいいでしょう。ついでにエラーチェックもしておくと安心ですが、これはマクロの外でワークシート関数でやることも可能ですね。

お礼

RandenSaiさん、初めまして
親切に教えていただきましてありがとうございます
D16は不変です

& Right(Range("D16").Value,1) & ")"の部分が私の能力では書けませんでした
早速教えていただいた内容でマクロに書いてみると、見事にD16の右端の5を読み込んでいます
右端の数値を変えても見事に対応しています

できないことができると感動ものですね
ありがとうございました
大切に使わせていただきます

セルD16は保護しないので確かにこれはエラー処理が必要です

その対策として、IF分を使って
D16の右端の一文字が数値でなかったら、かつ、1以上でなかったら5とする
とやってみるつもりです

ありがとうございました

補足

すみませんが、もうひとつ教えてもらってもいいでしょうか
以下のIF分ですが、そもそも、D16の右端が数値でないと＃ＮＡＭＥ？のエラーになってしまいます

If Right(Range("D16").Value, 1) > 1 Then
Range("D" & Target.Row).FormulaR1C1 = "=LEFT(RC[-1]," & Right(Range("D16").Value, 1) & ")"
Else
Range("D" & Target.Row).FormulaR1C1 = "=LEFT(RC[-1],5)"
End If

ＩＦ文をどう書いたらいいでしょうか？