Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


1060 / 13644 ツリー ←次へ | 前へ→

【76544】2次元配列の範囲指定について みかん 14/12/30(火) 12:16 質問[未読]
【76545】Re:2次元配列の範囲指定について β 14/12/30(火) 13:24 発言[未読]
【76546】Re:2次元配列の範囲指定について β 14/12/30(火) 15:46 発言[未読]
【76547】Re:2次元配列の範囲指定について β 14/12/30(火) 20:48 発言[未読]
【76548】Re:2次元配列の範囲指定について みかん 14/12/31(水) 14:46 お礼[未読]
【76549】Re:2次元配列の範囲指定について β 14/12/31(水) 21:39 発言[未読]

【76544】2次元配列の範囲指定について
質問  みかん  - 14/12/30(火) 12:16 -

引用なし
パスワード
   はじめまして。よろしくお願いします。

既存コードのスピードアップのため、配列のコーディングに取組みだしました。
セルからセルへの値の転記のイメージで、配列からセルへの値の転記を
実施しようとしています。

予め、セルの範囲わかっている場合の、配列から一気にセルに転記する例は、
Webでいろいろ検索出来たのですが、配列ないの範囲指定してセルに一気に
転記する例は見つける事が出来ませんでした。

以下のKizonコードを作り直ししていますが、配列の範囲指定が出来るのか、
方法もわからず悩んでいます。
※LOOPでトライしましたが、既存コードの方がスピードが速かったので、
 やはり、一気に転記する方法で実現したいと思っています。

ご指導のほど、お願いいたします。


【既存コード】------------------------------------------------------------
Sub kizon()

Dim Ij(1) as Integer:Dim Maxrows as Long

Maxrows = Ws.Cells(Rows.count, 46).End(xlUp).Row
Ij(1) = 54

Do Until Ij(1) >= 142
  Ws.Select
  Ws.Range(Cells(4, Ij(1) + 1), Cells(Maxrows, Ij(1) + 1)).Select
    Selection.Copy

  Ws.Range(Cells(4, Ij(1)), Cells(Maxrows, Ij(1))).Select
  Selection.PasteSpecial Paste:=xlPasteFormulas,
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Ws.Range(Cells(4, Ij(1) + 1), Cells(Maxrows, Ij(1) + 1)) = 0
 
  Ij(1) = Ij(1) + 4
Loop

End sub


【リメイク中のコード】----------------------------------------------------
Private Function Sheet2Array(BookName As String, SheetName As String) As Variant

 Dim RowNum1 As Double
 Dim ColNum1 As Double

 With Workbooks(BookName).Sheets(SheetName)
   RowNum1 = .UsedRange.Rows.count
   ColNum1 = .UsedRange.Columns.count
   Sheet2Array = .Range(.Cells(1, 1), .Cells(RowNum1, ColNum1))
 End With

End Function
---------------------------------------------------------------------------

Sub ReMake()

Dim Ij(1) as Integer:Dim Maxrows as Long
Dim DataBase() As Variant

Maxrows = Ws.Cells(Rows.count, 46).End(xlUp).Row
Ij(1) = 54

Do Until Ij(1) >= 142
  Ws.Select
  Ws.Range(Cells(4, Ij(1)), Cells(Maxrows, Ij(1))) = 'DataBase・・・ここが不明
  Ws.Range(Cells(4, Ij(1) + 1), Cells(Maxrows, Ij(1) + 1)) = 0
 
  Ij(1) = Ij(1) + 4
Loop

End sub

【76545】Re:2次元配列の範囲指定について
発言  β  - 14/12/30(火) 13:24 -

引用なし
パスワード
   ▼みかん さん:

コードは読んでいませんが、やろうとしておられることは
たとえば 10行5列の配列があったとして、このなかの
5行目から8行目、2列目から4列目の内容を、4行3列のセル範囲に一挙に転記したい。
こういうことでしょうか?

配列の部分を指定して、セルへ書き込むことは正規(?)のコードでは
(おそらく)できないでしょう。

配列のメモリー構造をコード内で把握して、ゴリゴリやればできるかもしれませんが
バージョンアップ時に外部にインターフェースとして公開されているもの以外は
内部構造がころころ変わるのが常ですから、やらないほうがいいですね。

考えられる方法としては、いったん元配列から必要部分を抜き出した配列を作る。
で、その配列から一挙にセルに転記。こういったことでしょうかね。

部分的な配列を作るのは、もちろん、コードを書けばいいんですが、普通に書く方法もありますし
シート関数の INDEX をマクロ内で使って生成する方法もあります。

【76546】Re:2次元配列の範囲指定について
発言  β  - 14/12/30(火) 15:46 -

引用なし
パスワード
   ▼みかん さん:

一応、↑でコメントした Index関数を使った処理案です。
各プロシジャで、最初にテストデータとして、A1:J1000 に値をセットしていますが
ここは、処理効率とは無縁のところですから、割り引いてください。
エクセルのメモリー内での内部処理を利用していますので、効率はいいと思います。

いずれもA1:J1000の内容を配列 v に取り込み、そこから抜出必要部分を
配列 w として生成。この配列 w をM1から始まるセル領域に一括で落とし込みます。

配列 v から配列 w を生成するところは、外だしのFunctionプロシジャにできますし
そうしておけば再利用に便利でしょうね。

(この配列生成方法は、以前に別掲示板で紹介されていたものです)

新規のブックの標準モジュールに以下をコピーして試してみてください。

'配列の指定列の全行を転記
Sub TestbyCol()
  Dim v As Variant
  Dim w As Variant
  
  testdata  'テストデータ生成
  
  'セル領域を配列に格納
  v = Range("A1").CurrentRegion.Value
  '2列目、4列目、6列目、8列目を指定
  w = Application.Index(v, Evaluate("row(1:" & UBound(v, 1) & ")"), Array(2, 4, 6, 8))
  
  Range("M1").Resize(UBound(w, 1), UBound(w, 2)).Value = w
  
End Sub

'配列の指定行の全列を転記
Sub TestbyRow()
  Dim v As Variant
  Dim w As Variant
  
  testdata  'テストデータ生成
  
  'セル領域を配列に格納
  v = Range("A1").CurrentRegion.Value
  '行を抜きだすために、いったん行列入れ替え
  v = WorksheetFunction.Transpose(v)
  '2行目、4行目、5行目を指定
  w = Application.Index(v, Evaluate("row(1:" & UBound(v, 1) & ")"), Array(2, 4, 5))
  '再度反転し元の行列の形式に
  w = WorksheetFunction.Transpose(w)
  
  Range("M1").Resize(UBound(w, 1), UBound(w, 2)).Value = w
  
End Sub

'配列の連続した指定行の指定列を転記
Sub testbyRowCol1()
  Dim v As Variant
  Dim f As Long
  Dim t As Long
  Dim w As Variant
  
  testdata  'テストデータ生成
  
  'セル領域を配列に格納
  v = Range("A1").CurrentRegion.Value
  '抜き出し 開始行 4行目 から 終了行 8行目
  f = 4
  t = 8
  '2列目、4列目、6列目、8列目を指定
  w = Application.Index(v, Evaluate("row(" & f & ":" & t & ")"), Array(2, 4, 6, 8))
  
  Range("M1").Resize(UBound(w, 1), UBound(w, 2)).Value = w

End Sub

'配列内のとびとびの行、列を指定して転記
Sub testbyRowCol2()
  Dim v As Variant
  Dim w As Variant
  
  testdata  'テストデータ生成
  
  'セル領域を配列に格納
  v = Range("A1").CurrentRegion.Value
  v = WorksheetFunction.Transpose(v)
  '2列目、4列目、6列目、8列目を指定
  w = Application.Index(v, Evaluate("row(1:" & UBound(v, 1) & ")"), Array(2, 4, 6, 8))
  '行を抜きだすために、いったん行列入れ替え
  w = WorksheetFunction.Transpose(w)
  '2行目、4行目、5行目を指定
  w = Application.Index(w, Evaluate("row(1:" & UBound(w, 1) & ")"), Array(2, 4, 5))
  
  Range("M1").Resize(UBound(w, 1), UBound(w, 2)).Value = w

End Sub

Sub testdata()
  Dim i As Long, j As Long
  Dim v(1 To 30, 1 To 10) As String
  Cells.Clear
  
  For i = 1 To 1000
    For j = 1 To 10
      Cells(i, j).Value = Cells(i, j).Address
    Next
  Next
  
End Sub

【76547】Re:2次元配列の範囲指定について
発言  β  - 14/12/30(火) 20:48 -

引用なし
パスワード
   ▼みかん さん:

★いったんアップしたレスを削除し、一部訂正して再度アップします。

そちらのコードをざっと読みました。
私の理解が正しければ、この処理に、配列からの一括転記はかなり無理ですね。

2次元配列の中身をセルに落とし込むときの絶対条件は、セル側が連続している
ということになります。なので、↑で、【連続している一部】を配列から抜き出して
一括転記するコードをアップしたんですが、今回のように4列とびの1列ずつを
別の列に転記するのは、無理ですねぇ。
(1列ずつの配列からの抜出と一括転記はできますが、意味がないですから)

それと、PasteSpecialでxlPasteFormulaとしていますね。
これに意味があるなら(つまりコピー対象領域に、本当に式があるなら)
たとえば元のセルに =A100+B100 といった式があったとして、それを配列に
式として取り込むことはできますが、別のセルに落とし込んでも =A100+B100
となります。PasteSpecialなら、例えば =E100+F100 となるべきところですね。
この意味でも配列に格納ーー>それを落とし込む ということは不可能になります。
もし、たまたま xlPasteFormula としているだけで、実際は値の転記でいいなら
話は別です。

つまり、BC列、BG列、BK列、。。。。のコピー対象行に式がないなら、この処理は
配列にセル領域から取り込み配列内で加工してシートに一挙に書き戻すということは
簡単に(↑でアップしたような INDEX関数処理を使わずとも)可能です。


ところで、もともとのコード自体に、ちょっと難なきにしもあらず。
Ws.Range(Cells(4, Ij(1) + 1), Cells(Maxrows, Ij(1) + 1)).Select
実は、このコードは【間違った書き方】です。
( ) の中の Cells は、実は Ws ではなく ActiveSheet の領域を指します。
その上にWs.Selectがあるので、この間違いが表に出ていませんが
日本語にすると、Wsの中の領域で、その詳細は ActiveSheetのここからここまで
という意味になります。なので、仮に ActiveSheetがWs ではなかったらデバッグ画面
が登場するところです。で、Ws.Selectだからいいだろうといいたいでしょうが、
それなら、 Ws.Range と、Wsで修飾する必要もなく、単に Range( と書けばいいんです。
(あぁ、私は Ws とつけるのは大変いいこと、というか必要なことだと思ってるんですよ)
そのほかにもループの中で毎回シート選択をするのは、いかがなものかとか、
変数Ijを配列にしている意味が全くないとか エトセトラ エトセトラ。

【76548】Re:2次元配列の範囲指定について
お礼  みかん  - 14/12/31(水) 14:46 -

引用なし
パスワード
   ▼β さん:

たくさん回答頂きありがとうございます。
明日か明後日になってしまいますが、
じっくり見て、理解を深めたいと思います。

また質問が出ると思いますが、
どうぞよろしくお願いします。

【76549】Re:2次元配列の範囲指定について
発言  β  - 14/12/31(水) 21:39 -

引用なし
パスワード
   ▼みかん さん:

ゆっくりと検討し、必要ならまた質問アップしてください。

ところで、現在のコード、EH列までの処理になっていますが、それでいいんですね?
EL列までの処理を想定してるんじゃないんですね?

1060 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free