エクセルの質問です。

Application.InputBox をtype8で使う時に、その取得した範囲を別のシートのA1から下に順々に格納したいのですが、エラーは出ないのですが、どうしても空振りしてしまいます。
エラーが出ないので余計に困っています。

お知恵を貸してください。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2008/06/09 14:36:29
  • 終了:2008/06/11 14:14:51

ベストアンサー

id:SALINGER No.3

SALINGER回答回数3454ベストアンサー獲得回数9692008/06/10 18:27:47

ポイント26pt

シートを追加して範囲という名前にしてください。

そこに、データを保存してみました。

Option Explicit

Private Sub CommandButton1_Click()
    Dim tenki As Range
    Dim str As String
    Dim WH As Worksheet
    Dim i As Long
    
    '範囲保存用のシートを指定
    Set WH = Worksheets("範囲")
    
    str = Application.InputBox(prompt:="集計名を入れてください", Title:="集計名", Type:=2)
    If str = "False" Then Exit Sub
    
    'キャンセルを選んだときのためエラートラップ
    On Error GoTo ErrNot
    Set tenki = Application.InputBox(prompt:="セル範囲を選択", Title:="セルを選択", Type:=8)
    On Error GoTo 0
    
    i = 1
    While WH.Cells(i, 1).Value <> ""
        i = i + 1
    Wend
    WH.Cells(i, 1).Value = str
    WH.Cells(i, 2).Value = tenki.Address
    
    ListBox1.AddItem (str)
    
    ListBox1.ListIndex = ListBox1.ListCount - 1
    
    Exit Sub
ErrNot:
End Sub

Private Sub ListBox1_Change()
    Dim WS As Worksheet
    Dim WH As Worksheet
    Dim r As Range
    Dim w As Worksheet
    Dim myRow As Long
    Dim tenki As Range
        
    If ListBox1.ListIndex = -1 Then Exit Sub
    
    '転記先のシートを指定
    Set WS = Worksheets("集計")
    
    '範囲保存用のシートを指定
    Set WH = Worksheets("範囲")
    
    WS.Cells.Clear
    myRow = 1
    For Each w In Worksheets
        If w.Name <> WS.Name And w.Name <> WH.Name Then
            Set tenki = w.Range(WH.Cells(ListBox1.ListIndex + 1, 2).Value)
            tenki.Copy
            WS.Cells(myRow, 1).PasteSpecial Paste:=xlAll, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            WS.Cells(myRow, tenki.Column).ColumnWidth = tenki.ColumnWidth
            WS.Cells(myRow, tenki.Column).RowHeight = tenki.RowHeight

            myRow = myRow + tenki.Rows.Count

        End If
    Next w
    
    Application.CutCopyMode = False
    
    ListBox1.ListIndex = -1
End Sub

更にThisWorkbookのWorkbook_Openのところに次のように書けば、ブックを開いたときにリストボックスが戻ります。

Private Sub Workbook_Open()
    Dim i As Integer
    i = 1
    While Worksheets("範囲").Cells(i, 1).Value <> ""
        Worksheets("Sheet1").ListBox1.AddItem (Worksheets("範囲").Cells(i, 1).Value)
        i = i + 1
    Wend
End Sub

シート名とかはお使いの環境に合わせてください。

http://q.hatena.ne.jp/

id:clinejp

大変ありがとうございました。

このトピックスは全部解決してしまいました。

これをカレンダーコントロールと組み合わせたいのですが、これは質問内容を整理して、別質問したいと思いますので、ぜひまた回答いただけると幸いです。

2008/06/11 14:13:11

その他の回答(2件)

id:kopj No.1

kopj回答回数123ベストアンサー獲得回数62008/06/09 21:22:40

ポイント11pt

http://pintxos.blog.bai.ne.jp/?eid=82496

Range型変数の使い方を間違えてませんか?

仕様の説明どおりにプログラムが作られているのかどうかは、プログラムがのってないのでわかりません。

このような質問のときは、どのようにプログラムしているのかのせるのが一番です。

id:clinejp

回答ありがとうございました。

下記のコメント欄にソースを書きました。

range型は確かによく分からなかったので、受け側をvariantにしたんですがそれでもだめでした。

下記は、そのDimはとってあります。

2008/06/10 08:17:50
id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692008/06/10 14:14:51

ポイント43pt

コメントのコードで自分の環境では普通にコピーできました。

コピーできない原因が特定できないと意味は無いかもしれませんが、リストボックスを使う方法を紹介します。

ボタンは使っているようなので、更にリストボックスを追加して、そのイベントに次のコードを書き込んでみてください。


ただし、リストボックスの内容はブックを開くたびにクリアされます。

その場合は、別のシートに範囲のリストを書き込んでおいて、開いたときに読み込ませるとかすればいいです。

Option Explicit

Private Sub CommandButton1_Click()
    Dim tenki As Range
    
    'キャンセルを選んだときのためエラートラップ
    On Error GoTo ErrNot
    Set tenki = Application.InputBox(prompt:="セル範囲を選択", Title:="セルを選択", Type:=8)
    On Error GoTo 0
    
    ListBox1.AddItem (tenki.Address)
    
    ListBox1.ListIndex = ListBox1.ListCount - 1
    
    Exit Sub
ErrNot:
End Sub

Private Sub ListBox1_Change()
    Dim WS As Worksheet
    Dim r As Range
    Dim w As Worksheet
    Dim myRow As Long
    Dim tenki As Range
        
    If ListBox1.ListIndex = -1 Then Exit Sub
    
    '転記先のシートを指定
    Set WS = Worksheets("集計")
    
    WS.Cells.Clear
    myRow = 1
    For Each w In Worksheets
        If w.Name <> WS.Name Then
            Set tenki = w.Range(ListBox1.Value)
            tenki.Copy
            WS.Cells(myRow, 1).PasteSpecial Paste:=xlAll, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            WS.Cells(myRow, tenki.Column).ColumnWidth = tenki.ColumnWidth
            WS.Cells(myRow, tenki.Column).RowHeight = tenki.RowHeight

            myRow = myRow + tenki.Rows.Count

        End If
    Next w
    
    Application.CutCopyMode = False
    
    ListBox1.ListIndex = -1

End Sub

http://q.hatena.ne.jp/

id:clinejp

ありがとうございます。

こういったイメージです。感激。

それぞれのRangeデータに「日報得意先名」「日報今後の課題」など名前をつけたいのですが、これはどうしたらよいでしょうか。

理想的には、2バイト文字をクリックすると、それに紐付けされたレンジが、転記されるというものです。

ひとつ考えたのは下記です。

1InputBoxで「集計名を入れてください」と入力を促す

2それをA列に入れる

3取得したレンジをB列に入れる

4ListBoxにはA列のデータが入るようにして、offsetでとなりのB列のデータを転記する

もっと洗練された方法があればご教示ください。

よろしくお願いいたします。

2008/06/10 16:26:36
id:SALINGER No.3

SALINGER回答回数3454ベストアンサー獲得回数9692008/06/10 18:27:47ここでベストアンサー

ポイント26pt

シートを追加して範囲という名前にしてください。

そこに、データを保存してみました。

Option Explicit

Private Sub CommandButton1_Click()
    Dim tenki As Range
    Dim str As String
    Dim WH As Worksheet
    Dim i As Long
    
    '範囲保存用のシートを指定
    Set WH = Worksheets("範囲")
    
    str = Application.InputBox(prompt:="集計名を入れてください", Title:="集計名", Type:=2)
    If str = "False" Then Exit Sub
    
    'キャンセルを選んだときのためエラートラップ
    On Error GoTo ErrNot
    Set tenki = Application.InputBox(prompt:="セル範囲を選択", Title:="セルを選択", Type:=8)
    On Error GoTo 0
    
    i = 1
    While WH.Cells(i, 1).Value <> ""
        i = i + 1
    Wend
    WH.Cells(i, 1).Value = str
    WH.Cells(i, 2).Value = tenki.Address
    
    ListBox1.AddItem (str)
    
    ListBox1.ListIndex = ListBox1.ListCount - 1
    
    Exit Sub
ErrNot:
End Sub

Private Sub ListBox1_Change()
    Dim WS As Worksheet
    Dim WH As Worksheet
    Dim r As Range
    Dim w As Worksheet
    Dim myRow As Long
    Dim tenki As Range
        
    If ListBox1.ListIndex = -1 Then Exit Sub
    
    '転記先のシートを指定
    Set WS = Worksheets("集計")
    
    '範囲保存用のシートを指定
    Set WH = Worksheets("範囲")
    
    WS.Cells.Clear
    myRow = 1
    For Each w In Worksheets
        If w.Name <> WS.Name And w.Name <> WH.Name Then
            Set tenki = w.Range(WH.Cells(ListBox1.ListIndex + 1, 2).Value)
            tenki.Copy
            WS.Cells(myRow, 1).PasteSpecial Paste:=xlAll, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            WS.Cells(myRow, tenki.Column).ColumnWidth = tenki.ColumnWidth
            WS.Cells(myRow, tenki.Column).RowHeight = tenki.RowHeight

            myRow = myRow + tenki.Rows.Count

        End If
    Next w
    
    Application.CutCopyMode = False
    
    ListBox1.ListIndex = -1
End Sub

更にThisWorkbookのWorkbook_Openのところに次のように書けば、ブックを開いたときにリストボックスが戻ります。

Private Sub Workbook_Open()
    Dim i As Integer
    i = 1
    While Worksheets("範囲").Cells(i, 1).Value <> ""
        Worksheets("Sheet1").ListBox1.AddItem (Worksheets("範囲").Cells(i, 1).Value)
        i = i + 1
    Wend
End Sub

シート名とかはお使いの環境に合わせてください。

http://q.hatena.ne.jp/

id:clinejp

大変ありがとうございました。

このトピックスは全部解決してしまいました。

これをカレンダーコントロールと組み合わせたいのですが、これは質問内容を整理して、別質問したいと思いますので、ぜひまた回答いただけると幸いです。

2008/06/11 14:13:11
  • id:SALINGER
    空振りというのは、コピーされないということでしょうか?
    範囲の中でコピーされる部分とされない部分があるということでしょうか?
    inputboxのtype8の場合、普通、戻り値がRange型なのですが、キャンセルを押したとき別の型が返るのでエラートラップで判定します。
    キャンセルを押さなければエラーは起きないので、エラートラップをはずして実行させてみれば、エラーが起きてるかはわかります。
  • id:clinejp
    実はSALINGERさんに教えていただきましたコードに、ちょこちょこと追加してみました。

    'ここに追加しました。
    という部分です

    コピーはまったくされません。
    それ以外はSALINGERさんのコードで完璧に動いています。
    (教えていただきましたコードを参考にサンプルの2の方に、行列幅の指定を加えています)
    ----------------------------------------------------------------------

    Private Sub CommandButton5_Click()

    Dim tenki As Range
    Dim WS As Worksheet
    Dim r As Range
    Dim w As Worksheet
    Dim myRow As Long

    '転記先のシートを指定
    Set WS = Worksheets("集計")

    'キャンセルを選んだときのためエラートラップ
    On Error GoTo ErrNot
    Set tenki = Application.InputBox(prompt:="セル範囲を選択", Title:="セルを選択", Type:=8)
    On Error GoTo 0

    'ここに追加しました。


    tenkidata = tenki
    Sheets(1).Range("A1").Value = tenkidata




    WS.Cells.Clear
    myRow = 1
    For Each w In Worksheets
    If w.Name <> WS.Name Then
    w.Range(tenki.Address).Copy
    WS.Cells(myRow, 1).PasteSpecial Paste:=xlAll, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    WS.Cells(myRow, tenki.Column).ColumnWidth = w.Range(tenki.Address).ColumnWidth
    WS.Cells(myRow, tenki.Column).RowHeight = w.Range(tenki.Address).RowHeight

    myRow = myRow + myRow + tenki.Rows.Count


    End If
    Next w

    Application.CutCopyMode = False

    Exit Sub
    ErrNot:
    End Sub
  • id:clinejp
    もう少し目的の部分を具体的に書いた方が良いと思いました。

    このコードで取得されるデータの取得範囲を別シートに記憶させて、次回からは手動で範囲を指摘しなくても、記録したその値を参照するようにしたいのです。

    完成形のイメージとしては

    1Rangeで必要部分を集計する
    2その集計パターンに名前をつける
    3集計パターンがリストボックスなどで一覧になっている
    4リストボックスを選択すると、該当パターンが動作する

    というのを目指しています。

    お答えいただける範囲で結構ですのでヒントなどもいただけると幸いです。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません