VBAでのシート内容を配列に保存した時の列の追加

前へ 次へ
このQ&Aのポイント
  • Excel VBAでシート内容を配列に保存する際に、列の追加をする方法について解説します。
  • 配列を動的に増やすためにRedim Preserveを使用しますが、第一引数を増やすことはできません。そのため、他の方法を探す必要があります。
  • Excelシートのフィルターや他のデータ処理の手法を使って、配列を使わずにシートへ出力する方法も考えられます。処理の重さが問題なら、この方法を検討してみてください。
回答を見る
  • ベストアンサー

VBA でのシート内容を配列に保存した時の列の追加

EXCEL　VBAで開発をしています。 ２つのEXCELファイルの各表(シート）にオーダーNo（ユニークキー）が あり、処理を書いています。 各表を二次元配列に保存して、行追加時に動的に配列を増やそうとしています。 その時、Redim Preserve　を利用すると配列（行,列）と宣言しているので、第一引数にを追加しようとすると、”インデックスが有効範囲にありません”(-9)となりエラーとなります。 二次元配列で、第一引数は増やせない仕様ということは知っているのですが、このエラーを回避するためにどのようにするのが最善なのでしょうか？ Excelシートのフィルタ昨日を利用したりして、他の方法で色々試したのですが、処理があまりにも重くて使えませんでした。 ですので、配列などのように内部処理にてデータ処理をして、 一気にシートへ出力という風に考えております。 何卒宜しくお願い致します。

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

  • ベストアンサー
  • kkkkkm
  • ベストアンサー率65% (1618/2457)
回答No.2

こちらを参考にしてみてください。 [VBA]2次元配列の1次元目をRedim Preserveする https://sevenb.jp/wordpress/ura/2017/01/20/vba2%E6%AC%A1%E5%85%83%E9%85%8D%E5%88%97%E3%81%AE1%E6%AC%A1%E5%85%83%E7%9B%AE%E3%82%92redim-preserve%E3%81%99%E3%82%8B/

Kuronari12
質問者

お礼

ご回答ありがとうございます。 今すぐどうにかしたかったので、これがBESTです。関数を新しく作るだけで解決しました。 処理速度も考えていた通り、早くできたので、今は満足です。 時間ができたら、じっくり検証していきたいです。 大変助かりました。ありがとうございます。

その他の回答 (1)

  • kon555
  • ベストアンサー率52% (1752/3362)
回答No.1

　一読した印象では、そろそろエクセルでの対応自体を見直すべき領域に入っている気がしますが・・・それはさておき、このような形で回避は可能です。 https://www.mutable.work/entry/2019/09/17/205224

Kuronari12
質問者

お礼

ご回答ありがとうございます。 求めていたURLリンクです。 内容も分かりやすく、容易くできそうです。 少し時間が空いた時に、この処理に修正していきたいです。 大変にありがとうございました。とても感動しました。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • エクセルVBAで配列の追加

    エクセル2000です。 1行4列のセル範囲のデータを配列に取り込んで、後から別の1行4列のセル範囲のデータを配列に追加し、2次元配列として吐き出そうと思います。 最初の範囲がA1:D1、追加範囲がA4:D4とした場合、こんな不細工なコードになってしまいました。 これでも動きますが、どう修正すべきでしょうか？ Sub test() Dim myAr() myAr = Application.Transpose(Range("A1:D1").Value) ReDim Preserve myAr(1 To 4, 1 To 2) For i = 1 To 4 myAr(i, 2) = Cells(4, i) Next i Range("F1").Resize(UBound(myAr, 2), UBound(myAr, 1)).Value = Application.Transpose(myAr) End Sub

  • VBAの動的配列について

    いつもお世話になっております。 エクセルVBAを学習中の者です。 動的配列についてお伺いします。 添付資料を見て頂きたいのですが、 シート名1～4に同一レイアウトの表があります。 これらの表をを2次元配列に格納し、その後、同一レイアウトのシートに一括転記したいと考えています。 転記の事を考えて、条件としては、 シート1から2行目以降のデータを配列『data』に格納、変数『dataCnt』が転記先の行番号と同じになるように考えています。 当初は、配列の定義を『Dim data(100,3) As Variant』と、多めに要素数を定義して、コードを記述していました。 正直、凄く気持ちが悪い感じでした・・・ 最近、動的配列を学習しまして、 シートごとにデータの行数を変数『lastRow』に格納して、配列を再定義して【データ数＝要素数】とならないか？ と思い、下記のようなコードを書いてみました。 が、『ReDim　Preserve～』で実行エラーが発生してしまいます。 原因がなぜかわかりません！ そもそも、動的配列はこのような使い方は出来ないのでしょうか？ Sub テスト() Dim data() As Variant Dim x As Long Dim i As Long Dim ii As Long Dim lastRow As Long Dim dataCnt As Long dataCnt = 2 For x = 2 To 5 Worksheets(x).Activate lastRow = Cells(Rows.Count, 1).End(xlUp).Row If x = 2 Then ReDim data(2 To lastRow, 3) Else ReDim Preserve data(2 To dataCnt + lastRow - 1, 3) End If For i = 2 To lastRow For ii = 1 To 3 data(dataCnt, ii) = Cells(i, ii) Next ii dataCnt = dataCnt + 1 Next i Next x End Sub どなたかご指導をよろしくお願いいたします。

  • VBAでバイナリとして配列の一部を書き込む方法

    配列a(256, 256)をまるごとバイナリデータとして書き込みたい場合には Put #1,,a a(256, 256)の配列に入った数値のうち、 a(51,51)～a(256,256) に入った数値のみをバイナリデータとして書き込みたいのですが どのようにすれば良いですか？ で次元を変更して、この配列の中のデータを消さないままで a(0,0)～a(50,0)とa(0,0)～a(0,50) を削除してa(206,206)の配列を作成したいのですが、 ReDim Preserve a(206,206) とすると ”インデックスが有効範囲にありません”というエラーがでます。 http://hpcgi1.nifty.com/kenzo30/b_cbbs/cbbs.cgi?mode=al2&namber=7771&rev=&no=0&P=R&KLOG=51 このページにあるように、 ReDim Preserveは一次元配列でしか使えないそうです。 どうすれば一部だけバイナリに書き込めますでしょうか？

  • ジャグ配列生成時の1オリジン

    ExcelVBAで、CSVFileを取込み、シートを介さずに 配列へ格納する処理をしています。 尚、一行のデータの数が変わる可能性があるため、 ジャグ配列にしています。 　Dim FileNamePath As Variant 'CSVファイルパス 　Dim CSVFile() As Variant 　Dim ch1 As Long 　Dim RowCnt As Long ～略～ 　ch1 = FreeFile 　Open FileNamePath For Input As #ch1 　RowCnt = 1 　Do While Not EOF(ch1) 　　ReDim Preserve CSVFile(RowCnt) 　　Line Input #ch1, CSVFile(RowCnt) 　　CSVFile(RowCnt) = Replace(CSVFile(RowCnt), """", "") 　　CSVFile(RowCnt) = Split(CSVFile(RowCnt), ",") 　　RowCnt = RowCnt + 1 　Loop ～略～ この時、後の処理でやりやすくするために配列の添字を 1からにしたく、行の添字となるRowCntを1としています。 同様に列となる添字も1からとしたくて、モジュールの宣言にて 「Option Base 1」としましたが、上記コードでジャグ配列を 生成すると、(多次元配列で言う)2次元目の添字は 0からとなってしまいます。 　例：CSVFile(1)(0) このような状況で、ジャグ配列でも1オリジンとするには どのようにすれば良いのでしょうか。 宜しくお願い致します。

  • VBAでエクセルのいくつかの表を同じ配列に格納する

    VBA初心者です。 調べてみましたがどうにも情報に行きあたらなく、ご質問をさせていただきます。 添付のようなエクセル上の同一シート上にある3つの表をコピーし、VBAで同じ配列の各列として取り込みをしたいと考えております。 大分類、中分類、小分類のそれぞれの行は同じもののそれぞれの内容を記載しており、 できればこれらを同じ配列の１列目、２列目、３列目に格納をしたいと思っております。 これらの表が１つの表にまとまっていれば、 Dim 配列(1,10) as Variant 配列 =range("A1:C10") で事足りると思うのですが、別々のためこの方法がとれません。 セル指定で行うことも考えましたが、データが10万行を超える場合もあるため、できれば避けたいと思っています。 各表を指定し、配列の1列目、2列目、3列目に一括して代入をご存じでしたらら、どうぞ教えていただきたいと思います。 どうぞよろしくお願いします。

  • VBA 配列について

    配列の使い方について教えてください １つの配列をどんどん追加したりしたいので１つの mybox　で追加していきたいと思っています。 （下記コードが実現できればと思います。） (1)配列を広げ追加したい (2)繰返しを使わず一気に書き込みたい (3)一部をクリアしたりしたい のですがよろしくお願いします。 Sub Macro1() Range("A1").Value = "A" Range("A2").Value = "B" Range("A3").Value = "A" o = Range("A1").End(xlDown).Row mybox = Range(Cells(1, 1), Cells(o, 1)).Value 'myBox(1,1)=A 'myBox(2,1)=B 'myBox(3,1)=A ←このような表示になります。 '------------------------------------------------- '(1)配列を広げ追加したい ReDim Preserve mybox(o, 2) For i = 1 To UBound(mybox) If mybox(i, 1) = A Then mybox(i, 2) = 0 Else mybox(i, 2) = 1 End If Next i '------------------------------------------------- '(2)(1)をＣ列に「myBox(?,2)を「繰返しを使わず一気に」書き込みたい 'Transposeは限界（65536個）を超えるので使えません。 Range(Cells(1, 3), Cells(UBound(mybox), 3)) = mybox '(3)配列myBox(？,1)は残したままmyBox(？,2)はクリアにしたい End Sub

  • VBA シートの選択と追加

    ExcelのVBAで以下の処理をするにはどうすれば良いですか。 ①現在シートが７枚あります。 ②そのうち１枚目から５枚目を選択しています。 ③先頭（最後でも可）に１シート追加します。 ④最初に選択したシート（２枚目から６枚目）に戻って処理をします。 なお、選択するシート（この場合は１枚目から５枚目）は毎回変わります。

  • EXCEL VBA 配列のIndex番号書きかわり

    こんばんは。 EXCEL VBA　で困っております。 シートから配列にコピーさせると、ReDimで設定してあったIndex番号が なぜか替ってしまうらしいのです。 いろいろと検索してみましたが、どうしても見つからず、ここで質問させていただくことにしました。 下記のコードで確認してみてください。 Sub test() Dim A As Variant ReDim A(1 To 1, 0 To 4) For j = 0 To 4 A(1, j) = j Next j Range("A1:E1") = A MsgBox A(1, 0) 'ちゃんと機能して(1, 0)の位置で[0]と表示 'MsgBox A(1, 5) '←インデックスが有効範囲にありません A = Range("A1:E1") Range("A2:E2") = A 'MsgBox A(1, 0) 'さっき機能したのに今度はエラー MsgBox A(1, 1) '(1, 1)はセルでは２列目の[2]が取得されない MsgBox A(1, 5) 'さっきエラーになった(1, 5)は今度はエラーにならない End Sub なぜ、A(1 to 1, 0 to 4) が A(1 to 1, 1 to 5) と替ってしまうのか？ シート範囲全体をいっぺんに配列に格納するとこういうことが起こるのが仕様なのか？？ 自分の推測では、配列は[0]から始めることができますが、 シート上では[0]という開始位置を認識できないため、 Variant変数型のなかで自動的に書き換えちゃってくれてるのかな？ なんて思ったりしました。 理由がわかる方、おりましたらご教授くださいませ。 また、どうしても、この２次元配列で、 てっとり早いこのコピーコードを使いセル範囲から配列へコピーさせて、 列方向へのIndexを (0 to 4)とさせたいのですが、 何かよい方法はありますでしょうか？？ For文やEach文でひとつずつセルから配列へ格納という方法もあるかもしれませんが、 実際に打ち込んでいるコードは、行列ともかなり範囲が広いために、 処理時間を気にしてしまいます。 そこそこ使い慣れてきたのに、よもやこんなところで、躓くの！？といった心境です。 ご指南のほど、どうぞよろしくお願いいたします。

  • VBＡの配列内容の確認

    VBAの配列で頭を抱えています。 元々、WorksheetFunctionの引数に配列を入れて計算したかったのですが、原因不明のエラーで行き詰まっています。 Dim temp_array() As Variant Dim a As Double ↓ ※temp_array()という一時的な配列に、別の大きな配列の一部を切り出して代入 ↓ a = WorksheetFunction.Average(temp_array()) という流れで、temp_array()に格納された数字の平均値を求めるという流れなのですが、 「実行時エラー'1004': 　WorksheetFuntionクラスのAverageプロパティーを取得できません。」 となります。 配列の内容データがおかしいのかと思って、以下のようにエクセルシートに書き出して確認しても、データにおかしいところはありません。 For k = LBound(temp_array) To UBound(temp_array) Cells(k, 1) = temp_array(k) 　Next ところが、書き出された数字（例えば、10,20,30,40,50とします）を 　temp_array()=array(10,20,30,40,50) として、全く同じ数字をtemp_array()に入れ直して、 　a = WorksheetFunction.Average(temp_array()) とすると、今度は、エラーも出ずに期待した平均値が計算されて、aに代入されます。 色々、考えたのですが、何がおかしいのか全くお手上げです。 この怪奇現象について、何か思い当たる方がいたら、アドバイスいただけませんでしょうか？

  • 【エクセル】横の列の、配列方法について

    「列が「CO」まで、行が「２万行」近いデータについて、 横の列を、配列通りに並び替えする方法」について、伺います。 列の配列を状況に応じてカスタマイズしたいと考えまして、 配列基準行（各列の優先順位を1から順番に振った配列表示行）の 順番に沿って、横（列）の「並び替え」をしたいのですが、うまくいきません。 自分が考えたこととして、まず、シートの縦・横配列を、 「形式を選択して貼り付け」の際の「行列を入れ替える」にして、 縦横を変えた後、行の並び替えを実施して、 またその後、「行列を入れ替える」で元に戻そうとしたのですが、 「コピー領域と貼り付け領域が違うため・・・」という エラー表示が出て、うまくいきません。 2万行という行数が多きすぎて、列に入りきらなかった ためではないかと思います。 恐れ入りますが、どなたか最善策について、ご教授いただければと思います。 どうぞ、よろしくお願い致します

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する