マクロの作り方について

質問

お世話になっております。
エクセルで次のようなテーブルの自動生成マクロを作成したいと考えておりますが、なかなかうまくいきません。
皆さまのお知恵を拝借いたしたく、お願いいたします。
「Data」シート、「Table」シートの２つのシートをエクセルに用意します。
「Data」シートには、200行2列のデータが予め入っています。
1列目にはaaa.F、aaa.W、aaa.R、bbb.F、bbb.W、bbb.Rといった形の文字列が延々と入っています。
2列目には数字のデータが入っています。
行いたいのは、まず1列目のデータを「.」で区切って、２つの配列「st」と「pr」に入れなおすことです。
ただ、単にsplitするだけですと、1列目のデータを分けた際に、
st=(aaa、aaa、aaa、bbb、bbb、bbb)
pr=(F、W、R、F、W、R)
といった形で同じ値が格納されますが、ダブっている文字ははじく様にしたいのです。
このため、例えばstについて、次のようなマクロを考えたのですが、これだとstをdebug.Print等で出力した際、一番最後のデータだけしか格納されていませんでした。

Dim C As Variant
C = Worksheets("DATA").Range("A1:B65000")
Dim D As Variant
Dim i As Integer
Dim st As Variant
For i = 1 To 10
D = Split(C(i, 1), ".")
If st <> D(0) Then
st = D(0)
Debug.Print (st)
　↑ここでstを出力している分には、aaa、bbb、cccとダブった文字を省いたすべてのデータが出力されます。
Else
End If
Next
Debug.Print (st)
　↑ここでstを出力すると、最後のデータだけしか出力することができません。
　st(3)等、特定の要素を出力しようとしてもできないようです。

stをvariant型にしていることが問題なのかもしれませんが、stをstring型のarrayで定義し、各要素ごとに入力すると、st(0)=aaa、st(1)=aaaといった形で同じ値のものも入ってしまいます。
異なった文字列だけ各要素に入力していくにはどのようにマクロを組めばよいのでしょうか？

回答No.3

imogasi · ベストアンサー

#2です。
もう一度私の手元のパソコンのデータで、下記を実行しましたが、エラーが出ず、うまく行くようです。
原因追及の前に、＃１の回答のシートのデータを使うこと（質問者の実際のデータを使わず）で、下記を標準モジュールに貼り付けて（下記をコピペして、そっくりそのままで）、もう一度実行してみてもらえませんか。
実行前に、aaa.Fなどの間のカンマについて（A列ある行で漏れていないか）チェックしてみてください。
Sub test06()
Dim st(10): Dim pr(10)
x = 0: y = 0
Dim rng As Range
Dim d As Variant
lr = Worksheets("DATA").Range("A1000").End(xlUp).Row
'MsgBox lr
'-----
For i = 1 To lr
'MsgBox Cells(i, "A")
d = Split(Cells(i, "A"), ".") 'Ａ列各行セルのデータをスプリット
'---st について
For j = 1 To x
If st(j) = d(0) Then
GoTo p1 '探索終り
Else
'何もしない
End If
Next j
'最終的にd(0)はst()に見つからず、配列に追加
x = x + 1
st(x) = d(0)
p1:
'---prについて
For j = 1 To y
If pr(j) = d(1) Then
GoTo P2 '探索終り
Else
'何もしない
End If
Next j
'最終的にd(0)はpr()に見つからず、配列に追加
y = y + 1
pr(y) = d(1)
P2:
'---次行データへ
Next i
'---処理終了
For i = 1 To x
MsgBox "stの" & i & "=" & st(i)
Next i
For i = 1 To y
MsgBox "prの" & i & "=" & pr(i)
Next i
End Sub
すぐに思い当たるエラーが、みつけられずで、すみません。
結果をお聞かせください。

お礼

お手数おかけいたしまして申し訳ございません。
私のDataタブの中でイレギュラーなデータがあったのか、aaa.F、aaa.W等手入力で入れた10行程度のデータではご連絡いただいたコードで問題なく目的が達成できました。
何度もご連絡いただきまして誠にありがとうございます。
ここまでできれば、あとは自分でもできそうです。
こちらをベストアンサーとして閉めさせていただきたいと思います。
ありがとうございました。

回答No.2

imogasi

#1です。セルを中間記録域として使わず、Ｆｉｎｄも使わず、逐次比較（Ｉｆ）だけでやりました。
Sub test06()
Dim st(10): Dim pr(10) '１０は結果を見通して、適当に見積もること
x = 0: y = 0　 '最初の要素は、＋１してから、セットするので、１からになります
Dim d As Variant
lr = Worksheets("DATA").Range("A1000").End(xlUp).Row 'データのある最終行探知
'MsgBox lr
'-----
For i = 1 To lr
'MsgBox Cells(i, "A")
d = Split(Cells(i, "A"), ".") 'Ａ列各行セルのデータをカンマでスプリット
'---st について
For j = 1 To x
If st(j) = d(0) Then
GoTo p1 'すでに存在するので探索終り
Else
'何もしない
End If
Next j
'最終的にd(0)はst()に見つからず、配列に追加
x = x + 1
st(x) = d(0)
p1:
'---prについて
For j = 1 To y
If pr(j) = d(1) Then
GoTo P2 'すでに存在するので探索終り
Else
'何もしない,配列次要素と比較へ
End If
Next j
'最終的にd(0)はst()に見つからず、配列に追加
y = y + 1
pr(y) = d(1)
P2:
'---次行データへ
Next i
'---処理終了
For i = 1 To x
MsgBox "stの" & i & "=" & st(i)
Next i
For i = 1 To y
MsgBox "prの" & i & "=" & pr(i)
Next i
End Sub
後はｓｔ、ｐｒを配列化して、似たようなルーチンを繰り返さないようにする課題は
あると思う。
上記は質問者がｓｔ、ｐｒと個別の配列の名前を付けていたので、それを尊重したもの。

お礼

面倒な依頼にご対応いただきまして誠にありがとうございます。
ご連絡いただきましたマクロでst(x)=d(0)の部分で「インデックスが有効範囲にありません」となってしまい、どうもdをd(0)あるいはd(1)と指定していることでエラーが出ているようなのですが、こちらはどのように指定したらよろしいでしょうか？

回答No.1

imogasi

本回答は、たとえば、Ｇ，Ｈ列を中間作業列で使います。
例データ　Ａ１：Ｂ８
aaa.F 12
aaa.W 23
aaa.R 45
bbb.F 65
bbb.W 43
bbb.Q 22
bbb.R 22
ccc.D 22
ーーー
Sub test05()
Dim st(10): Dim pr(10)
x = 1: y = 1
Dim rng As Range
Dim d As Variant
lr = Worksheets("DATA").Range("A1000").End(xlUp).Row
'MsgBox lr
'-----
For i = 1 To lr
'MsgBox Cells(i, "A")
d = Split(Cells(i, "A"), ".") 'Ａ列各行セルのデータをスプリット
'---
Set rng = Worksheets("DATA").Range("G:G").Find(what:=d(0))
If rng Is Nothing Then
lg = Worksheets("DATA").Range("G1000").End(xlUp).Row
Cells(lg + 1, "G") = d(0)
st(x) = d(0)
x = x + 1
End If
'---
Set rng = Range("H:H").Find(d(1))
If rng Is Nothing Then
lh = Worksheets("DATA").Range("H1000").End(xlUp).Row
Cells(lh + 1, "H") = d(1)
pr(y) = d(1)
y = y + 1
End If
Next i
'--配列の-結果確認
'st = Range("G:G")　'ダメ
For i = 0 To x - 1
MsgBox st(i)
Next i
For i = 0 To y - 1
MsgBox pr(i)
Next i
End Sub
ーーー
結果　Ｇ：Ｈ列
aaa F
bbb W
ccc R
Q
D
ーーー
配列にも、上記の確認でわかるように、ｓｔ、ｐｒに結果はセットできてています。
ただし、本回答はＦｉｎｄを使う以上、Ｇ，Ｈ列は省けないやり方です。
このやり方は、ロジックを作り配列内だけでふるいにかけるより、コードが初等的で簡単で、わかりやすいと思っています。ＲｅＤｉｍなどの問題も起こります。
ーー
配列のＦｉｌｔｅｒなどやってみましたが、適当でないようです。既存のＣＣＣに対しＣも「存在する」、になるようだ。
夜遅いので、十分検討ができてません。配列のＦｉｎｄとか（多分不可）、セルから配列に入れるとか、など。

お礼

夜遅くに回答を考えていただきましてありがとうございます。
解説も分かりやすく書いていただきまして助かります。
"DATA"シートのGH列は使用する予定がないので、GH列を使用して問題ないのですし、後で消してしまえばよいのですが、取り扱うデータ量が多いので、できれば最終結果を配列に格納する所まではマクロ内で済ませたいと考えています。
もしマクロ内で納める方法がございましたらご教授いただけますと幸いです。