Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
236 / 3841 ﾍﾟｰｼﾞ ←次へ　｜　前へ→
【77722】Re:縦並びを横並びにしたいです。
発言  β  - 15/12/6(日) 14:39 -
引用なし
パスワード
   ▼さと さん：

二番煎じですが。


Sub Test()
　　Dim dic As Object
　　Dim c As Range
　　Dim w As Variant
　　Dim tmp As Variant
　　Dim mx As Long
　　
　　Set dic = CreateObject("Scripting.Dictionary")
　　
　　For Each c In Range("D2", Range("D" & Rows.Count).End(xlUp))
　　　　dic(c.Value) = Array(dic.Count + 1, 0)
　　Next
　　
　　ReDim w(1 To dic.Count, 1 To Columns.Count)
　　
　　For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
　　　　If dic.exists(c.Value) Then
　　　　　　tmp = dic(c.Value)
　　　　　　tmp(1) = tmp(1) + 1
　　　　　　w(tmp(0), tmp(1)) = c.Offset(, 1).Value
　　　　　　w(tmp(0), tmp(1) + 1) = c.Offset(, 2).Value
　　　　　　If tmp(1) + 1 > mx Then mx = tmp(1) + 1
　　　　　　tmp(1) = tmp(1) + 2
　　　　　　dic(c.Value) = tmp
　　　　End If
　　Next
　　
　　ReDim Preserve w(1 To UBound(w, 1), mx)
　　Range("E2").Resize(UBound(w, 1), UBound(w, 2)).Value = w
　　
End Sub
・ツリー全体表示
【77721】Re:縦並びを横並びにしたいです。
発言  γ  - 15/12/6(日) 13:58 -
引用なし
パスワード
   まず気づくのは、マッチしたあとも比較を続けていること。
マッチして作業が終わったら Exit Forするとそれだけでも 1/2 になります。

ただこのような場合、一つずつ突き合わせをしていくのは効率が悪いです。
Dictionaryというデータ構造を使うと、それに備わった高速の検索機能が活かせて、
もっと早く突き合わせができます。
これを使うと良いと思います。

例えば、こんな書き方です。（未検証なのでまちがっていたら失礼）

Sub yokonarabi2()
　　Dim dic As Object
　　Dim i As Long, ii As Long
　　Dim r As Long
　　Dim saishuA As Long, saishuE As Long
　　
　　Set dic = CreateObject("Scripting.Dictionary")
　　'A列(コード)の最終セル
　　saishuA = Cells(Rows.Count, 1).End(xlUp).Row
　　'E列(作業列)の最終セル
　　saishuE = Cells(Rows.Count, 5).End(xlUp).Row
　　
　　For ii = 2 To saishuE
　　　　dic(Cells(ii, 5).Text) = ii
　　Next

　　For i = 2 To saishuA
　　　　r = dic(Cells(i, 1).Text)
　　　　Range(Cells(i, 2), Cells(i, 3)).Copy _
　　　　　 Destination:=Cells(r, Columns.Count).End(xlToLeft).Offset(0, 1)
　　Next
End Sub
・ツリー全体表示
【77720】縦並びを横並びにしたいです。
質問  さと  - 15/12/6(日) 12:50 -
引用なし
パスワード
   はじめて投稿させていただきます。
仕事で、縦並びの表を横並びにする必要があり、VBAで対応したく思っております。
条件としてA列(コード)に従い、予めE列(作業列)に表示してあるコードの右側に氏名と金額を横並びにしたいのですが、その際、A列のコードが同じものはE列に記載してあるコードの更に右側に(氏名)と(金額)を加えていくというものです。

（表）
A　　　　B　　　　C　 　D　　E　　　　 F　　　　G　　　　H　　　　 I　　 　J　　　 K
コード　氏名　　金額　　　作業列　 1　　　　2　　　　3　　　 　4　　 　5　　　 6
1001　　ホンダ 3500　　　1001　　ホンダ　3500　　　　　　　　　　　
1002　　スズキ 5000　　　1002　　スズキ　5000　 ヤマハ　6000　　　　　　　　
1002　　ヤマハ 6000　　　1003　　カワサ　5000　 カノン　 8000　 ペンタ　5000
1003　　カワサ 5000　　　1004　　ナイコ　10000　 オリン　10000　　　　　　　　
1003　　カノン　8000　　　1005　　トヨタ　 10000　 ニサン　8000　　　　　　　　
1003　　ペンタ 5000
1004　　ナイコ10000　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
1004　　オリン 10000　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
1005　　トヨタ　10000　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
1005　　ニサン 8000　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

VBAについては初心者で、いろいろ参考にしながら下記のコードを書きました。

----------------------------------------------------------------------
Sub yokonarabi()

Dim i As Long, ii As Long

'A列(コード)の最終セル
saishua = Cells(Rows.Count, 1).End(xlUp).Row
'E列(作業列)の最終セル
saishue = Cells(Rows.Count, 5).End(xlUp).Row

For i = 2 To saishua
　　For ii = 2 To saishue
'コードと作業列の値が同じならば
　　　　If Cells(i, 1).Value = Cells(ii, 5).Value Then
'作業列の右側空白セルに氏名と金額を貼り付け
　　　　Range(Cells(i, 2), Cells(i, 3)).Copy Destination:= _
　　　　　　Cells(ii, Columns.Count).End(xlToLeft).Offset(0, 1)
　　　　Else
　　　　End If
　　Next
Next

End Sub
-----------------------------------------------------------------------

上記コードを実行したところ、うまく動いたのですが、本番で取り扱う表はA列が２万行近くあり、VBAを実行するとエクセルが固まってしまいます。
どのようにすれば、大量の行数にも対応できるようになるでしょうか。

是非、ご教授願います。
・ツリー全体表示
【77719】Re:ハイパーリンクをつけるマクロについて
発言  γ  - 15/12/6(日) 9:08 -
引用なし
パスワード
   > vbaを勉強中なので、この意味が全くわかりません。
> わかる方、出来るだけ易しく教えてください。よろしくお願いします。

回答が示されたのでそちらを参考にして頂ければと思いますが、
補足です。
VBAを学習中とのことで、ご苦労さまです。
VBAのコードを書くには、それに対応する一般機能を知っていなければなりません。
単純なことですが重要なことです。
それを自覚しない方が結構多いです。

・どこに、ハイパーリンクを設定するか。
・リンク先はどこか。他のファイルなのか、他のサイトなのか、
　今使っているブックの特定シートなのか、そのセル範囲は？
・リンクを張っているセルに、どんな文字列を表示するのか
といった情報を指示する必要があります。
リンク先がどんな種類かによって、指定方法が微妙に違います。
それらは、ご自分で確認するのが確実です。

コードを書くには、すべて、頭からコードが湧いてくる訳でもありません。
実際に手を動かして確認しながら進めるものです。
そのあたりを誤解されていてはいけないので、補足しておきます。
・ツリー全体表示
【77718】Re:ハイパーリンクをつけるマクロについて
発言  マナ  - 15/12/5(土) 22:34 -
引用なし
パスワード
   ▼こういち さん：

参考コードです。
目次シートのシートモジュールに貼り付けてください。
説明は苦手なので、コードの意味はネット検索等で調べてください。

Sub 他のシートへのハイパーリンク作成()
　　Dim h As Hyperlink
　　Dim ws As Worksheet
　　Dim c As Range
　　Dim n As Long
　　
　　For Each h In Hyperlinks
　　　　h.Range.Clear
　　Next
　
　　For Each ws In Worksheets
　　　　If Not ws Is Me Then
　　　　　　Set c = Range("A3").Offset(n)
　　　　　　Hyperlinks.Add Anchor:=c, Address:="", _
　　　　　　　　SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
　　　　　　n = n + 1
　　　　End If
　　Next

End Sub
・ツリー全体表示
【77717】Re:ハイパーリンクをつけるマクロについて
発言  γ  - 15/12/5(土) 19:03 -
引用なし
パスワード
   > vbaを勉強中なので、この意味が全くわかりません。
> わかる方、出来るだけ易しく教えてください。よろしくお願いします。
βさんから指摘がありましたように、
ご自分でマクロ記録をとって確認するのがベストです。
こういうことは、いくら文章で説明しても分からないときは分かりません。
ご自分でやれば一目瞭然です。

余り時間は経っていませんが、
もし、言葉による追加説明の書き込みを待っていてはいけないので、
賛成票を投じます。
・ツリー全体表示
【77716】Re:ハイパーリンクをつけるマクロについて
発言  β  - 15/12/5(土) 14:02 -
引用なし
パスワード
   ▼こういち さん：

この設定を操作で行うことはできますよね。
念のために以下に記述しますが、この一連の操作をマクロ記録すると目的のコードが生成されますよ。

１．ハイパーリンクをセットするセルを選択
２．挿入タブ　ハイパーリンクをクリック
３．このドキュメント内(A)をクリック
４．セル範囲として表示されたツリーの中で目的のシートを選択
５．必要なら　表示文字列(T)にセルに表示する文言を入力
６．ＯＫボタン
・ツリー全体表示
【77715】ハイパーリンクをつけるマクロについて
質問  こういち  - 15/12/5(土) 13:40 -
引用なし
パスワード
   目次の一覧にハイパーリンクをつけてそれぞれのシートに移動するように
vbaで設定するにはどのような構文を書けばよいのでしょうか。
ヒントとして次のような参考資料がついています。

参考　Hyperlinks オブジェクトのAddメソッドの書式
　　　Object.Hyperlinks.Add _
　　　Anchor:=リンクセル,Address:=飛び先　１,　SubAddress:=飛び先２,TextToDisplay:=リンクセルに表示内容
※今回のObjectはアクティブシートである。

vbaを勉強中なので、この意味が全くわかりません。わかる方、出来るだけ易しく教えてください。よろしくお願いします。
・ツリー全体表示
【77714】Re:非表示の別ブックにシートコピーでき...
発言  マナ  - 15/12/4(金) 20:46 -
引用なし
パスワード
   ▼yy さん：

数分から1時間程度かかるのであれば、
そもそも画面を見続けるわけではないと思うので
ステータスバーのちらつきが気になるものなの？
という気がしますが…

ユーザーフォームを表示させておいて、
プログレスバーやラベルで
進捗状況、現在の条件等を確認できるようにする
という案はどうでしょうか。

いろいろ考えたうえでの質問のようなので
私なんかがアドバイスできそうになく、これで最後にします。
・ツリー全体表示
【77713】Re:非表示の別ブックにシートコピーでき...
回答  yy  - 15/12/4(金) 8:10 -
引用なし
パスワード
   ▼マナ さん：

マクロブックは1つのツールのような感じになっていて、
設定シートというシート上に、実行ボタンや
対象フォルダや実行条件の設定項目が配置されていて、
これを設定してマクロを実行する感じになっています。
フォルダの中のファイル数にもよりますが、
処理が終わるまでにある程度の時間が(数分から1時間程度)
かかるためステータスバーに進行状況を表示しています。
今、どのような条件で実行させているのかが分かるように、
設定シートの画面は表示させておきたいです。
・ツリー全体表示
【77712】Re:非表示の別ブックにシートコピーでき...
発言  マナ  - 15/12/3(木) 21:15 -
引用なし
パスワード
   ▼yy さん：

>非表示ブックにシートをコピーすることができるケースもありますよね?

そうでしたか。ごめんなさい。
トラブったときが心配ですが、マクロブックも非表示では嫌ですか。

Application.Visible = False
・ツリー全体表示
【77711】Re:Functionへの配列の渡し方
発言  β  - 15/12/3(木) 17:31 -
引用なし
パスワード
   ▼taro さん：

アップされたコードは掲示板様に手打ちされたんだと思いますが、実際のものを
コピペしたほうがよろしいですよ。

それはさておき。

アップされた　Call 処理A(day)　を　Call 処理B(day) に変更して実行すると
少なくとも、エラーにはならず、最後まで実行されますよ？
・ツリー全体表示
【77710】Functionへの配列の渡し方
質問  taro  - 15/12/3(木) 17:04 -
引用なし
パスワード
   初めまして。

こちらにはお世話になっています。
早速質問なのですが、配列を引数としてFunctionに渡す処理で、
「配列には割り当てられません」のエラーとなります。

おそらくday()のカッコ内に何か必要と思って、色々試したのですがうまくいきません。
また、Varient型を使えばできるのかもしれませんが、
あまり使わない方が良いというHPをいくつか見かけましたので、
使わないやり方を教えて頂きたいです。
済みませんが、よろしくお願いします。

Public Sub

　　'Date型配列宣言
　　Dim day(3) As Date

　　'初期化
　　day(0)="2001/1/1"
　　day(1)="2001/1/1"
　　day(2)="2001/1/1"
　　day(3)="2001/1/1"

　　'Data型配列を引数として処理B呼び出し
　　Call 処理A(day)

End Sub


Public Sub 処理B(day() As Date)

　　For i = 0 To 3
　　'Data型配列を引数として処理C呼び出し
　　　　temp = 処理C(day())　　←ここで「配列には割り当てられません」のエラー
　　Next

End Sub

Function 処理C(day() As Date) As Date

　　　　色々な処理

End Function
・ツリー全体表示
【77709】Re:非表示の別ブックにシートコピーでき...
質問  yy  - 15/12/3(木) 9:37 -
引用なし
パスワード
   ▼γ さん：

仕様を確認したのは、
非表示ブックにシートをコピーすることができるケースもあるからです。

sampleでは1つのExcelファイルだけを開いていますが、
実際のプログラムでは、複数のExcelファイルを順番に
開いて閉じる処理を繰り返しています。
このため、プログラム実行中の間、
常にタスクバーがちらちら動いて、
見苦しいので非表示にしたかったのです。
一時的に表示にして作業後に非表示にもしてみましたが
タスクバーはちらちら動いています。
・ツリー全体表示
【77708】Re:非表示の別ブックにシートコピーでき...
質問  yy  - 15/12/3(木) 9:34 -
引用なし
パスワード
   ▼マナ さん：

非表示ブックにシートをコピーすることができるケースもありますよね?
・ツリー全体表示
【77707】Re:非表示の別ブックにシートコピーでき...
発言  γ  - 15/12/2(水) 21:27 -
引用なし
パスワード
   仕様のようだと申し上げた。あくまで私見だ。
あなたと論争するつもりもないし、何の益もない。
こんな話はいくらでもある。
仕様であるかどうかをハッキリさせたいという必要性を感じているなら、
MS社に問い合わせるのが筋だ。聞く相手が違う。
事実が判明したら、皆さんに報告すると満足する人もいるかもしれない。

仕様だろうがバグだろうが、そのように動作すること自体は事実なので、
そして、その動作自体を変えることはできないんだから、
私にとっては、そんな境界線の話はどうでもいい。
それよりも一時的に表示にして作業後に非表示にすれば済むことだと
思っている。以上。
・ツリー全体表示
【77706】Re:非表示の別ブックにシートコピーでき...
発言  マナ  - 15/12/2(水) 21:13 -
引用なし
パスワード
   ▼yy さん：

少しだけ試してみました。

非表示ブックに対して
・シートの追加、削除はできそうですが
・コピー、移動はできないみたいです
・ツリー全体表示
【77705】Re:アクセス制限されているフォルダへの...
発言  はむ  - 15/12/2(水) 8:55 -
引用なし
パスワード
   ご回答ありがとうございます。

調べたところnet useなるものを使えば上手くいくのかと思いましたが、net useでは解決できませんでした。

別のアプローチも考えてみます。
・ツリー全体表示
【77704】Re:非表示の別ブックにシートコピーでき...
質問  yy  - 15/12/2(水) 8:37 -
引用なし
パスワード
   γ さん：早速のご回答有難うございます！

その仕様の内容をもう少し詳しく教えてほしいのですが、

「非表示のブックからシートのコピーはできるが
　非表示のブックへのシートのコピーできない。」

という仕様になっているということでしょうか。

よろしくお願いします。
・ツリー全体表示
【77703】Re:四角形吹き出しの先っぽにあたる点が...
発言  γ  - 15/12/1(火) 21:13 -
引用なし
パスワード
   ▼mumu さん：
>書いていただいたコード、今の私の知識だと
>半分も理解できてないですが、何とか読み解いて

私もAdjustmentsなんて触ったことはなかった。
それで実験してみた。

sp.Adjustments.Item(1)=0
sp.Adjustments.Item(2)=0
としたら、長方形の中心点を指した。

sp.Adjustments.Item(1)=1
sp.Adjustments.Item(2)=1
としたら、
その中心点を起点に、長方形の横幅、縦幅だけずらした点を指した。
それで、コードをでっちあげてみた。
参考になれば。
・ツリー全体表示
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
236 / 3841 ﾍﾟｰｼﾞ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free