VBA 月の選択により表示の値を変わる ループ

昨日の質問ですが、ちょっと詳しく説明させていただきます。
VBAの初心者、初めてのコーディングです。
ネットで調べつつ、悩みながらゆっくりソースを書いております。
宜しくお願い致します。

sheet1のセルA1は１月～１２月のプルダウンです。

sheet1のセルA1に６月を選ぶ時、
sheet2のI40、I54、I68、I82...の値をsheet1のD3～D9にセットします。
このセルとセルの紐づきと値の取得処理は終わってます。
こんな感じで値をセットしております。
「if ws.range("A1").Value="6月"　then
ws.range("D3").Value=application.round("sheet2".range("I40").value/100000,1)
ws.range("D4").Value=application.round("sheet2".range("I54").value/100000,1)
...」
実現したいのは次に
sheet1のセルA1に
７月を選ぶ時、セルに値を一つ一つセットではなくて、
列+１、J列の値を取得する。
J40、J54、J68、J82...
８月を選ぶ時、K列の値を取得する。
列+２
K40、K54、K68、K82...
９月を選ぶ時、L列の値を取得する。
列+3
L40、L54、L68、L82...
…

また、月を選ぶことによって取得データは変わる時、IF、IF　ELSEの分岐分を使いますか？

こんな機能をソースをどうやって書けばよいでしょうか。
説明がうまくできず申し訳ございません。
皆様のご知恵を借りていただきたいと思います。
宜しくお願い致します。

No.1 ベストアンサー 回答者： tatsumaru77 回答日時： 2021/07/29 10:17

以下のようにしてください。

dim tukiは1月から12月の月の文字をとった数値1～１２ が格納されます。
dim colは1月から12月に対応したSheet2の列の値を格納します。
４月の場合、G列＝７
５月の場合、H列＝８
・・・
１２月の場合、O列＝１５
１月の場合、P列＝１６
・・・
３月の場合、R列＝１８
となるようにcolに値を設定します。
月が4月以降なら、月＋３
月が３月以前なら、月＋１５
が求めるcolの値です。

ｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰｰ
Public Sub 月対応設定()
Dim tuki As Variant '月を表す数値 1～12
Dim col As Long '列を表す数値　4月=7,5月=8,,12月=15,1月=16,,

Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
If ws.Cells(1, 1).Value = "" Then Exit Sub
tuki = Left(ws.Cells(1, 1).Value, Len(ws.Cells(1, 1).Value) - 1)
If tuki < 4 Then
col = tuki + 15
Else
col = tuki + 3
End If
ws.Range("D3").Value = Application.Round(ws2.Cells(40, col).Value / 100000, 1)
ws.Range("D4").Value = Application.Round(ws2.Cells(54, col).Value / 100000, 1)
End Sub

No.2 回答者： めぐみん_ 回答日時： 2021/07/29 10:20

質問を立てなおすなら前の質問はきちんと閉じましょう。

＞７月を選ぶ時、セルに値を一つ一つセットではなくて、
＞列+１、J列の値を取得する。

これですと基準の列はＩ列となり”６月”と言う事になりますが？？？

結局
・４月ならＧ列（列番号：７）
・５月ならＨ列（列番号：８）
・６月なら９
・７月なら１０

と月＋３で列番号が取れますよね。
⇒１～３月はどうなっているかで補正をかける必要もあるようですし。

それに『Combobox』ではなく『入力規則』を用いているのでは？

と、Excel－ＰＣから離れる初級レベルなジジィはここで停まりますけど。

＞sheet2のI40、I54、I68、I82...の値をsheet1のD3～D9にセットします

もう一つの背景に色を付けてるセル群は何か意味が？

＞また、月を選ぶことによって取得データは変わる時、IF、IF　ELSEの分岐分を使いますか？

上記に書いたように月数を利用して列番号を取得する手段を取るなら判定式は不要かと。

No.3 回答者： fujillin 回答日時： 2021/07/29 10:23

こんにちは

同じ質問を（締め切りもせずに）繰り返すのは、回答が分散するだけでなく、同じような回答も繰り返されるので、得なことはありません。
https://oshiete.goo.ne.jp/qa/12485040.html
数少ない情報であったセルの位置も変わっているようですし、回答を得ても実際のセル関係と異なる場合は、ご自身でそれを読み解くだけの技量が必要になります。

前回も回答しましたけれど、規則的に同じセルを参照するだけの内容なので、関数を設定しておいた方が、いちいちマクロを実行する必要もなくなります。
ですので、特別な理由がない限りは、この方法一番のお勧めです。


・・・などと言ったところで、多分、マクロに拘るのでしょうから、そちらの例もご参考までに。
（上記の関数での解決法も兼ねたつもりですが、意味が伝わるかどうかは質問者様次第なので不明。）

f = "=ROUND(OFFSET(Sheet2!$C$26,ROW(A1)*14,LEFT($A$1,LEN($A$1)-1))/100000,1)"
With Worksheets("Sheet1").Range("D3:D9")
.FormulaLocal = f
.Value = .Value
End With

No.4 回答者： Qchan1962 回答日時： 2021/07/29 11:12

こんにちは

＃３様に同感ですが、どうしましょう、、
https://oshiete.goo.ne.jp/qa/12483964.html
こちらは、どうするのでしょう？

限定的な処理コードになりますが、やり方は色々あります

＞sheet1のセルA1に７月を選ぶ時
この実行トリガーなら
sheet1のシートモジュールに
Private Sub Worksheet_Change(ByVal Target As Range)
で実行します。
sheet2の1月はD列から始まっているのかな？
結合セルはいただけないです。。
抽出、代入セルは固定で良いのでしょうか、、Offsetなどで直せるかな

どうも、ドロップダウンとおしゃったのは入力規則のようですね、、
理解できるか出来ないかは、判りませんが　ご質問の表組で
＞＞
sheet1のセルA1に
７月を選ぶ時、セルに値を一つ一つセットではなくて、
列+１、J列の値を取得する。
J40、J54、J68、J82...
８月を選ぶ時、K列の値を取得する。
列+２
K40、K54、K68、K82...
９月を選ぶ時、L列の値を取得する。
列+3
L40、L54、L68、L82...
＞＞
列は何を指しているのか不明ですが
＞値をsheet1のD3～D9にセットします。
するプログラムです。
雑ですが、検索パターン
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Cl As Range, n As Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Worksheets("Sheet2")
Set Cl = .Range("D30:P30").Find(Target)
If Not Cl Is Nothing Then
Application.EnableEvents = False
For i = 40 To 124 Step 14
Cells(3 + n, "D").Value = .Cells(i, Cl.Column).Value
n = n + 1
Next
Application.EnableEvents = True
End If
End With
End If
End Sub

注意　上記は月のセル範囲に結合セルがあるとエラーになります。
結合セル対策をすれば良いですが、表組作成途中のようなので割愛

雑ですが、入力数値（数値部分を取り出す）パターン
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, Cl As Integer, n As Long
Dim Ary
If Not Intersect(Target, Range("A1")) Is Nothing Then
Cl = CInt(numEx(StrConv(Target.Text, vbNarrow)))
If Cl < 1 Then Exit Sub
With Worksheets("Sheet2")
Application.EnableEvents = False
For i = 40 To 124 Step 14
Cells(3 + n, "D").Value = .Cells(i, Cl + 3).Value
n = n + 1
Next
Application.EnableEvents = True
End With
End If
End Sub
Function numEx(strV As String) As String
Dim i As Integer, numText As String
For i = 1 To Len(strV)
numText = Mid(strV, i, 1)
If numText Like "[0-9]" Then numEx = numEx & numText
Next i
End Function

補：Cl + 3はD列から始まっているようなので、、

この回答へのお礼

分かりやすくご説明いただき、
誠にありがとうございます。

お礼日時：2021/07/29 14:37