VBA セル内の数値を取得し、その数値の列番号を特定、そのセルの値を取得するマクロを教えてください

Question

こんにちは、
タイトルにあるようなマクロのコードをどのように書けば良いか悩んでいます。
どなかた詳しい方教えて頂きたく
言葉で言い表しにくいので　
エクセルのデータの例を下に示します。
　マクロ前	
　　　A   /     B   /    C   /     D    /      E    /     F    /     G    /    H    /     I    /     J     /    K 
1:  10:00 / 129　/　0　/　2300 /   4829 /  6253 /  8555 /  9200 /  4121 /  6332  /  7584  
2:   空白   /    1   /    2   /     3   /      8
3:   空白
4:   空白
5:   空白
6:  11:00 /  129   /   0   /   2432 /  9453  /  7234 / 1265  / 7888  /  3290 /  9833 /  8869
7:   空白　/    2   /    4   /     5      /    6
8:　空白
9:　空白
10: 空白
11:12:00  / 129   /   0   /    2543 / 5647  /  8994  / 2221 /  8763  / 1098 /  2275  /  9943 
12: 空白　 /　6     /   7  /       8　      
							
  マクロ実施後	
       A   / B  /   C		
1: 10:00  1  2300
2: 10:00  2  4829
3: 10:00  3  6253
4: 10:00  4  7584
5: 11:00  2  9453
6: 11:00  4  1265
7: 11:00  5  7888
8: 11:00  6  3290
9: 12:00  5  8763
10:12:00 6  1098
11:12:00 7  2275
12:12:00 8  9943 
							
マクロ前は								
2行一組3行置きにデータが並んでおり、2行目の数値は、1行目にある左から4列目のデータを1番目としてカウントしその後の列の個数にあるセルの場所を示しております。
マクロ後、
A列に時間、B列にデータの番号　C列にマクロ前に1行目の特定された数値記載する。
以上のようなコードをどなたか教えて頂きたく何卒よろしくお願い申し上げます。

tatsu99 · Accepted Answer

以下のマクロを標準モジュールに登録してください。

2行1組で4行から開始の前提です。（空白３行はないことになります）（この旨の回答をいただいていませんが想像でこのようにします）
2段目のデータ取得の為の値はC列から開始の前提です。
-----------------------------------------
Option Explicit

Public Sub 並べ替え()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim row1 As Long
Dim row2 As Long
Dim col1 As Long
Dim val As Long
Application.ScreenUpdating = False
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
row2 = 1
row1 = 4
sh2.Cells.ClearContents
Do While sh1.Cells(row1, 1).Value <> ""
col1 = 3
Do While sh1.Cells(row1 + 1, col1).Value <> ""
val = sh1.Cells(row1 + 1, col1).Value
If val > 0 And val < 130 Then
sh2.Cells(row2, "A").Value = sh1.Cells(row1, 1).Value
sh2.Cells(row2, "B").Value = val
sh2.Cells(row2, "C").Value = sh1.Cells(row1, val + 3).Value
row2 = row2 + 1
End If
col1 = col1 + 1
Loop
row1 = row1 + 2
Loop
Application.ScreenUpdating = True
MsgBox ("完了")
End Sub
----------------------------------------

めぐみん_ · Answer

No.12です。

あ～、空白行が存在していないって事であれば、そりゃ取得するセルの座標がずれるから、
デバッグエラーも起きますよ。

No.5で回答した中の
＞Set r = r.Offset(5)
は空白行を考慮してずらす物ですし、空白行３行がないなら
Set r = r.Offset(2)
にしてみたら良いでしょうし、その他数ヶ所を修正すればデバッグエラーも起きないかと。

＞何を表すコードなのでしょうか？
集計して書き出したい値を連想配列に追加していく物です。
ただ正解の回答が出ているのですから、Dictionaryオブジェクトについて調べてみては？とだけですかね。
・・・検証したデータにミスがあるのであれ自体意味はなくなってますし。
先にも申し上げたように、この手段は１０年ほど前に幾つかのサイトで回答されてた方が
良く用いたものでセルへの書き出しを纏めて行う方法です。

https://oshiete.goo.ne.jp/qa/10032799.html
こちらでそれは感じたのではないでしょうか？

今回速度に不満がなかったのなら、それで良しとしましょうよ。
結局は『どこのセルの値又はどんな値を書き出したいか？』を考えて配列にするだけのことです。

めぐみん_ · Answer

No.10です。

回答ではありません。

Ｅｘｃｅｌの画像を表示する際にＥｘｃｅｌそのものをもう少し小さくしておき（行・列数が少なくなるよう）、
Ｅｘｃｅｌが最前列に表示されている段階で
https://allabout.co.jp/gm/gc/462485/
・トリミングしてスクリーンショットを撮る方法
のように『Ｅｘｃｅｌだけ』の画像を作れるはずです。

ご参考まで。

tatsu99 · Answer

＞データのカウントはD列から開始します。
これは、2段目のデータの取得位置を示すデータは、D列から開始すると理解して良いですか。
（この数値の値が、D列を１としたときの上の行のデータの列を示すことは既に存じています。）
1: 10:00 / 129　/　0　/　2300 / 4829 / 6253 / 8555 / 9200 / 4121 / 6332 / 7584 
2: 空白 / 1 / 2 / 3 / 8・・・・・①
①の例でいえば、D列から開始なので、３と８の値が上のデータ位置を示しているということでしょうか。
また、①でD列以降の数値に1～１２９以外の数値がある場合は、その値を無視して処理するということでしょうか。

>このデータは、超音波のデータです。
>21行目も含め奇数行のデータは其々数字の意味が違います。
>例えば
>20行目は其々の時間に発生した音波のデータ　129個のデータ、其々の時間の音波の値
>21行目のデータは、C21（25番目のデータが許容外音波）D21（25番目の音波の角度）E21 （最大音波HZ）
>23行目のデータは、C21 ～E21（許容外音波が25、70、71番目にある）F23 ～H23（其々の角度）
>I23～K23（其々の最大音波Hz）
>となっております。
>ですので129以下の数値のデータから許容外音波の発生箇所を取得したく

まったく、レイアウトがどうなっているのか判らなくなりました。
そもそも、2行1組で、そのあと空白行が３行の前提だったはずです。（空白行の箇所はスキップするので空白でなくても良いですが・・・）
そうすると、
４行と５行
9行と10行
14行と10行
19行と20行
でこのケースでは、
19行が時間とその時間の音波
20行が取得位置を示すデータ
のはずですが、そのようには、見えません。

最初に提示された
「2行一組3行置きにデータが並んでおり、2行目の数値は、1行目にある左から4列目のデータを1番目としてカウントしその後の列の個数にあるセルの場所を示しております。」というのは、誤りでしょうか。

めぐみん_ · Answer

No.8です。

補足等がないと言うことは、私は必要ないようなので失礼します。
今回のはマクロが難しいのではなく、正確な情報に基づく検証が回答者側で出来なかったからですし。
あのコードの基礎は１０年も前に普通に使われていた物です。

tatsu99 · Answer

＞最初の部分でデータが有ったり無かったりします。
これは、最初に提示すべきですね。

確認ですが、提示された例では
1段目を時刻のあるデータと呼ぶとして
2段目で取得位置を表すデータはB列から開始するように見えましたが、C列から開始するのですか。
1: 10:00 / 129　/　0　/　2300 / 4829 / 6253 / 8555 / 9200 / 4121 / 6332 / 7584 
2: 空白 / 1 / 2 / 3 / 8・・・①
の例をみると、①の１の値はB列のように見えます。

めぐみん_ · Answer

No.5です。

＞データの内容が変わった場合、いつもここでデバッグが発生します。

この詳細がわからないとコードを説明しても、そもそもコード自体が合わない可能性も考えられますし。
なのでNo.6の方の回答で検証してみてどうなのか？ですかね。
多分情報（こちらで検証した内容）と実際のデータが違うと思います。

例えばＡ列はＢ列以降にデータがなくても、先に入力され値が存在すればデバッグは起きますよ。
その場合どの行まで実行させるかをどう決めるか考えなければならないですし。

他には２段目の値で『重複』があるとダメですけど。

tatsu99 · Answer

>No.5の方と違ったコードってあるのでしょうか？
>教えて頂ければ幸甚です。

提供は可能ですが、その前に、No5の方のデータでエラーが発生するのは、E21の値が19719のような非常に大きな値だからではないでしょうか。本当にこの値は、正しいのでしょうか。

tatsu99 · Answer

No2です。
No5の方の回答がででますので、私のほうからの回答は控えます。
もし、No5の方の回答以外を望まれる場合は、その旨補足ください。

めぐみん_ · Answer

No.4です。

質問文にある元のデータを４行目にずらして以下を実行。

Sub try()
　Dim myDic As Object
　Dim r As Range, rr As Range

Set myDic = CreateObject("Scripting.Dictionary")

With Worksheets("Sheet1") ' データのあるシート
　　Set r = .Range("A4") ' 最初のデータのあるＡ列のセル
　　Do Until r.Value = ""
　　　For Each rr In .Range(r.Offset(1, 1), .Cells(r.Row + 1, Columns.Count).End(xlToLeft))
　　　　myDic.Add r.Row & "_" & rr.Value, Array(r.Text, rr.Value, r.Range("C1").Offset(, rr.Value).Value)
　　　Next
　　　Set r = r.Offset(5)
　　Loop
　End With

With Worksheets("Sheet2") ' データを書き出すシート
　　.Cells.ClearContents ' 以前のデータを削除★
　　.Range("A1").Resize(myDic.Count, 3).Value = _
　　Application.Transpose(Application.Transpose(myDic.Items))
　End With

Set myDic = Nothing
　Set r = Nothing
End Sub

すると結果は、

10:00	1	2300
10:00	2	4829
10:00	3	6253
10:00	8	7584
11:00	2	9453
11:00	4	1265
11:00	5	7888
11:00	6	3290
12:00	6	1098
12:00	7	2275
12:00	8	9943

で、先の回答者の指摘などのように違う箇所が出てきます。

＞また、マクロ実施後も同じく、 4行目は 10：00 / 4 / 7584
＞です。

10:00の下段には『４』はないです。
もしそれが正しいのなら明確な判断基準を提示して頂かないと。

VBA セル内の数値を取得し、その数値の列番号を特定、そのセルの値を取得する マクロを教えてください

以下のマクロを標準モジュールに登録してください。

No.12です。

No.10です。

＞データのカウントはD列から開始します。

No.8です。

＞最初の部分でデータが有ったり無かったりします。

No.5です。

>No.5の方と違ったコードってあるのでしょうか？

No2です。

No.4です。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

VBA セル内の数値を取得し、その数値の列番号を特定、そのセルの値を取得するマクロを教えてください