Excel VBA質問箱 IV

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

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


4589 / 13646 ツリー ←次へ | 前へ→

【55697】シート間のデータ検索 T-k 08/5/16(金) 0:39 質問[未読]
【55701】Re:シート間のデータ検索 kanabun 08/5/16(金) 10:18 発言[未読]
【55702】Re:シート間のデータ検索 kanabun 08/5/16(金) 10:22 発言[未読]
【55708】Re:シート間のデータ検索 T−K 08/5/16(金) 11:50 質問[未読]
【55711】Re:シート間のデータ検索 kanabun 08/5/16(金) 14:04 発言[未読]
【55724】Re:シート間のデータ検索 T-k 08/5/16(金) 23:51 質問[未読]
【55725】Re:シート間のデータ検索 kanabun 08/5/17(土) 0:23 発言[未読]
【55726】Re:シート間のデータ検索 kanabun 08/5/17(土) 1:18 発言[未読]
【55765】Re:シート間のデータ検索 T-k 08/5/18(日) 23:16 質問[未読]
【55767】Re:シート間のデータ検索 kanabun 08/5/19(月) 1:03 回答[未読]
【55797】Re:シート間のデータ検索 T-k 08/5/19(月) 23:26 お礼[未読]

【55697】シート間のデータ検索
質問  T-k  - 08/5/16(金) 0:39 -

引用なし
パスワード
   2枚のシートで下記データがあります。

<シート1>
      B列  C列  D列 E列
先頭行   5/15 5/16 5/17 5/18〜  
A列  
A品名                 ←この中に数量を自動転記
B品名
C品名

<シート2>(データベース)

A列  B列  C列
A品名 5/15 100
A品名 5/18 200
A品名 5/30 500
B品名 5/15 100

シート1は先頭行に日にちを記入してありA列には商品のリスト名が
入力されていますシート2にはCSVファイルからコピーした注文リストが
データーベース化したものがあり定期的に追加を加えながらあります。
シート2のデータをシート1にデータを入れたいです。関数か、VBAかで処理したいのですがいろいろ考えましたが、まったく作れません誰かわかる方、教えていただけないでしょうか。よろしくお願いいたします。

【55701】Re:シート間のデータ検索
発言  kanabun  - 08/5/16(金) 10:18 -

引用なし
パスワード
   ▼T-k さん:

ぼくがこの手の問題をするなら、いつもこうします。

<シート1>
   A列    B列  C列  D列  E列
1  先頭行   5/15 5/16 5/17 5/18〜  
2  A列  ┌-----------------------------
3  A品名 |  (1,1) (1,2) (1,3) (1,4)  
4  B品名 |  (2,1) (2,2) (2,3) (2,4) 
5  C品名 |  (3,1) (3,2) (3,3) (3,4) 
6  〜  | 

(1) 書き込みたい正味範囲だけの配列を用意します。
  図の ↓部分 (行番号, 列番号)
   ┌-----------------------------
   |  (1,1) (1,2) (1,3) (1,4)
   |  (2,1) (2,2) (2,3) (2,4)
   |  (3,1) (3,2) (3,3) (3,4)
   |
(2)品名と行番号の対応表を用意します。
  「A品名」といったら、行番号1 が、
  「B品名」で問い合わせたら、行番号2が返るようなシステムです。
  ・・・
  これには Dictionary オブジェクトを使います。
    dic("A品名") = 1
    dic("B品名") = 2
    dic("C品名") = 3
  と、
  品名をキーに、行番号をアイテムとして辞書に記憶しておくと、
  毎回A列を検索しなくても、ある品名に対応する行番号が
  行番号 = dic(品名)
  のようにたちどころに得られます。
(3)同様に、日付から列番号が返るような式を用意します。
  上の例ですと, 5/15 が 1列目なので、
  ある日付の列番号 は (ある日付 - 5/14シリアル値) という式
  でもいちおう列番号が得られそうです。

(4)
 (1)の配列と (2)(3) を予備調査したら、
 データベースを上から順に読んでいき、
   行番号 = dic("A品名")
   列番号 = #5/1# - #5/14#
   配列(行番号,列番号) = 100
のように 順に配列内の適切な位置に データを代入していき、

(5)最後に シートの[B3]セル以降に 配列を貼り付けます。

【55702】Re:シート間のデータ検索
発言  kanabun  - 08/5/16(金) 10:22 -

引用なし
パスワード
   すみません。↑ タイプミスがありました。
> (4)
>  (1)の配列と (2)(3) を予備調査したら、
>  データベースを上から順に読んでいき、
>   行番号 = dic("A品名")
>   列番号 = #5/1# - #5/14#

列番号 = #5/15# - #5/14#

という意味でした。m(__)m

【55708】Re:シート間のデータ検索
質問  T−K  - 08/5/16(金) 11:50 -

引用なし
パスワード
   ▼kanabun さん:
>すみません。↑ タイプミスがありました。
>> (4)
>>  (1)の配列と (2)(3) を予備調査したら、
>>  データベースを上から順に読んでいき、
>>   行番号 = dic("A品名")
>>   列番号 = #5/1# - #5/14#
>
>列番号 = #5/15# - #5/14#
>
>という意味でした。m(__)m

早速返事いただきありがとうございます。疑問があるのは、品名が現時点で256行
有り列も256列使いたいのです行もこれから増える可能性もあり、メモリー的には
どうでしょうか?

【55711】Re:シート間のデータ検索
発言  kanabun  - 08/5/16(金) 14:04 -

引用なし
パスワード
   ▼T−K さん:
> 疑問があるのは、品名が現時点で256行
> 有り列も256列使いたいのです行もこれから増える可能性もあり、メモリー的には
>どうでしょうか?

メモリというより、さしあたり、シートの列数等の制限が問題になりそう
ですが、そこは 1シートの日数を 6か月とかにすることで、必要枚数シートを
追加していけばよいと思います。
それより、その問題に至るまで、まだ具体化しなければならないことが
いくつかあると思いますが、どうでしょう?(^^
上にあげた、
 (1),(2),(3),(4),(5)
のステップはすでにクリアされた(ご理解いただけた)ということでしょうか?

【55724】Re:シート間のデータ検索
質問  T-k  - 08/5/16(金) 23:51 -

引用なし
パスワード
   ▼kanabun さん:
>▼T−K さん:
>> 疑問があるのは、品名が現時点で256行
>> 有り列も256列使いたいのです行もこれから増える可能性もあり、メモリー的には
>>どうでしょうか?
>
>メモリというより、さしあたり、シートの列数等の制限が問題になりそう
>ですが、そこは 1シートの日数を 6か月とかにすることで、必要枚数シートを
>追加していけばよいと思います。
>それより、その問題に至るまで、まだ具体化しなければならないことが
>いくつかあると思いますが、どうでしょう?(^^
>上にあげた、
> (1),(2),(3),(4),(5)
>のステップはすでにクリアされた(ご理解いただけた)ということでしょうか?


ご返事ありがとうございます。ステップの理解ですが、素人に近い私には、少しむずかし
く理解が苦しいです。
(1)...書き込みたい正味範囲だけの配列を用意しますとのことですが、
シート1のシートに配列を用意するのでしょうか?それとも別にシートを用意するのでしょうか?
(2)品名と行番号の対応表を用意しますとのことですが
これも別シートに品名とそれに付随した、行番号のリストのシートを作るのか
わかりません?
Dictionary オブジェクトを使いますとのことでVBAのようですが
dic("A品名") = 1の構文の"Dic"は変数名でしょうか?変数の場合型は何になるのでしょうか?それともユーザー定義型変数でしょうか??
この処理をしてシート2のデータベースのデータをシート1に処理する流れがいまいち
つかめません?たび重ね申し訳ありませんが、もう一度教えていただけないでしょうか?
よろしくお願いいたします。

【55725】Re:シート間のデータ検索
発言  kanabun  - 08/5/17(土) 0:23 -

引用なし
パスワード
   ▼T-k さん:
こんばんは。
すべて説明していると、文章が苦手なぼくは 1日あっても足らない量なので...
かいつまんで。
>(1)...書き込みたい正味範囲だけの配列を用意しますとのことですが、
>シート1のシートに配列を用意するのでしょうか?それとも別にシートを用意するのでしょうか?
配列array というのは メモリ上に作られる変数の集合です。
品名分の行数 × 日付分の列数 の要素をもった2次元配列ですので、
イメージとしては ワークシートのセル範囲と似ていますが。

>(2)品名と行番号の対応表を用意しますとのことですが
>これも別シートに品名とそれに付随した、行番号のリストのシートを作るのか
>わかりません?
>Dictionary オブジェクトを使いますとのことでVBAのようですが
>dic("A品名") = 1の構文の"Dic"は変数名でしょうか?
> 変数の場合型は何になるのでしょうか?
> それともユーザー定義型変数でしょうか??
dic は 変数です。型は Object です。
Dictionary オブジェクト用の変数ですから、名前はあなたが好きなように
つけてかまいません。
Dictionary オブジェクトはこの掲示板を閲覧していると、よく出てくるかと
思いますが、Help にも説明があります。でも、
ネットで調べたほうが分かりやすい説明が多いように思います。
例として
 Excel(エクセル)VBA入門:Dictionaryを利用する
 h t t p://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_dictionary.html
とか、
 実例付きで詳しく説明してあるのは、
  Office TANAKA - Excel VBA(重複しないリストを作る)
  h t t p://officetanaka.net/excel/vba/tips/tips80.htm
あたりでしょうか。

> この処理をしてシート2のデータベースのデータをシート1に処理する流れが
> いまいちつかめません?

チームで作業するとして、

>> <シート1>
>>    A列    B列  C列  D列  E列
>> 1  先頭行   5/15 5/16 5/17 5/18〜  
>> 2  A列  ┌-----------------------------
>> 3  A品名 |  (1,1) (1,2) (1,3) (1,4)  
>> 4  B品名 |  (2,1) (2,2) (2,3) (2,4) 
>> 5  C品名 |  (3,1) (3,2) (3,3) (3,4) 
>> 6  〜  | 
の、
>>    ┌-----------------------------
>>    |  (1,1) (1,2) (1,3) (1,4)
>>    |  (2,1) (2,2) (2,3) (2,4)
>>    |  (3,1) (3,2) (3,3) (3,4)
>>    |
部分を配列に用意する人、

> <シート2>(データベース)
>
> A列  B列  C列
> A品名 5/15 100
> A品名 5/18 200
> A品名 5/30 500
> B品名 5/15 100

を順に読んでいって、
A品名の 5/15 だから 1行目、1列目に 100 を代入しなさい、
A品名の 5/18 だから 1行目の 3列目に 200を格納しなさい、
と、指示を出す人がいるわけです。(^^
「A品名」をみて 「1」行目と知る道具が Dictionaryオブジェクトという訳です。

とりあえずの説明はこんなところです。

【55726】Re:シート間のデータ検索
発言  kanabun  - 08/5/17(土) 1:18 -

引用なし
パスワード
   シートのレイアウトがこんな風だったとしますと、

<Sheet1>
----------------------------------------------------------------
    A    B  C   D   E   F   G   H  … AV
1 品名/日付 5/15 5/16 5/17 5/18 5/19 5/20 5/21 … 6/30
2 A品名
3 B品名
4 C品名

<Sheet2>
-------------------------------
  A    B   C
1 品名  日付  数量
2 A品名  5月15日  100
3 A品名  5月18日  200
4 A品名  5月30日  500
5 B品名  5月15日  100

先に説明した概略手順に対応するコードはこんな風です。
(エラー処理とかやってません)

Sub Try1()

>'<シート1>
>'(1) 書き込みたい正味範囲だけの配列を用意します。
>'  図の ↓部分(行番号, 列番号)
>'   ┌-----------------------------
>'   |  (1,1) (1,2) (1,3) (1,4)
>'   |  (2,1) (2,2) (2,3) (2,4)
>'   |  (3,1) (3,2) (3,3) (3,4)
>'   |
  Dim arry
  Dim 品名 As Range
  Dim 日付 As Range
  Dim 行数 As Long, 列数 As Long
  With Sheets("Sheet1")
    Set 品名 = .Range("A2", .Range("A65536").End(xlUp))
    Set 日付 = .Range("B1", .Range("IV1").End(xlToLeft))
  End With
  行数 = 品名.Rows.Count
  列数 = 日付.Columns.Count
  ReDim arry(1 To 行数, 1 To 列数)
   

>'(2)品名と行番号の対応表を用意します。
>'  「A品名」といったら、行番号1 が、
>'  「B品名」で問い合わせたら、行番号2が返るようなシステムです。
>'  ・・・
>'  これには Dictionary オブジェクトを使います。
>'    dic("A品名") = 1
>'    dic("B品名") = 2
>'    dic("C品名") = 3
>'  と、
>'  品名をキーに、行番号をアイテムとして辞書に記憶しておくと、
>'  毎回A列を検索しなくても、ある品名に対応する行番号が
>'  行番号 = dic(品名)
>'  のようにたちどころに得られます。
  Dim 品名List, Ls
  Dim dic As Object
  Dim i As Long
  
  品名List = 品名.Value
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(品名List)
   dic(品名List(i, 1)) = i
  Next
    

>'(3)同様に、日付から列番号が返るような式を用意します。
>'  上の例ですと, 5/15 が 1列目なので、
>'  ある日付の列番号 は (ある日付 - 5/14シリアル値) という式
>'  でもいちおう列番号が得られそうです。
>'
  Dim date0 As Long
  date0 = 日付.Item(1).Value2 - 1 'つまり 5/14のシリアル値

>'(4)
>' (1)の配列と (2)(3) を予備調査したら、
>' データベースを上から順に読んでいき、
>'   行番号 = dic("A品名")
>'   列番号 = #5/1/2008# - #5/14/2008#
>'   配列(行番号, 列番号) = 100
>'のように 順に配列内の適切な位置に データを代入していき、
>'
  Dim data
  Dim y As Long, x As Long
  With Sheets("Sheet2")
   data = .Range("A2", .Range("A65536").End(xlUp)).Resize(, 3).Value
  End With
  For i = 1 To UBound(data)
   y = dic(data(i, 1))
   x = CLng(data(i, 2)) - date0
   arry(y, x) = data(i, 3)
  Next


>'(5)最後に シートの[B3]セル以降に 配列を貼り付けます。
  Sheets("Sheet1").Range("B2").Resize(行数, 列数).Value = arry
  
  Set dic = Nothing
End Sub

【55765】Re:シート間のデータ検索
質問  T-k  - 08/5/18(日) 23:16 -

引用なし
パスワード
   ▼kanabun さん:
>シートのレイアウトがこんな風だったとしますと、
>
><Sheet1>
>----------------------------------------------------------------
>    A    B  C   D   E   F   G   H  … AV
>1 品名/日付 5/15 5/16 5/17 5/18 5/19 5/20 5/21 … 6/30
>2 A品名
>3 B品名
>4 C品名
>
><Sheet2>
>-------------------------------
>  A    B   C
>1 品名  日付  数量
>2 A品名  5月15日  100
>3 A品名  5月18日  200
>4 A品名  5月30日  500
>5 B品名  5月15日  100
>
>先に説明した概略手順に対応するコードはこんな風です。
>(エラー処理とかやってません)
>
>Sub Try1()
>
>>'<シート1>
>>'(1) 書き込みたい正味範囲だけの配列を用意します。
>>'  図の ↓部分(行番号, 列番号)
>>'   ┌-----------------------------
>>'   |  (1,1) (1,2) (1,3) (1,4)
>>'   |  (2,1) (2,2) (2,3) (2,4)
>>'   |  (3,1) (3,2) (3,3) (3,4)
>>'   |
>  Dim arry 
>  Dim 品名 As Range
>  Dim 日付 As Range
>  Dim 行数 As Long, 列数 As Long
>  With Sheets("Sheet1")
>    Set 品名 = .Range("A2", .Range("A65536").End(xlUp))
>    Set 日付 = .Range("B1", .Range("IV1").End(xlToLeft))
>  End With
>  行数 = 品名.Rows.Count
>  列数 = 日付.Columns.Count
>  ReDim arry(1 To 行数, 1 To 列数)
>   
>
>>'(2)品名と行番号の対応表を用意します。
>>'  「A品名」といったら、行番号1 が、
>>'  「B品名」で問い合わせたら、行番号2が返るようなシステムです。
>>'  ・・・
>>'  これには Dictionary オブジェクトを使います。
>>'    dic("A品名") = 1
>>'    dic("B品名") = 2
>>'    dic("C品名") = 3
>>'  と、
>>'  品名をキーに、行番号をアイテムとして辞書に記憶しておくと、
>>'  毎回A列を検索しなくても、ある品名に対応する行番号が
>>'  行番号 = dic(品名)
>>'  のようにたちどころに得られます。
>  Dim 品名List, Ls
>  Dim dic As Object
>  Dim i As Long
>  
>  品名List = 品名.Value
>  Set dic = CreateObject("Scripting.Dictionary")
>  For i = 1 To UBound(品名List)
>   dic(品名List(i, 1)) = i
>  Next
>    
>
>>'(3)同様に、日付から列番号が返るような式を用意します。
>>'  上の例ですと, 5/15 が 1列目なので、
>>'  ある日付の列番号 は (ある日付 - 5/14シリアル値) という式
>>'  でもいちおう列番号が得られそうです。
>>'
>  Dim date0 As Long
>  date0 = 日付.Item(1).Value2 - 1 'つまり 5/14のシリアル値
>
>>'(4)
>>' (1)の配列と (2)(3) を予備調査したら、
>>' データベースを上から順に読んでいき、
>>'   行番号 = dic("A品名")
>>'   列番号 = #5/1/2008# - #5/14/2008#
>>'   配列(行番号, 列番号) = 100
>>'のように 順に配列内の適切な位置に データを代入していき、
>>'
>  Dim data
>  Dim y As Long, x As Long
>  With Sheets("Sheet2")
>   data = .Range("A2", .Range("A65536").End(xlUp)).Resize(, 3).Value
>  End With
>  For i = 1 To UBound(data)
>   y = dic(data(i, 1))
>   x = CLng(data(i, 2)) - date0
>   arry(y, x) = data(i, 3)
>  Next
>
>
>>'(5)最後に シートの[B3]セル以降に 配列を貼り付けます。
>  Sheets("Sheet1").Range("B2").Resize(行数, 列数).Value = arry
>  
>  Set dic = Nothing
>End Sub


2回も回答いただきありがとうございます。
返事がおくれましたのは、試行錯誤していて頭が病んでいました。
書き込みたい正味範囲だけの配列を用意する部分はよくわかりました。
シート1の行数と列数を配列処理するのは、プログラムを見て理解できました
わからないのは、2)品名と行番号の対応表を用意する部分

品名List = 品名.Value この処理ですが品名の値(商品名)だと思いますが、
For i = 1 To UBound(品名List)この文でFor〜Next文1から最大値(添え字?)
の範囲を処理する部分が、前に書いてある、
     dic("A品名") = 1
>'    dic("B品名") = 2
>'    dic("C品名") = 3
を辞書に登録してないとうごかないのでしょうか?
上記登録は、モジュールにするのだとおもいますが、どこに入力しておくと処理
されるのでしょうか?
 後Dim date0 As Long
  date0 = 日付.Item(1).Value2 - 1 'つまり 5/14のシリアル値
のItem(1).Value2-1 ’5/14を求める理由がわからない
x = CLng(data(i, 2)) - date0’
この部分の意味がわかりません
後まったくそのまま使用して下記部分でデバッグがでます

arry(y, x) = data(i, 3) 'インデックスが有効範囲にありませんと表示される
何回かよみなおしていますが、文章読解能力がないようです(悲)


T-k

【55767】Re:シート間のデータ検索
回答  kanabun  - 08/5/19(月) 1:03 -

引用なし
パスワード
   こんばんは。

> わからないのは、2)品名と行番号の対応表を用意する部分
>
> 品名List = 品名.Value この処理ですが品名の値(商品名)だと思いますが、
> For i = 1 To UBound(品名List)この文でFor〜Next文1から最大値(添え字?)
> の範囲を処理する部分が、前に書いてある、
>>'    dic("A品名") = 1
>>'    dic("B品名") = 2
>>'    dic("C品名") = 3
> を辞書に登録してないとうごかないのでしょうか?
> 上記登録は、モジュールにするのだとおもいますが、どこに入力しておくと処理
> されるのでしょうか?

>>  Set dic = CreateObject("Scripting.Dictionary")
>>  For i = 1 To UBound(品名List)
>>   dic(品名List(i, 1)) = i
>>  Next
で、書き込み先の「品名」が 何行目にあるか をDictionary に記憶
しておくということです。

要は、
<シート2>(データベース)
のほうから
データ 100 を  <シート1>の「A品名」の行の 5/15 の列に、
データ 200 を  <シート1>の「A品名」の行の 5/18 の列に、
データ 500 を  <シート1>の「A品名」の行の 5/30 の列に、
データ 100 を  <シート1>の「B品名」の行の 5/15 の列に

転記すればいいのですが、毎回 「A品名」は何行目にあるか?
「C商品」は何行目にあるか?
Find やMatch 関数使って 都度調べるよりも、最初に1回だけの走査で
<シート1>のA列の各品名(行見出し)が何行目にあるか行番号を
あらかじめ記憶しておけば、

データ 100 を <配列>の (1,1) 要素位置に、
データ 200 を <配列>の (1,4) 要素位置に、
データ 500 を <配列>の (1,15) 要素位置に、
データ 100 を <配列>の (2,1) 要素位置に、
  …
簡単にすばやく格納できるということなんです。


>後  Dim date0 As Long
>   date0 = 日付.Item(1).Value2 - 1 'つまり 5/14のシリアル値
> のItem(1).Value2-1 ’5/14を求める理由がわからない
> x = CLng(data(i, 2)) - date0’
> この部分の意味がわかりません

.Value2 プロパティはデータの内部格納値のことで、
Value が 2008/5/15 という日付のとき、 Value2 は 39583 という倍精度値です。
いま、data(i, 2) が 2008/5/15 であれば、
> x = CLng(data(i, 2)) - date0’
は、
  x = CLng(#2008/5/15#) - 39582
すなわち、
  x = 39583 - 39582
で、結局 x = 1 が返ります。書き込む列番号は 1 ということです。


> 後まったくそのまま使用して下記部分でデバッグがでます
> arry(y, x) = data(i, 3) 'インデックスが有効範囲にありませんと表示される
デバッグで中断しているときに、 コード上の y や x や i のうえにマウス
カーソルをあてがうと、それら変数の現在の値が表示されます。
この現在値が Redimで宣言した配列の要素数のなかにおさまってないと
>  インデックスが有効範囲にありません
となります。

【55797】Re:シート間のデータ検索
お礼  T-k  - 08/5/19(月) 23:26 -

引用なし
パスワード
   ▼kanabun さん:
>こんばんは。
>
>> わからないのは、2)品名と行番号の対応表を用意する部分
>>
>> 品名List = 品名.Value この処理ですが品名の値(商品名)だと思いますが、
>> For i = 1 To UBound(品名List)この文でFor〜Next文1から最大値(添え字?)
>> の範囲を処理する部分が、前に書いてある、
>>>'    dic("A品名") = 1
>>>'    dic("B品名") = 2
>>>'    dic("C品名") = 3
>> を辞書に登録してないとうごかないのでしょうか?
>> 上記登録は、モジュールにするのだとおもいますが、どこに入力しておくと処理
>> されるのでしょうか?
>
>>>  Set dic = CreateObject("Scripting.Dictionary")
>>>  For i = 1 To UBound(品名List)
>>>   dic(品名List(i, 1)) = i
>>>  Next
>で、書き込み先の「品名」が 何行目にあるか をDictionary に記憶
>しておくということです。
>
>要は、
> <シート2>(データベース)
>のほうから
>データ 100 を  <シート1>の「A品名」の行の 5/15 の列に、
>データ 200 を  <シート1>の「A品名」の行の 5/18 の列に、
>データ 500 を  <シート1>の「A品名」の行の 5/30 の列に、
>データ 100 を  <シート1>の「B品名」の行の 5/15 の列に
>…
>転記すればいいのですが、毎回 「A品名」は何行目にあるか?
>「C商品」は何行目にあるか?
>Find やMatch 関数使って 都度調べるよりも、最初に1回だけの走査で
><シート1>のA列の各品名(行見出し)が何行目にあるか行番号を
>あらかじめ記憶しておけば、
>
> データ 100 を <配列>の (1,1) 要素位置に、
> データ 200 を <配列>の (1,4) 要素位置に、
> データ 500 を <配列>の (1,15) 要素位置に、
> データ 100 を <配列>の (2,1) 要素位置に、
>  …
>簡単にすばやく格納できるということなんです。
>
>
>>後  Dim date0 As Long
>>   date0 = 日付.Item(1).Value2 - 1 'つまり 5/14のシリアル値
>> のItem(1).Value2-1 ’5/14を求める理由がわからない
>> x = CLng(data(i, 2)) - date0’
>> この部分の意味がわかりません
>
>.Value2 プロパティはデータの内部格納値のことで、
>Value が 2008/5/15 という日付のとき、 Value2 は 39583 という倍精度値です。
>いま、data(i, 2) が 2008/5/15 であれば、
>> x = CLng(data(i, 2)) - date0’
>は、
>  x = CLng(#2008/5/15#) - 39582
>すなわち、
>  x = 39583 - 39582
>で、結局 x = 1 が返ります。書き込む列番号は 1 ということです。
>
>
>> 後まったくそのまま使用して下記部分でデバッグがでます
>> arry(y, x) = data(i, 3) 'インデックスが有効範囲にありませんと表示される
>デバッグで中断しているときに、 コード上の y や x や i のうえにマウス
>カーソルをあてがうと、それら変数の現在の値が表示されます。
>この現在値が Redimで宣言した配列の要素数のなかにおさまってないと
>>  インデックスが有効範囲にありません
>となります。


答えいただきありがとうごうざいます。粘りずよい説明のおかげで、100%とまではいかないまでも内容は6割〜7割理解できたとおもいます。あとは、自分なりにプログラムができるようになるため、日々努力と勉強ですね。一応そのまま利用さしてもらいインデックスの問題も
調べて解決して期待どうりの処理ができました。ほんとにありがとうございます。
一応データ取り込みしてからの一連の処理までのプログラムを記入します。

 '’データ取り込み部分''

Sub 取込()


Application.ScreenUpdating = False '画面の移動停止

Dim vri As Variant 'Vriをvariant型で宣言(メモリー格納)


 vri = Application.GetOpenFilename( _
   Filefilter:="テキストファイル,*.csv,", _
     Title:="他のファイルを開く", _
      MultiSelect:=False)
 'ファイルを開く処理
If vri = False Then
   MsgBox "ファイルは選択されませんでした。", _
    vbOKOnly + vbExclamation, "ファイル名の入力チェック"
    Exit Sub
    'ファイルが選択されない場合エラーメッセージ処理する
Else
   Workbooks.Open (vri)
   '選ばれた場合は選択したCSVファイルを開く
End If
 
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  Selection.Copy           'CSVファイルのデータ選択してコピー
   Windows("SHINBA.xls").Activate 'SHINBAxlsに移動
    Sheets("データ取込").Select  '(データ取込)シートに移動
     Range("A1").Select    'セルA1を選択してデータを貼り付け処理する
      ActiveSheet.Paste


Union(Columns("A"), Columns("C:d"), Columns("F"), _
   Columns("I"), Columns("K")).Select
     Selection.Delete
     '選択した列(不要なデータ)を削除する
Range("F1").Activate 'セルF1に移動する
With Range("F1") 'F1に対しての処理(Withステートメントの開始)
  .FormulaR1C1 = "=RC[-1]&RC[-4]" '文字連結
  .AutoFill Destination:=Range("F1", Range("E" & Rows.Count).End(xlUp).Offset(, 1)), _
   Type:=xlFillDefault
  '指定行までオートフィルする
End With '(Withステートメントの終了)
Range("F1", Range("F65536").End(xlUp)).Select
     Selection.Copy  '文字連結したセルを選択してコピーする
   
Range("G1").Activate 'G1へ移動
 Selection.PasteSpecial Paste:=xlPasteValues '値のみ貼り付け
      
Range("H1").Activate 'H1へ移動
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])" 'TRIM関数で文字間の不要なスペースを削除
ActiveCell.AutoFill Destination:=Range("H1", Range("G" & Rows.Count).End(xlUp).Offset(, 1)), _
   Type:=xlFillDefault  '指定行までオートフィルする
       
Range("H1", Range("H" & Rows.Count).End(xlUp)).Select
 Selection.Copy '指定行まで、コピー
   Range("I1").Activate 'セルI1へ移動
    Selection.PasteSpecial Paste:=xlPasteValues 'コピーした値を貼り付ける
    

Union(Columns("B"), Columns("E:H")).Select
  Selection.Delete '指定列を削除
  
Range("A1").Activate

   Selection.CurrentRegion.Select
    Selection.AutoFilter
     Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
'A列の0以外をオートフィルターでデータを絞り表示
Range("A1").CurrentRegion.Select '文字入力されている範囲を選択
 Selection.Copy 'その部分をコピーする
Range("E1").Select
 Selection.PasteSpecial Paste:=xlPasteValues 'セルE1を選択して貼り付け
Range("A1").Select
 Selection.AutoFilter Field:=1
  Application.CutCopyMode = False
   Selection.AutoFilter 'オートフィルターを解除してコピー処理を停止する

Columns("A:E").Select '列 A〜Eを選択
 Selection.Delete '選択した範囲を、削除する
Columns("A").Select '列Aを選択する
 Selection.Insert '列Aを挿入する
  Columns("D").Select
   Selection.Copy '列Dを選択してコピーする。
Columns("A").Select
 Selection.PasteSpecial Paste:=xlPasteValues '列Aを選択して、貼り付ける
   Application.CutCopyMode = False 'コピー処理を中止する
Columns("D").Select
  Selection.Delete '列Dを選択して削除する。

 
 Range("A1", Range("C" & Rows.Count).End(xlUp)).Select '値のある部分を選択
 Selection.Copy '選択したセルをコピーする
 
  Sheets("展開").Activate 'シート("展開")に移動
 If Sheets("展開").Range("A1") = "" Then  'A1が空白なら下記処理を実行
   Range("A1").Select  'セルA1を選択
    Selection.PasteSpecial Paste:=xlPasteValues '値のみ貼り付け
    
 Else
    Sheets("展開").Range("A1").End(xlDown).Offset(1, 0).Select '最終行を選択
     Selection.PasteSpecial    '値を貼り付け
    
    Columns("A:C").Select  '列A〜Cを選択
   
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
    
    '品番、納期の順番で昇順に並べ替える
     
    
 End If
 
    
      Application.CutCopyMode = False
      'コピーする処理を中止する
      Columns("B").Select '列Bを選択
       Selection.NumberFormatLocal = "yyyy/m/d" '日付形式に変換
       
     Sheets("メニュー画面").Activate 'メニュー画面に戻る
      Application.ScreenUpdating = True '画面の移動実行
     
     MsgBox "データ取込終了" '取込み終了メッセージ

End Sub


''教えていただいたプログラム''

Sub 展開()
  Dim arry      'arryを動的配列変数(Variant)を宣言
  Dim 品名 As Range '品名を、Rangeの型で宣言
  Dim 日付 As Range '日付をRange型で宣言
  Dim 行数 As Long, 列数 As Long '行数 列数をそれぞれLong型で宣言
 
  Dim 品名List, Ls
  Dim Dic As Object 'dicをObject型で宣言
  Dim i As Long   'iをLong型で宣言
  Dim date0 As Long ' 'Date0をLong型で宣言
  Dim data        'Detaを宣言(variant)
  Dim y As Long, x As Long 'xyをLong型で宣言


   With Sheets("データシート") 'データシートに対してそれぞれ処理する
    Set 品名 = .Range("A2", .Range("A65536").End(xlUp)) '品名の行の範囲指定
    Set 日付 = .Range("B1", .Range("IV1").End(xlToLeft)) '日付の列の範囲指定
      End With 'With 処理の終了
       行数 = 品名.Rows.Count  '行の数
        列数 = 日付.Columns.Count '列の数
         ReDim arry(1 To 行数, 1 To 列数) '動的配列変数に対するメモリ領域の再割り当てを
  
  
  品名List = 品名.Value '品名List=データシート品名範囲の値
  Set Dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(品名List) '品名リストの最小値から最大値を変数iに代入
  
   Dic(品名List(i, 1)) = i
  Next
  
  date0 = 日付.Item(1).Value2 - 1
  
  
  With Sheets("展開") 'シート展開にたいして処理
 
   data = .Range("A2", .Range("A65536").End(xlUp)).Resize(, 3).Value '展開のデータ範囲のデータの値
  End With
  For i = 1 To UBound(data) '展開のデータ範囲のデータ値の最小値から最大値まで順に処理
   y = Dic(data(i, 1))
   x = CLng(data(i, 2)) - date0 '指定された式をバリアント型 (内部処理形式が長整数型 (Long) の Variant) に変換して返します。
   arry(y, x) = data(i, 3)
  Next
  Sheets("").Range("B2").Resize(行数, 列数).Value = arry
 
  Set Dic = Nothing


End Sub

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