Excel VBA質問箱 IV

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

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


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

【7274】nameプロパティについて hisao 03/8/29(金) 15:52 質問
【7282】Re:nameプロパティについて よろずや 03/8/30(土) 23:43 発言
【7321】Re:nameプロパティについて hisao 03/9/2(火) 15:12 質問
【7583】Re:nameプロパティについて しのしの 03/9/9(火) 1:25 回答
【7588】Re:nameプロパティについて hisao 03/9/9(火) 9:21 発言
【7592】Re:nameプロパティについて しのしの 03/9/9(火) 10:51 回答
【7607】Re:nameプロパティについて しのしの 03/9/9(火) 17:03 発言
【7612】Re:nameプロパティについて hisao 03/9/10(水) 11:17 お礼

【7274】nameプロパティについて
質問  hisao E-MAIL  - 03/8/29(金) 15:52 -

引用なし
パスワード
   教えてください。
sheet1に vbaでピボットテーブルを作成、テーブルに名前"T"を付け
更にA columnに名前をcolumns("a:a").select
selection.name="A"としました。
別シート sheet2 にmatch関数 =match(T,A,0)を取ろうとしたのですが
ピボットテーブルを再作成して、上記vbaで名前の範囲が変わっても"A"の範囲が変わりません。sheet2から 名前"A"を呼び出しても前のままなので、範囲が広くなっている場合にmatch関数が取れません。
ちなみに sheet1で名前"A"を選択すると変わっているのですが
何かvba記述の方法が悪いのでしょうか。良い方法を教えてください。

【7282】Re:nameプロパティについて
発言  よろずや  - 03/8/30(土) 23:43 -

引用なし
パスワード
   ▼hisao さん:
>教えてください。
>sheet1に vbaでピボットテーブルを作成、テーブルに名前"T"を付け
>更にA columnに名前をcolumns("a:a").select
>selection.name="A"としました。
>別シート sheet2 にmatch関数 =match(T,A,0)を取ろうとしたのですが
>ピボットテーブルを再作成して、上記vbaで名前の範囲が変わっても"A"の範囲が変わりません。sheet2から 名前"A"を呼び出しても前のままなので、範囲が広くなっている場合にmatch関数が取れません。
>ちなみに sheet1で名前"A"を選択すると変わっているのですが
>何かvba記述の方法が悪いのでしょうか。良い方法を教えてください。

実際のコードを提示してください。

【7321】Re:nameプロパティについて
質問  hisao E-MAIL  - 03/9/2(火) 15:12 -

引用なし
パスワード
   ▼よろずや さん:
すみません。
ちょっとコードが大きくて書きにくいのでお返事が遅れていました。
Sheets("借集計").Select
  Cells.Select
  Selection.Clear

  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "仕訳日記帳!R2C1:R" & y & "c11").CreatePivotTable TableDestination:=Range("A1"), _
    TableName:="ピボットテーブル4"
  ActiveSheet.PivotTables("ピボットテーブル4").SmallGrid = False
  With ActiveSheet.PivotTables("ピボットテーブル4").PivotFields("借")
    .Orientation = xlRowField
    .Position = 1
  End With
  With ActiveSheet.PivotTables("ピボットテーブル4").PivotFields("月")
    .Orientation = xlColumnField
    .Position = 1
    
  End With
  With ActiveSheet.PivotTables("ピボットテーブル4").PivotFields("借方金額")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlSum
  End With
    ActiveSheet.PivotTables("ピボットテーブル4").PivotSelect "", xlDataAndLabel
Selection.Name = "借"
Selection.Rows("2:2").Select
Selection.Name = "借行"
ActiveSheet.PivotTables("ピボットテーブル4").PivotSelect "", xlDataAndLabel
Selection.Columns("a:a").Select
Selection.Name = "借列"

を実行して ”損益表”及び”貸借表”シートにあらかじめ入れていた
=IF(ISERROR(INDEX(借,MATCH($B39,借列,0),MATCH(F$2,借行,0))),0,INDEX(借,MATCH($B39,借列,0),MATCH(F$2,借行,0)))
の値を出そうとしましたが 
ピボットテーブルの名前の範囲が変わっても”損益表”シートの関数で表示される
名前の範囲が変わりません。

そこで 
Sheets("損益表").Select
Sheets("損益表").Unprotect
ActiveWorkbook.Names.Add Name:="借", RefersToR1C1:="=借集計!借"
ActiveWorkbook.Names.Add Name:="借列", RefersToR1C1:="=借集計!借列"
ActiveWorkbook.Names.Add Name:="借行", RefersToR1C1:="=借集計!借行"
ActiveWorkbook.Names.Add Name:="貸", RefersToR1C1:="=貸集計!貸"
ActiveWorkbook.Names.Add Name:="貸列", RefersToR1C1:="=貸集計!貸列"
ActiveWorkbook.Names.Add Name:="貸行", RefersToR1C1:="=貸集計!貸行"
をモジュールの最後に入れて名前を付け直したらうまく行ったのですが
その後
Sheets("貸借表").Select
Sheets("貸借表").Unprotect
ActiveWorkbook.Names.Add Name:="借", RefersToR1C1:="=借集計!借"
ActiveWorkbook.Names.Add Name:="借列", RefersToR1C1:="=借集計!借列"
ActiveWorkbook.Names.Add Name:="借行", RefersToR1C1:="=借集計!借行"
ActiveWorkbook.Names.Add Name:="貸", RefersToR1C1:="=貸集計!貸"
ActiveWorkbook.Names.Add Name:="貸列", RefersToR1C1:="=貸集計!貸列"
ActiveWorkbook.Names.Add Name:="貸行", RefersToR1C1:="=貸集計!貸行"
と入れると 今度は名前の範囲が 何かとんでもないことになりました。
説明がわかり難いかもしれませんが
selection.name
とか activeworkbook.names の使い方 protectの張り方などお教え頂ければ
有りがたいです。


>▼hisao さん:
>>教えてください。
>>sheet1に vbaでピボットテーブルを作成、テーブルに名前"T"を付け
>>更にA columnに名前をcolumns("a:a").select
>>selection.name="A"としました。
>>別シート sheet2 にmatch関数 =match(T,A,0)を取ろうとしたのですが
>>ピボットテーブルを再作成して、上記vbaで名前の範囲が変わっても"A"の範囲が変わりません。sheet2から 名前"A"を呼び出しても前のままなので、範囲が広くなっている場合にmatch関数が取れません。
>>ちなみに sheet1で名前"A"を選択すると変わっているのですが
>>何かvba記述の方法が悪いのでしょうか。良い方法を教えてください。
>
>実際のコードを提示してください。

【7583】Re:nameプロパティについて
回答  しのしの  - 03/9/9(火) 1:25 -

引用なし
パスワード
   hisaoさん こんばんわ

ちょっと混乱しているようなので、
手助けになるかどうかわかりませんが、とりあえず。

>>別シート sheet2 にmatch関数 =match(T,A,0)を取ろうとしたのですが
>>ピボットテーブルを再作成して、上記vbaで名前の範囲が変わっても"A"の範囲が変わりません。sheet2から 名前"A"を呼び出しても前のままなので、範囲が広くなっている場合にmatch関数が取れません。
>>ちなみに sheet1で名前"A"を選択すると変わっているのですが
>>何かvba記述の方法が悪いのでしょうか。良い方法を教えてください。

そのMATCH関数の入っているシート(Sheet2)を再計算させれば
いいように思うのですが、どうでしょう?

【7588】Re:nameプロパティについて
発言  hisao E-MAIL  - 03/9/9(火) 9:21 -

引用なし
パスワード
   ▼しのしの さん:
有難う御座います。
その通りなのですが
名前Aがsheet1のピボットテーブルを作成したときのラベルの名前なものですから
sheet1上で定義します。ところがその名前Aの定義が何故かsheet1上のみの定義になってbook全体の定義になってないと言うかsheet2のmatch関数を再計算した時に
名前Aを拾えないのです。説明が我ながら不要領ですみません。
>hisaoさん こんばんわ
>
>ちょっと混乱しているようなので、
>手助けになるかどうかわかりませんが、とりあえず。
>
>>>別シート sheet2 にmatch関数 =match(T,A,0)を取ろうとしたのですが
>>>ピボットテーブルを再作成して、上記vbaで名前の範囲が変わっても"A"の範囲が変わりません。sheet2から 名前"A"を呼び出しても前のままなので、範囲が広くなっている場合にmatch関数が取れません。
>>>ちなみに sheet1で名前"A"を選択すると変わっているのですが
>>>何かvba記述の方法が悪いのでしょうか。良い方法を教えてください。
>
>そのMATCH関数の入っているシート(Sheet2)を再計算させれば
>いいように思うのですが、どうでしょう?

【7592】Re:nameプロパティについて
回答  しのしの  - 03/9/9(火) 10:51 -

引用なし
パスワード
   >名前Aがsheet1のピボットテーブルを作成したときのラベルの名前なものですから
>sheet1上で定義します。ところがその名前Aの定義が何故かsheet1上のみの定義になってbook全体の定義になってないと言うかsheet2のmatch関数を再計算した時に
>名前Aを拾えないのです。説明が我ながら不要領ですみません。

=match(T,A,0)

=match(T,Sheet1!A,0)
とすればよいのではないでしょうか?Sheet2からでも参照できますよ。

ご提示のコードは、たぶんマクロの記録かなにかで作成されたのですね。
できれば、もう少し作りかえたほうがよいと考えますが、
とりあえず、再計算を確認してくださいね。

【7607】Re:nameプロパティについて
発言  しのしの  - 03/9/9(火) 17:03 -

引用なし
パスワード
   しばらく拝見できないので 取り急ぎ、言いたいことだけ延べます。
ご検討をお願いします。

>    ActiveSheet.PivotTables("ピボットテーブル4").PivotSelect "", xlDataAndLabel

Nameプロパティ設定しなくて、正しく取れるのでしょうか?
それに、xlDataAndLabelでなくて、xlLabelOnlyでは?と考えますが

>Selection.Name = "借"

私はこのような使い方をしないので、これが正しいかどうかは分かりません。
でも、通常今まであったものを削除し、更に新規に追加する場合は、
削除処理をつけ、追加処理をNamesに対して行なったほうがよいと思います。
また、
Selection  active* というのはあまり使わないほうが良いと思います。
ひょんなことから、意図しないものがActiveになったり、Selectされるからです。
個人的にはこの2つがhisaoさんを悩ませている原因であると考えています。


>Selection.Rows("2:2").Select
>Selection.Name = "借行"

一度設定してやればマクロに記述する必要はないです。


hisaoさんのコードを参考にして
自分ならこういう風にするかな?というのをアップしてみます。
#動作確認できませんので、申し訳ありませんが、よろしくお願いします。
時間がなくて尻切れトンボですが...


----
Private Sub Test()
  Dim pvt     As Excel.PivotTable 'テーブル
  Dim rngSource  As Excel.Range   'テーブルソースとなるセル
  Dim rngTgt   As Excel.Range   '名前定義するセル
  
  
  '既に作成済みのテーブルに参照を設定します
  Set pvt = ThisWorkbook.Worksheets("借集計").PivotTables("ピボットテーブル4")
  
  
  'hisaoサンのコードはactiveworkbookになっていましたが、thisworkbookに
  '変更しています。必要に応じてWorkbooks(index)とかに変更してください。
  With ThisWorkbook.Worksheets("仕訳日記帳")
    
    ' ピポットテーブルのデータ範囲を設定します。
    'hisaoサンのコード"仕訳日記帳!R2C1:R" & y & "c11")のyが、A列のデータ格納
    '最終行ならこれでいけるはず。
    Set rngSource = _
      .Range(.Range("A1"), _
          .Cells(.Cells(.Rows.Count, .Columns("A").Column).End(xlUp).Row, _
            .Columns("K").Column))
    pvt.SourceData _
      = .Name & "!" & rngSource.Address(ReferenceStyle:=xlR1C1)
    
   End With
  
  '更新する
  pvt.RefreshTable

  '借のセル範囲ですが、DataBodyRangeプロパティなどを参考に考えてみてください
  'ここから先は知りきれトンボです.ごめんなさい。
  'でもhisaoサンのやりたいことPivotTableのなかにあるんじゃないかな?
  Set rngTgt = pvt.DataBodyRange
  Debug.Print pvt.ColumnRange.Select
  

  '参照の解放
  Set rngSource = Nothing
  Set rngTgt = Nothing
  Set pvt = Nothing

End Sub

【7612】Re:nameプロパティについて
お礼  hisao E-MAIL  - 03/9/10(水) 11:17 -

引用なし
パスワード
   ▼しのしの さん:
ご多忙なのに 大変有難う御座いました。

(Selection  active* というのはあまり使わないほうが良いと思います。
ひょんなことから、意図しないものがActiveになったり、Selectされるからです。
個人的にはこの2つがhisaoさんを悩ませている原因であると考えています。)

何故 参照できないか、出来たり出来なかったり 理由が良くわかりませんので
おっしゃる事だと思います。どうも ピボットテーブルを使ったモジュールは
不安定な気がします。

早速ご提示頂いた構文を組み込んで動作確認してみます。
本当に有難う御座いました。


>しばらく拝見できないので 取り急ぎ、言いたいことだけ延べます。
>ご検討をお願いします。
>
>>    ActiveSheet.PivotTables("ピボットテーブル4").PivotSelect "", xlDataAndLabel
>
>Nameプロパティ設定しなくて、正しく取れるのでしょうか?
>それに、xlDataAndLabelでなくて、xlLabelOnlyでは?と考えますが
>
>>Selection.Name = "借"
>
>私はこのような使い方をしないので、これが正しいかどうかは分かりません。
>でも、通常今まであったものを削除し、更に新規に追加する場合は、
>削除処理をつけ、追加処理をNamesに対して行なったほうがよいと思います。
>また、
>Selection  active* というのはあまり使わないほうが良いと思います。
>ひょんなことから、意図しないものがActiveになったり、Selectされるからです。
>個人的にはこの2つがhisaoさんを悩ませている原因であると考えています。
>
>
>>Selection.Rows("2:2").Select
>>Selection.Name = "借行"
>
>一度設定してやればマクロに記述する必要はないです。
>
>
>hisaoさんのコードを参考にして
>自分ならこういう風にするかな?というのをアップしてみます。
>#動作確認できませんので、申し訳ありませんが、よろしくお願いします。
>時間がなくて尻切れトンボですが...
>
>
>----
>Private Sub Test()
>  Dim pvt     As Excel.PivotTable 'テーブル
>  Dim rngSource  As Excel.Range   'テーブルソースとなるセル
>  Dim rngTgt   As Excel.Range   '名前定義するセル
>  
>  
>  '既に作成済みのテーブルに参照を設定します
>  Set pvt = ThisWorkbook.Worksheets("借集計").PivotTables("ピボットテーブル4")
>  
>  
>  'hisaoサンのコードはactiveworkbookになっていましたが、thisworkbookに
>  '変更しています。必要に応じてWorkbooks(index)とかに変更してください。
>  With ThisWorkbook.Worksheets("仕訳日記帳")
>    
>    ' ピポットテーブルのデータ範囲を設定します。
>    'hisaoサンのコード"仕訳日記帳!R2C1:R" & y & "c11")のyが、A列のデータ格納
>    '最終行ならこれでいけるはず。
>    Set rngSource = _
>      .Range(.Range("A1"), _
>          .Cells(.Cells(.Rows.Count, .Columns("A").Column).End(xlUp).Row, _
>            .Columns("K").Column))
>    pvt.SourceData _
>      = .Name & "!" & rngSource.Address(ReferenceStyle:=xlR1C1)
>    
>   End With
>  
>  '更新する
>  pvt.RefreshTable
>
>  '借のセル範囲ですが、DataBodyRangeプロパティなどを参考に考えてみてください
>  'ここから先は知りきれトンボです.ごめんなさい。
>  'でもhisaoサンのやりたいことPivotTableのなかにあるんじゃないかな?
>  Set rngTgt = pvt.DataBodyRange
>  Debug.Print pvt.ColumnRange.Select
>  
>
>  '参照の解放
>  Set rngSource = Nothing
>  Set rngTgt = Nothing
>  Set pvt = Nothing
>
>End Sub

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