EXCEL VBAの配列でわかりません。

質問

こんなコードがあるのですが、最後の他のシート（作業中シート）に書き込もうとするとエラーになってしまいます。”Sheets("作業中").”を抜くと同じシートに結果は返ってくるのですが…。コードの内容は、ある範囲のある列から空白ではないセルを探し出してその行のデータを配列で汲み取り、他のシートに一括で洗い出すというものです。

Sub 作業中()

Dim myRow As Long
Dim Data As Variant
Dim WC() As Variant
Dim WCE() As Variant

myRow = Range("H1").CurrentRegion.Rows.Count
Data = Range("H1:M" & myRow).Value

For i = 1 To myRow
If Data(i, 5) <> "" Then
a = a + 1
Else
b = b + 1
End If
Next

ReDim WC(a)
ReDim WCE(b)
c = 0
d = 0
For i = 1 To myRow
If Data(i, 5) <> "" Then
WC(c) = Range("H" & i & ":K" & i).Value
c = c + 1
Else
WCE(d) = Range(Cells(i, 8), Cells(i, 11)).Value
d = d + 1
End If
Next

For i = 0 To a
Range(Cells(i + 1, 15), Cells(i + 1, 18)).Value = WC(i)
Next
For i = 0 To b
Range(Cells(i + 1, 19), Cells(i + 1, 22)).Value = WCE(i)
Next

e = Range(Cells(1, 15), Cells(a, 18)).Value
Sheets("作業中").Range(Cells(1, 1), Cells(a, 4)).Value = e

End Sub

ちなみに同じシートから↓のコードを実行するとうまくいきます。
なぜ～？？わからな～い？？おしえてくださーい！！
Sub test()

Dim a As Variant
a = Range("H1:K4")
Sheets("作業中").Range("N1:Q4") = a

End Sub

回答No.3

Wendy02 · ベストアンサー

こんばんは。

Sheets("作業中").Range(Cells(1, 1), Cells(a, 4)).Value = e
と言う場合、Cellsプロパティは、マクロを実行しているActiveSheetの属性のままだから、エラーが発生します。

そのような場合は、With ステートメントでつないであげます。
Range オブジェクトは、Cells を直接、配下にはしていませんから、こういう問題が発生します。たぶん、VBAの文法を作るときには、こんなことは想定していなかったに違いありません。

こういう高級文法を用いた構文では、よほどのことがない限りは、シート　Select やWorkbook の Activate は用いません。

With Sheets("作業中")
　　.Range(.Cells(1, 1), .Cells(a, 4)).Value = e
End With

ところで、私個人も、同じ内容で考えてみました。配列がジャグになっていますので、少し、ややこしいです。このような場合は、Sort オブジェクトを使っても良いのですが、それも、また面倒です。

本来は、Copy メソッドとかを使い、もう少し基本的なコードで書いたほうがよいかもしれません。また、同じ局面の中で、Range 型と Cells 型の混在は、非常に読みにくいです。どちらかに統一したほうがよいです。今回のようなミスを誘発します。Range型で書く場合は、Resize を使うと便利です。

--------------------------------------

Sub 作業中2()
　　Dim WC() As Variant
　　Dim WCE() As Variant
　　Dim i As Long
　　Dim n As Long, m As Long
　　Dim x As Long, x1 As Long
　　Dim y As Long, y1 As Long

　　With Range("H1").CurrentRegion
　　　　For i = 1 To .Rows.Count
　　　　　　If .Cells(i, 5).Value <> "" Then
　　　　　　　　ReDim Preserve WC(n)
　　　　　　　　WC(n) = .Rows(i).Resize(, 4)
　　　　　　　　n = n + 1
　　　　　　Else
　　　　　　　　ReDim Preserve WCE(m)
　　　　　　　　WCE(m) = .Rows(i).Resize(, 4)
　　　　　　　　m = m + 1
　　　　　　End If
　　　　Next i
　　End With
　　x1 = UBound(WC())
　　y1 = UBound(WCE())
　　For x = 0 To x1
　　　　Range("O1").Offset(x).Resize(1, 4).Value = WC(x)
　　Next x
　　For y = 0 To y1
　　　　Range("S1").Offset(y).Resize(1, 4).Value = WCE(y)
　　Next y

　　Worksheets("作業中").Range("A1").Resize(x1 + 1, 4).Value = _
　　Range("O1").Resize(x1 + 1, 4).Value
End Sub

お礼

ありがとうございます！
なるほど！！
配列を使うのは今回が初めてだったのでとても勉強になります！！
RedimをPreserveにしてループに取り込むといいんですね。
あと、cellsプロパティの件もよーく考えてみると、はっ！！と落とし穴に気づきました。今後気をつけていきたいと思います。

なぜCOPYメソッドやら基本的なコードを使わないようにしているかといいますと、データの量が増えてきて処理に時間がかかり始めたため、なんとかいい方法がないかと模索していた矢先、配列を使うと処理が軽くなることがわかったためです。

前はForループで空白セルを見つけたら直接他のシートにその時点で書き込ませるようにしていたのですが、かなり時間がかかるため、切って貼っての処理をなるべくさせないようにしたかったのです。

本当にありがとうございました。

回答No.4

Wendy02

こんばんは。

#3 の回答者です。

なお、AutoFilter メソッドは侮れません。これって、選択するのに、3万行ぐらいになっても一瞬で行ってしまうのです。1万行ぐらいですと、まあ、配列変数でこなせられるけれども、それ以上の場合は、AutoFilter を考えたほうがよいです。一応、昔のバージョンなどですと、なにやらバグの話も聞いたけれども、今は、そんなことがありませんから、見えているものだけを、Copy するというワザが使えます。

それから、
Application.ScreenUpdating =False
の効用は知っていますか？

Copyメソッドとかでも、結局のところ、Select や Range オブジェクトを取得するときに、画面もつられるので、重くなるのであって、画面の動きを止めると、格段にスピードが速くなります。

回答No.2

bin-chan

Next

e = Range(Cells(1, 15), Cells(a, 18)).Value

'以下の一行を追加してください。
Sheets("作業中").Select

Sheets("作業中").Range(Cells(1, 1), Cells(a, 4)).Value = e

End Sub

お礼

うまく動きました～！！
ありがとうございます！！
でも、どうして、test()のプロシージャだとうまく動くんですかね？
シロウトなのでよくわかりません…。

ともかく本当にありがとうございました！！

回答No.1

bin-chan

どの行でどんなエラーが出るのかな？

変数宣言の次の行、
myRow = Range("H1").CurrentRegion.Rows.Count
で、Range("H1")とはどのシートを指しているのでしょうか？
なんだか、作業中シートとは別のシートでは？と思いますが。

補足

Sheets("作業中").Range(Cells(1, 1), Cells(a, 4)).Value = e

の部分が黄色くなって、
アプリケーション定義またはオブジェクト定義エラーです

といわれます。
マクロ実行時はデータが載っているシートを開いて実行します。
そのシート内だけで作業するとうまく動くのです。つまり

Sheets("作業中").Range(Cells(1, 1), Cells(a, 4)).Value = e

の”Sheets("作業中").”の部分だけなくすと思い通りに動いてくれるのですが、データを作業中シートに返そうとするとたちまちエラーです。（涙）