VBA配列 印のついた列番号を格納して利用したい

質問

Excel VBAで下記処理を作成しました。配列を使えば処理が高速化できるのでは？と思い調べたのですが、配列を使ったことが無く、挫折しましたのでどなたかご教示いただけませんでしょうか。

＜対象データ＞（添付画像の黄色いセルが処理対象）
・処理対象の行は「区分」列に「0」が入っている
・処理対象の列は、1行目に「●」が入っている

＜作成した処理＞
・ループ(1)：データ開始行（画像では3行目）から順に見ていく
　・IF：「区分」列が「0」なら下記に進む（0以外なら次の行へ）
　　・ループ(2)：列を左から順に見ていく
　　　・IF：その列の1行目が「●」なら処理を行う

For i = 3 To 10000
If Cells(i , 1) = 0 And Cells(i , 1) <> "" Then
その行に対する処理の下準備のコード（記載省略）
For j = 2 To 500
If Cells(1 , j) = "●" Then
Cells(i , j)に対する処理（記載省略）
End If
Next j
End If
Next i

＜問題点＞
データ量が数千行×数百列と相応にあるため、処理実行に数秒かかります。今後、処理をより複雑にしたいため、高速化できないかと考えています。

＜質問＞
対象行について毎回、ループ(2)で各列の1行目を調べて●だったら処理する、としているのが非効率かも知れないと思いました。
1行目に●が入っている列番号を調べるのは1度で良いので、それを配列に格納して、ループ(2)の部分で使うことはできますでしょうか？

ご存じの方、ご教示いただけますと幸いです。よろしくお願いいたします。

回答No.6

kkkkkm · ベストアンサー

No5は間違って違うものをコピペしてしましました。無視してください。

お礼

お陰様で当初やりたかったことは下記コードで達成できました。有難うございました！

n = WorksheetFunction.CountIf(Range(Cells(1,1),Cells(1,500)), "●")
ReDim mCol(n) '動的配列mCol()の要素数＝●の数に再定義

For i = 1 To 500
If Cells(1, i) = "●" Then
k = k + 1 '動的配列の0番目は使わない
mCol(k) = i '動的配列のk番目に列番号を格納
End If
Next i

For i = 3 To 10000
If Cells(i , 1) = 0 And Cells(i , 1) <> "" Then
その行に対する処理の下準備のコード（記載省略）
For k = 1 To n
Cells(i , mCol(k))に対する処理（記載省略）
Next k
End If
Next i

※上記では簡略化のため、実際は変数で持っている部分も実数で書いています（セル範囲など）
※セル範囲を配列buf()に入れるという工夫も、今後試してみます。

回答No.5

kkkkkm

No1を以下のように変更してもいいような気がします。

Sub Test()
Dim buf As Variant
Dim i As Long, j As Long
Dim mCount As Long

buf = Range("A1:F12")
For j = 3 To 12
If buf(j, 1) = 0 Then
'その行に対する処理の下準備のコード
For i = 1 To 6
If buf(1, i) = "●" Then
Cells(j, i) = "処理"
End If
Next
End If
Next
End Sub

お礼

ありがとうございました

回答No.4

kkkkkm

下準備のコードの存在を忘れてました。

For j = 3 To 12
If Cells(j, 1) = 0 And Cells(j, 1) <> "" Then
'その行に対する処理の下準備のコード（記載省略）
For i = 1 To k
Cells(j, mCol(i)).Value = "処理"
Next i
End If
Next j

お礼

ありがとうございました

回答No.3

kkkkkm

> ・1行目を左から順に見て「●」が入っている列が【処理対象列】→ 結果（画像で「3・5・6列目」）を配列に格納

buf = Range("A1:F1")
n = WorksheetFunction.CountIf(Range("A1:F1"), "●")
ReDim mCol(n)
For i = 1 To Range("A1:F1").Columns.Count
If buf(1, i) = "●" Then
k = k + 1
mCol(k) = i
End If
Next

の部分です。mCol(k)に3・5・6が順に入ります。
列データを全て配列にいれたいのでしたら

tmp=Cells(行数,mCol(k))

でtmpに入ります。

> ・ループ(1)：データ開始行（画像では3行目）から順に見ていく
> 　・IF：「区分」列が「0」なら下記に進む（0以外なら次の行へ）
> 　　　・その行の【処理対象列】に対して処理を行う

行と列のループを入れ替えたらいかがですか

For j = 3 To 12
If Cells(j, 1) = 0 And Cells(j, 1) <> "" Then
For i = 1 To k
Cells(j, mCol(i)).Value = "下処理"
Next i
End If
Next j

> 「If buf(j, 1) = 0」の判定を「For j = 3 To 12」の回数分繰り返すことになりますが、これを1回だけで済ませられないか

すみません、「IF：「区分」列が「0」なら下記に進む（0以外なら次の行へ）」の処理を1回でというのがわかりません。

お礼

ありがとうございました

補足

ありがとうございます。bufとmColが配列なのですね。少しずつ理解してみて、やりたいことに適用できるか試してみます。

回答No.2

kkkkkm

> 「各行の処理の下準備」ができなくなる

buf(j, i) = "処理" の部分で処理すればいかがでしょう。
画像の場合だと下準備の回数は9回です。

極力セルにアクセスしないようにすると処理が早くなると思います。

> ループ(1)の前で●の列を調べた結果（画像例であれば「3・5・6列目」）を配列に格納し

一例です。

Sub Test2()
Dim i As Long, j As Long, k As Long, n As Long, mCol As Variant
Dim buf As Variant
Dim mCount As Long

buf = Range("A1:F1")
n = WorksheetFunction.CountIf(Range("A1:F1"), "●")
ReDim mCol(n)
For i = 1 To Range("A1:F1").Columns.Count
If buf(1, i) = "●" Then
k = k + 1
mCol(k) = i
End If
Next
For i = 1 To k
For j = 3 To 12
If Cells(j, 1) = 0 And Cells(j, 1) <> "" Then
Cells(j, mCol(i)).Value = "下処理"
End If
Next j
Next i
End Sub

お礼

説明が下手ですみません。元の質問に沿った補足説明です。

＜作成した処理＞
・ループ(1)：データ開始行（画像では3行目）から順に見ていく
　・IF：「区分」列が「0」なら下記に進む（0以外なら次の行へ）
　　・ループ(2)：列を左から順に見ていく
　　　・IF：その列の1行目が「●」なら処理を行う

＜こういう処理はできないか？＞
・1行目を左から順に見て「●」が入っている列が【処理対象列】→ 結果（画像で「3・5・6列目」）を配列に格納
・ループ(1)：データ開始行（画像では3行目）から順に見ていく
　・IF：「区分」列が「0」なら下記に進む（0以外なら次の行へ）
　　　・その行の【処理対象列】に対して処理を行う

配列の使い方が解っていないので、後者はロジカルな書き方になっていないと思います。それとも、配列ではこういうことはできませんでしょうか。

作業対象のセル範囲を一旦配列に格納してからアクセスした方が高速になる、というのは解ります。それはそれで取り込みたいですが、1行目に●が入っている列は1回調べれば判っているのに、作業対象行で一々1行目を見に行くのは非効率ではないか、という観点です。
よろしくお願いいたします。

補足

kkkkkm様
再度ご回答ありがとうございます。質問の＜問題点＞に記載しました通り、データは数千行ありますので、その方法だと下準備処理を数千回繰り返すことになります（画像はあくまで例示です）

一旦、元の質問も下処理云々も忘れていただいて、1回目のご回答で書いていただいた下記コードで再質問差しあげます。
下記では「If buf(j, 1) = 0」の判定を「For j = 3 To 12」の回数分繰り返すことになりますが、これを1回だけで済ませられないか、というのが元の質問の趣旨です。
（また行列の処理順を入れ替えるのはナシです。入れ替えると元の質問に戻ります。）

Sub Test()
Dim buf As Variant
Dim i As Long, j As Long

buf = Range("A1:F12")
For i = 1 To 6
If buf(1, i) = "●" Then
For j = 3 To 12
If buf(j, 1) = 0 Then '質問者メモ：ここを効率化したい
Cells(j, i).Interior.Color = vbYellow
'buf(j, i) = "処理"
End If
Next
End If
Next

回答No.1

kkkkkm

画像の状態のようにセルの色を黄色にする場合の一例です。

配列 buf(j, i)はbuf(行,列 )に対応します。
データの無いセルの値は0になります。
Sub Test()
Dim buf As Variant
Dim i As Long, j As Long

buf = Range("A1:F12")
For i = 1 To 6
If buf(1, i) = "●" Then
For j = 3 To 12
If buf(j, 1) = 0 Then
Cells(j, i).Interior.Color = vbYellow
'buf(j, i) = "処理" 'セルに値を代入する場合こちらで
End If
Next
End If
Next

'セルに値を代入する場合こちらで
'Range("a1:f12").Value = buf

End Sub

お礼

ありがとうございました

補足

kkkkkm様
ご回答ありがとうございます。
しかしこれですと、区分列=0の全列について1行目=●を調べていたものを、1行目=●の全行について区分列=0ように、縦横の処理順を入れ替えただけで、かつ質問のコードに書きました「各行の処理の下準備」ができなくなる（する場合は膨大な繰り返しが発生する）と思います。

質問の書き方が悪かったのですが、ループ(1)はそのままにして、ループ(1)の前で●の列を調べた結果（画像例であれば「3・5・6列目」）を配列に格納し、ループ(2)ではその配列から取り出して処理対象セルを特定する、という動きはできませんでしょうか？
よろしくお願いいたします。