EXCEL VBAについて

質問

いつも識者の皆様にはお世話になっております。
Excel VBAのことで質問させてください。

下記のようなシートがあります。

データの状態は下記です。
1.
1行目は必ずタイトル行になっています。

2.
1行目が「入荷_1」になっている列以降の日付はシリアル値となっています。
※必ずしもG1が「入荷_1」となっているとは限りません。H1だったりI1だったりします。

VBAでやりたいことは下記です。
1.
過去取引と現在取引の状態によってA列に入力する文字列を変える。
過去取引_1＝有　現在取引_1＝有　の場合　「OK」
過去取引_1＝有　現在取引_1＝無　の場合　「NG」
過去取引_1＝ブランク　現在取引_1＝有　の場合　「確認要」

2.
「入荷_1」以降の列で「入荷_1」「出荷_1」など、アンダーバー以降の文字列が同じになっている2列を一まとめとして、そのどちらかもしくは両方に日付の入っているセルを探し、その列の1行目の、アンダーバーの次の文字から最後の文字までを[]で囲んだ文字列をB列に入力する。
※_1にも_2にもある場合は、アンダーバーを使って文字列を連結する。(B2セル)
※パイナップルやぶどうのように入荷か出荷のいずれかにしか日付が入っていないものもある。

3.
タイトル行が「入荷_1」となっている列以降で日付の入っている列を探し、出荷から入荷を引いた数字をC列に入力する。
※_1にも_2にもある場合は、アンダーバーを使って文字列を連結する。(C2セル)

これを2行目から1行ずつ最後の行まで行い、かつ、このシートはブックにある複数のシートの中の1つですが、BA1セルが空白ではないシートにだけこの処理をしたいのです。

どうやらSelect Caseという構文を使えばできそうだというところまで突き止めたものの、条件が僕には複雑すぎて（特に2列を1まとめにするところ）まったく進みません。
どなたか、上記内容の場合どのような構文になるのか教えていただけませんでしょうか。
よろしくお願いいたします。

回答No.1

doredora · ベストアンサー

まず、記述をする前にプログラムを日本語で書いてみるのも方法です

もしBA1が空白でないなら実行
　もし、過去取引_1=有　で、現在取引_1=有　なら　A列「OK」
　もし、過去取引_1=有　で、現在取引_1=無　なら　A列「NG」
　もし、過去取引_1=ブランク　で、現在取引_1=有　なら　A列「確認要」
　それ以外は何もしない

入荷_1のある列を探す　取得列1
入荷_2のある列を探す　取得列2
もし、取得列1がブランク以外　または、取得列1の右列がブランク以外実行
もし、取得列2がブランク以外　または、取得列2の右列がブランク以外実行
　　B列は[1]_[2]
　　　C列は取得列1の右列－取得列1　_　取得列2の右列－取得列2
　それ以外は
　　B列は[1]
　　　C列は取得列1の右列－取得列1
それ以外
もし、取得列2がブランク以外　または、取得列2の右列がブランク以外実行
　　B列は[2]
　　　C列は取得列2の右列－取得列2
　それ以外は
　　B列はブランク
　　　C列はブランク

これを全てのレコードに繰り返す
という作文ができると思います
もし、入荷_2以上が存在しシートごとに入荷_5や入荷_8までというようにまちまちの場合は、事前に入荷_?　の個数を取得しておく必要がありますね
これを記述すると下記のようになります。
If Sheets("Sheet1").Range("BA1").Value <> "" Then
取得列_1 = Sheets("Sheet1").Range("A1:IV1").Find("入荷_1", lookat:=xlWhole).Column
取得列_2 = Sheets("Sheet1").Range("A1:IV1").Find("入荷_2", lookat:=xlWhole).Column
For K = 2 To 6
If Sheets("Sheet1").Range("E" & K).Value = "有" And Sheets("Sheet1").Range("F" & K).Value = "有" Then
Sheets("Sheet1").Range("A" & K).Value = "OK"
End If
If Sheets("Sheet1").Range("E" & K).Value = "有" And Sheets("Sheet1").Range("F" & K).Value = "無" Then
Sheets("Sheet1").Range("A" & K).Value = "NG"
End If
If Sheets("Sheet1").Range("E" & K).Value = "" And Sheets("Sheet1").Range("F" & K).Value = "有" Then
Sheets("Sheet1").Range("A" & K).Value = "確認要"
End If

If Sheets("Sheet1").Cells(K, 取得列_1).Value <> "" Or Sheets("Sheet1").Cells(K, 取得列_1).Offset(0, 1) <> "" Then
If Sheets("Sheet1").Cells(K, 取得列_2).Value <> "" Or Sheets("Sheet1").Cells(K, 取得列_2).Offset(0, 1) <> "" Then
Sheets("Sheet1").Range("B" & K).Value = "[1]_[2]"
Sheets("Sheet1").Range("C" & K).Value = Sheets("Sheet1").Cells(K, 取得列_1).Offset(0, 1) * 1 - Sheets("Sheet1").Cells(K, 取得列_1).Value * 1 & "_" & _
Sheets("Sheet1").Cells(K, 取得列_2).Offset(0, 1) * 1 - Sheets("Sheet1").Cells(K, 取得列_2).Value * 1
Else
Sheets("Sheet1").Range("B" & K).Value = "[1]"
Sheets("Sheet1").Range("C" & K).Value = Sheets("Sheet1").Cells(K, 取得列_1).Offset(0, 1) * 1 - Sheets("Sheet1").Cells(K, 取得列_1).Value * 1
End If
Else
If Sheets("Sheet1").Cells(K, 取得列_2).Value <> "" Or Sheets("Sheet1").Cells(K, 取得列_2).Offset(0, 1) <> "" Then
Sheets("Sheet1").Range("B" & K).Value = "[2]"
Sheets("Sheet1").Range("C" & K).Value = Sheets("Sheet1").Cells(K, 取得列_2).Offset(0, 1) * 1 - Sheets("Sheet1").Cells(K, 取得列_2).Value * 1
Else
Sheets("Sheet1").Range("B" & K).Value = ""
Sheets("Sheet1").Range("C" & K).Value = ""
End If
End If
Next K

End If

Select Case　を使われても良いとは思いますが、最初はIf文を使ったほうが構文を理解し易いように思います。
プログラムを実行するか、しないかの大きい括りの判断（セルBA1の状態）から順に見にいかせています。
質問の内容で書かれているいろいろな条件によって導く答えが複数提示されていますが、条件に合わない結果は何なのかを先に拾い出すことから始めると、複雑そうに思えることも意外と簡単にできたりします。
質問者様のような方が会社にいらっしゃると業務の効率化が進み、集計・分析など顧客への対応など企業のフットワークが軽くなるはずです。頑張ってください。