No.3ベストアンサー
- 回答日時:
こんにちは、
1点目は、実行トリガーの三つを削除した時に連動して消えるようにするのは可能でしょうか?
3点目は、ボタン実行のパターンも知りたいです
1点目は、
If Cells(r, "B") <> "" And Cells(r, "E") <> "" And Cells(r, "G") <> "" Then を
If Cells(r, "B") <> "" Then とすれば、型番と機番は、入力時、つまり空白を含め書き替えられます。
しかし、日付を消した時にすべてを消す場合は、検索キーワードになっているので、少し工夫が必要です。
日付を消したら、出力先の型番と機番を消す方法としていくつか考えられますが、
Application.Undoを使用してみます
複数の実行条件で同じ処理を行うので、重複する処理は使い廻しをします。
例
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range, r As Long, dtKey As Date
If Intersect(Target, Range("B3:G15")) Is Nothing Then Exit Sub
Application.EnableEvents = False
r = Target.Row
If Target(1).Column = 2 And Target(1).Value = "" Then
Application.Undo
dtKey = Cells(r, "B")
Range("B" & r & ":G" & r) = ""
Call FindWord(dtKey, Target.Row)
End If
If Cells(r, "B") <> "" Then
dtKey = Cells(r, "B")
Call FindWord(dtKey, Target.Row)
End If
Application.EnableEvents = True
End Sub
Sub FindWord(dtKey As Date, r As Long)
Dim myRng As Range
Set myRng = Range("M2", Cells(2, Cells(2, Columns.Count).End(xlToLeft).Column)). _
Find(What:=dtKey, LookIn:=xlFormulas, SearchOrder:=xlByColumns)
If Not myRng Is Nothing Then
myRng.Offset(4) = Cells(r, "E").Value
myRng.Offset(5) = Cells(r, "G").Value
End If
End Sub
練る必要がありますが取り敢えず。
Application.EnableEvents = False はApplication.Undoエラーを回避する為、必須になります。
3点目は、ボタン実行のパターンも知りたいです
単純に考えると
Private Sub Worksheet_Change(ByVal Target As Range) の Target As Rangeに当てはめる値があれば良い事になるのではと
思いますが、
ボタンで実行するなら対象となるすべての範囲を対象に実行する方が簡単です。
また、ボタンから実行するなら関数式も理解しているようですので、色々遠回りせずExcelぽく
#1で示した 範囲=”関数式” 範囲.Value=範囲.Value の方が下記の様にわかり易いです。
Sub sample()
Dim col As Long
col = Cells(2, Columns.Count).End(xlToLeft).Column
Range(Cells(6, "M"), Cells(6, col)).Formula = "=IFERROR(INDEX($B$3:$H$15,MATCH(M1,$B$3:$B$15,0),4),"""")"
Range(Cells(7, "M"), Cells(7, col)).Formula = "=IFERROR(INDEX($B$3:$H$15,MATCH(M1,$B$3:$B$15,0),6),"""")"
Range(Cells(6, "M"), Cells(7, col)).Value = Range(Cells(6, "M"), Cells(7, col)).Value
End Sub
参考:少し遠回り
Private Sub Test()
Dim myRng As Range, r As Long, dtKey As Date
Dim Target As Range
Set Target = Selection(1)
If Intersect(Target, Range("B3:B15")) Is Nothing Then Exit Sub
ボタンからの処理なのに範囲が狭く、選択された日付セルを基準にしていますので
合理的な処理とは言えないと思いますが、あくまで参考です。
Qchan1962先生様、本当にありがとうございます。先生のおっしゃるように私にはボタンからの方がように分かり易いです。
ですが今回色んなパターンのコードを作って頂いたので理解して使いこなせるようにしたいと思います。
これからもお世話になることがあると思いますがよろしくお願いいたします。
No.2
- 回答日時:
#1です。
多分、動くだろうプロシージャを書く事は出来ますが、少し条件、表組みなどが
変わった場合、ご自身で改修出来ますでしょうか?
現在、関数式で問題ないようでしたらそのまま構築する方が良いのではとお節介に思います。
ご質問が >VBAでの方法があるのでしたらご教示ください。なので
サンプルを提示しますが、行っている内容などは、ご自身でメソッドなどを検索して理解してくださいね。
2行目が日付Date型になっているのなら、Findで探し、Offsetで入力できるかと思います。の方法です。
ワークシートモジュールに下記をコピペで
VBAの導入方法なども理解されている事を前提とします。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range, r As Long
If Intersect(Target, Range("B3:G15")) Is Nothing Then Exit Sub
Application.EnableEvents = False
r = Target.Row
If Cells(r, "B") <> "" And Cells(r, "E") <> "" And Cells(r, "G") <> "" Then
Set myRng = Range("M2", Cells(2, Cells(2, Columns.Count).End(xlToLeft).Column)). _
Find(What:=Cells(r, "B"), LookIn:=xlFormulas, SearchOrder:=xlByColumns)
End If
If Not myRng Is Nothing Then
myRng.Offset(4) = Cells(r, "E").Value
myRng.Offset(5) = Cells(r, "G").Value
End If
Application.EnableEvents = True
End Sub
M2行からの横の日付を検索対象にしていますので、このセルは 2020/9/5のように入力されていて
書式設定でdにして表示1,,,4,5の様になっています。>1行目が月、2行目が日、3行目が曜日で3つとも年月日で入れております。
列方向のセル結合がある為、SearchOrder:=xlByColumns
実行トリガーはB3:G15ですが、同じ行の3つ共に値が入っている時に実行されます。
逆に言うと3つとも値がある状態でいずれかを変更すると対象セルに値が入ってしまいます。ご入力の可能性があります。
ご入力を一旦止めてキャンセルできるようにするか、トリガーを狭め条件を厳しくするか、または、入力実行後
トリガーのセルの値をクリアーするなど更に工夫が必要と思います。
(入力セルは一行で良い様な気がします。入力実行後下に蓄積させるなどで)
Application.EnableEvents = Falseは、イベント発生を止めるコマンドです。
Application.EnableEvents = Trueを実行せずにVBAを終了すると次回イベントが発生しませんので
検証時は、'Application.EnableEvents = False などとして無効にしてください。
Qchan1962様、ありがとうございます。やりたいことができました。3点聞きたいことがあります。1点目は、実行トリガーの三つを削除した時に連動して消えるようにするのは可能でしょうか?2点目は、今後横展開を考えており、列方向のセル結合がない(現状1日2列ですが1日1列とした時のこと)ときはSearchOrder:=xlByColumnsの式は不要ということでしょうか?>列方向のセル結合がある為、SearchOrder:=xlByColumns 3点目は、ボタン実行のパターンも知りたいです。標準モジュールに移し
Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range, r As Long
If Intersect(Target, Range("B3:G15")) Is Nothing Then Exit Sub
' Application.EnableEvents = False
r = Target.Row
If Cells(r, "B") <> "" And Cells(r, "E") <> "" And Cells(r, "G") <> "" Then
Set myRng = Range("M2", Cells(2, Cells(2, Columns.Count).End(xlToLeft).Column)). _
Find(What:=Cells(r, "B"), LookIn:=xlFormulas, SearchOrder:=xlByColumns)
End If
If Not myRng Is Nothing Then
myRng.Offset(4) = Cells(r, "E").Value
myRng.Offset(5) = Cells(r, "G").Value
End If
' Application.EnableEvents = True
End Sub
でボタンと紐づけれるか試みたのですがだめでした。
週末の貴重な時間を私の我儘にお付き合い下さりありがとうございます。
No.1
- 回答日時:
こんにちは、
ご質問の場合、関数式とVBAの大きく違うところは、いつ実行するかと言うところです。
関数は、数式の参照セルに入力された時に解が求められますが、VBAはそのトリガーを
どうするかを、仕様に含める必要があります。
例えば、G:Hセル(A:H)に入力された時に実行するとか、まとめてボタンから実行するとかです。
前者は、シートのイベントを使いますが、後者は、ボタンにプログラムを登録します。
また、入力されているINDEX,MATCH関数を補足で示されると良いと思います。
右表のセルが日付Date型になっているか等もわかるので、、
自動記録でいくつか作って手を加える方法も考えられますが、結合セルが少し気になります。
2行目が日付Date型になっているのなら、Findで探し、Offsetで入力できるかと思います。
また、VBAで 入力したい範囲に=”INDEX,MATCH関数” .Value=.Value なんて方法もあると思います。
参考:https://kokodane.com/mini_macro29.htm
こんばんは、御回答ありがとうございます。助言通りに補足をさせていただきました。参考のサイトもみせてもらいましたが、自分にはうまく組めなくて途方にくれています。
後、ラインの日付、1行目が月、2行目が日、3行目が曜日で3つとも年月日で入れております。
よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelシートのある番地の文字が一致したすべての行を別シートに転記する方法 11 2022/10/25 08:43
- Visual Basic(VBA) Excle VBA Findメソッドについて 3 2022/07/15 13:56
- Excel(エクセル) ExcelVBAの、Index matchについて 2 2023/04/18 16:57
- Excel(エクセル) VBAで重複データを合算したい(時間) 1 2022/12/08 23:06
- Access(アクセス) Accessで予定表を作成しようとしてます。 テーブル フィールド名 連番 オートナンバー型 年月日 2 2023/07/23 11:40
- Excel(エクセル) エクセルVBA、ファイル名をセルの値で保存の方法を教えてください。 おそれいります。こちらで数々のエ 6 2023/06/30 22:17
- Visual Basic(VBA) 複数指定セルの可視セルのみを別シートに転記するVBAについて 2 2022/05/27 21:19
- Visual Basic(VBA) ExcelVBAで、型が一致しませんのエラーについて 3 2023/06/20 09:51
- Excel(エクセル) 【Excel】指定のセル内容を基に別シートのセルを検索して選択する【VBA】 1 2022/06/16 16:16
- Excel(エクセル) エクセルでcsvファイルを開いてVBAを使いたい 7 2022/04/28 11:12
このQ&Aを見た人はこんなQ&Aも見ています
-
賃貸で可能な古民家風レトロな部屋作りのコツ!改めて知る畳の高い機能性と魅力も紹介
畳の部屋を雰囲気のよい部屋に仕上げたい!賃貸住宅でもできる古民家風のレトロな部屋作りのコツを伺った。
-
エクセルのVBAで日付を検索し転機したい
Visual Basic(VBA)
-
VBA 別シートの同じ日付の欄に値を貼付け
Excel(エクセル)
-
excel vbaで日付一致の行にデータ転記
Excel(エクセル)
-
-
4
(VBAにて)日付でデータを抽出するやり方
Excel(エクセル)
-
5
vba 2つの条件が一致したらコピーして別シートに値のみ貼り付け
Visual Basic(VBA)
-
6
今日の日付が入った行のデータを取得するマクロ
Excel(エクセル)
-
7
Excelで日付変更ごとに、自動的にデータを転記
Excel(エクセル)
-
8
ExcelVBAを使って、値がある場合は作業を繰り返し実行するプログラムを作成したい。
Visual Basic(VBA)
-
9
VBA 値と一致した行の一部の列のデータを転記について教えてください
Visual Basic(VBA)
-
10
エクセルVBA 4行飛ばしで転記するループ処理
Excel(エクセル)
-
11
VBAで繰り返しコピーしながら下へ移動させる方法
Excel(エクセル)
-
12
VBA 別ブック(シート)の同一日付のデータ欄に値をコピー
Visual Basic(VBA)
-
13
テキストボックス(VBA)でEnterを押したときに作動するマクロ
Access(アクセス)
-
14
エクセルVBAで、条件に一致するセルへ移動
Excel(エクセル)
-
15
Excel VBA あるセルでENTERを押すと特定のセルへ移動したい
Excel(エクセル)
-
16
メッセージボックスのOKボタンをVBAでクリックさせたい
Visual Basic(VBA)
-
17
指定したセルと同じ日付の入ったセルを選択するVBA
その他(Microsoft Office)
-
18
本日の日付とマッチした列の値を抽出
Excel(エクセル)
-
19
vbaで他のブックに転記したい。 vba初心者です。 コマンドボタンを押すと、別ブックにA2からD2
Visual Basic(VBA)
-
20
【VBA】指定した検索条件に一致したら別シートに転記したい
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
プログラミング
-
Excelのマクロについて教えてく...
-
VBA 別ブックからコピペしたい...
-
Excelのマクロについて教えてく...
-
エクセルVBAコードで教えて下さ...
-
Outlookの「受信日時」「件名」...
-
VBAコードについて教えてくださ...
-
vba アクティブシートの名前変...
-
ユーザーフォームに別シートか...
-
ExcelVBA シート名を複数セルか...
-
ExcelのVBAコードについて教え...
-
Outlookの「受信日時」「送信者...
-
エクセルのマクロについて教え...
-
FileCopy時のエラー
-
エクセルのマクロについて教え...
-
Excelのマクロについて教えてく...
-
VBA listBoxから
-
【ExcelVBA】インデックスが有...
-
エクセルVBAの配列について
-
配列のペースト出力結果の書式...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBAで大量のファイルをシート名...
-
VBA レジストリの値の読み方に...
-
Excelのマクロについて教えてく...
-
ユーザーフォームに別シートか...
-
VBAの計算で@が出てしまう件
-
エクセルVBAについて
-
Vba 実数および実数タイプの変...
-
【ExcelVBA】値を変更しながら...
-
VBA一覧取得 再投稿
-
VBA指定行削除
-
エクセルVBAについて
-
VBA ユーザーフォーム ボタンク...
-
VBA 何かしら文字が入っていたら
-
エクセルについて
-
2つのマクロでチェックボックス...
-
【マクロ】1つのマクロの中に...
-
ExcelのVBAコードについて教え...
-
VB.net(VB)で、フォームにExcel...
-
Vba SelStart、SelLen教えてく...
-
Excel-VBAのmsgBox()の不思議
おすすめ情報
S6に入ってるのが=IFERROR(INDEX($B$3:$H$15,MATCH(S$1,$B$3:$B$15,0),4),"")
S7に入ってるのが=IFERROR(INDEX($B$3:$H$15,MATCH(S$1,$B$3:$B$15,0),6),"")
トリガーですが、入力された時に実行できるのはとてもありがたいです
現在使っているINDEX,MATCH関数の表示をVBAでセルに関数を出さずにできる方法を模索しております。よろしくお願いします。助けてください。
追加質問2点目の1日2列から1日1列にしたいときのことを聞きましたがQchan1962様の組んでくれたプログラムをはめてみたら使えました。試さず質問し申し訳ありません。