こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

投稿日時 - 2008-05-12 23:43:15

QNo.4018019

すぐに回答ほしいです

質問者が選んだベストアンサー

こんばんは。

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

投稿日時 - 2008-05-13 02:19:06

お礼

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

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

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

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

投稿日時 - 2008-05-14 01:10:43

ANo.3

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

こんばんは。

#3 の回答者です。

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

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

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

投稿日時 - 2008-05-14 01:43:35

ANo.2

Next

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

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

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

End Sub

投稿日時 - 2008-05-13 00:15:30

お礼

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

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

投稿日時 - 2008-05-13 00:27:11

ANo.1

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

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

投稿日時 - 2008-05-12 23:58:05

補足

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

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

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

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

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

投稿日時 - 2008-05-12 23:59:26

あなたにオススメの質問