Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
同ブック中の別シートのデータをユーザーが選択的できる方法を知りたい
投稿日時: 20/08/09 09:58:08
投稿者: saccer

VBA初心者です。
大学で化学系の研究をしていて夏休みを使って数字を自動で取りまとめるマクロを組もうと挑戦しています。
 

Sub まとめマクロ()

'
' まとめマクロ Macro
' 数値記録マクロとにたマクロ
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    
    ActiveCell.Offset(0, 0).Range("A1").Select
    
    
   Dim bk As Workbook
   Dim ws As Worksheet
   Set bk = ThisWorkbook
  
    ActiveCell.Formula = "=[bk]ws!D2"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!J9"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!J6"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!G4"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!G30"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!H30"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!I30"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!E30"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!F30"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!J30"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Formula = "=[bk]ws!K30"
    ActiveCell.Offset(1, -10).Range("A1").Select
  

End Sub

 
 
上記のようなコードで、「結果まとめ」シートに、選択したシートから必要なデータ(10個)を1列に列挙することはできました。しかし、いろいろ調べてみても、「ブックの値の更新」「シートの値の更新」がすべてのセルにおいて要求されます。
 
自分は、
@ブックの指定は今起動しているブックを選択していることにしたい
Aシートは、マクロを起動したときの「値の更新」時にすべて一括で更新したい
 
自力で調べてやってみたのですが解消できませんでした。
その名残で無駄なコードが残っているかもしれません。
自分では解決できそうにないので、なにかいいコードは存在しませんか?
教えていただければ幸いです。
 

回答
投稿日時: 20/08/09 12:12:37
投稿者: simple

こんな作りにしてはどうですか? 一案です。
(1)転記元のシートをアクティブにした状態で、マクロを起動することとする。
(2)マクロの中で、InputBoxを表示し、そこで、
   数式を書き込む「結果まとめ」シートのセルたちの書込開始セルを指定する。
(3)あとは自動で計算式を設定。
 
参考コードはこんな感じです。

Sub まとめマクロ()
    Dim ws  As String
    Dim r   As Range
    Dim ary As Variant
    Dim k   As Long

    ws = ActiveSheet.Name   'マクロ起動時に対象のシートをアクティブにしておくこと

    Set r = Application.InputBox("結果まとめシートの書き込み開始位置を指定", Type:=8)

    ary = Split("D2 J9 J6 G4 G30 H30 I30 E30 F30 J30 K30")
    
    For k = 0 To 10
        r.Offset(0, k).Formula = "=" & ws & "!" & ary(k)
    Next

    Application.Goto r
End Sub

回答
投稿日時: 20/08/09 15:33:35
投稿者: simple

一案と書いたのは、
例えば、"結果まとめ"シートのA列最終行の次の行に書き込む、と決まっているのであれば、
そのつど書込先を指定する必要はなく、次のように自動化できます。
 

Set r = Worksheets("結果まとめ").Cells(Rows.Count, "A").End(xlUp).Offset(1)

回答
投稿日時: 20/08/09 16:45:33
投稿者: WinArrow
投稿者のウェブサイトに移動

コードについてのアドバイス
 
処理の最初に
> ActiveCell.Offset(0, 0).Range("A1").Select
があります。
>ActiveCell
は、カーソルの位置するセルの意味です。
 
この処理を実行する前に、どこにカーソルを置くか、決まっているのでしょうか?
 
セルA1にカーソルがあるときは、A1が選択されます。
セルH10にカーソルがあるときは、H10が選択されます。
「カーソルがあるとき」とは、既に選択されているから、敢えて選択する必要はありません。
 
しかし、処理の都合上、「最初にA1を選択したい」ということならば、
ActiveCellは使用せず、
Range("A1").Select
だけで十分です。
 
また、随所にある
同形のコードはわかりにくいと思いますよ!
 
 
 

回答
投稿日時: 20/08/09 16:53:16
投稿者: WinArrow
投稿者のウェブサイトに移動

> ActiveCell.Formula = "=[bk]ws!D2"
↑のコードは、まともに動きますか?
まず、ws が設定されていませんし、数式が不完全です。
 
 
 
 

回答
投稿日時: 20/08/09 17:04:16
投稿者: WinArrow
投稿者のウェブサイトに移動

>、「ブックの値の更新」「シートの値の更新」がすべてのセルにおいて要求されます。
 
この原因は、
セルに代入している数式に問題が合います。
 
 Ws がセットされていないので、仮にSheet2とすると
Set Ws = bk.Sheets("Sheet2")
を適当な位置に記述して
 
>"=[bk]ws!D2"
を、
"=[" & bk.Name & "]" & ws.Name & "!D2"
に変更してみてください。、

回答
投稿日時: 20/08/09 17:21:33
投稿者: WinArrow
投稿者のウェブサイトに移動

質問
 
転送元のデータのセルに
規則性はありますか?
1件目→D2,J9,J6,G4,G30,H30,I30,E30,J30,K30
2件目はどのようになりますか?
このままだと、n回実行すると、同じデータがn行複写されてしまいますよね?
 

投稿日時: 20/08/09 19:42:25
投稿者: saccer

simpleさん ご返信ありがとうございます。
 
まだ薄学の身でなれないことばかりで、とても参考になります。
とりあえず、simple様の参考コードをコピペしてみて動くかどうか試してみたのですが、
 

 r.Offset(0, k).Formula = "=" & ws & "!" & ary(k)

 
の部分で「アプリケーションエラー」または「オブジェクトのエラー」と表記されました。
自力で考えてみてもエラーをはいている原因がよくわからなかったので良ければ一緒に考えていただけませんか?

投稿日時: 20/08/09 19:55:28
投稿者: saccer

 WinArrow様 
お返信ありがとうございます。
一個ずつ説明します。
 
一つ目、

ActiveCell.Offset(0, 0).Range("A1").Select

については無駄でした。ただ、自分の当初の予定では「B*」のセルを自分で選択をしていて、
実験記録のシート(先輩が昔作っていただいたもの)のデータを参照して「結果まとめ」シートに
値を張り付ける想定で作っています。
 
二つ目、
ActiveCell.Formula = "=[bk]ws!D2"

なぜか動きます。おそらくws bk をString型のデータとして認識しているのかもしれません。
 
三つ目、
挑戦してみます。
 
四つ目、
転送先のデータのセルの規則性はあります(固定値です)。
実験結果をまとめたものなので、
D2,J9,J6,G4,G30,H30,I30,E30,J30,K30
のセルにある値が欲しくて、
 
自分がExcelのシートごとに条件を変えた実験の結果の値が欲しくてこんなマクロを作りました。

回答
投稿日時: 20/08/09 19:58:17
投稿者: simple

Inputboxが表示された時に、
結果まとめ表の、数式を入れるべきセルの最初を
クリック(選択)しましたか?
 
そこが怪しそうですが、エラー時に、
それ以外の変数についてどうなっているか、
よく観察してください。
 
私は動作確認した上でアップしましたので
途中で雑音が入らない限り動作するはずです。

投稿日時: 20/08/09 20:21:16
投稿者: saccer

simple様
 
原因は不明?ですが、パソコンを再起動することでsimpl様のマクロが動くようになりました。
(もしかすると一度Inputbox時にキャンセルしたことがエラーをはくようになった原因かも?)
 
ありがとうがございます。
これからも何か思いついたら頑張ってみます。