Excel VBA質問箱 IV

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

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


12291 / 13645 ツリー ←次へ | 前へ→

【11251】変数の指定方法について 初心者です 04/3/3(水) 21:23 質問
【11257】Re:変数の指定方法について かみちゃん 04/3/3(水) 22:22 回答
【11258】Re:変数の指定方法について アイエネス 04/3/3(水) 22:36 回答
【11259】Re:変数の指定方法について かみちゃん 04/3/3(水) 23:16 回答
【11297】ありがとうございます。 初心者です 04/3/4(木) 21:08 お礼

【11251】変数の指定方法について
質問  初心者です  - 04/3/3(水) 21:23 -

引用なし
パスワード
   はじめまして。
マクロを勉強して1ヶ月の新人です。
シートの名前に(氏名一覧、集計、山田花子、○△□×、・・・)というシートがあって山田花子のシート以下数百名のシートがある状態です。名前の付いたシートは氏名一覧のシートに、名前をシートの順にA1のセルから下に列挙してあります。マクロで組みたいのは、集計の表にある各個人のデータを名前のシートにそれぞれ飛ばしたいのです。いろいろとやってみたのですが、エラー9の「インデックスが有効範囲にありません」というエラーがでてうまくいきません。どなたか手助けしていただけるとありがたいのですが、よろしくお願いします。

Sub 転記()

For i = 2 to 1179 step11

Sheetname=Worksheets("氏名一覧").cells((i-2)/11+1,1)

With Worksheets(sheetname)
 .range("c3")="=集計!b"&i
 .range("d10")="=集計!e"&i+1
 .range("e10")="=集計!g"&i+1
 .range("g10")="=集計!k"&i+1
 .range("h10")="=集計!p"&i+1
End With

Next

End sub

どうも、Sheetnameの変数の指定の仕方が間違っているようなのですが本当にわかりません。ここに、変数を無視してシートの名前を入れると、そのシートだけはうまく動くのです。是非とも教えてください。

【11257】Re:変数の指定方法について
回答  かみちゃん E-MAIL  - 04/3/3(水) 22:22 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>For i = 2 to 1179 step11
>
>Sheetname = Worksheets("氏名一覧").Cells((i - 2) / 11 + 1, 1)

このコードだと氏名一覧シートのA1〜A108に書かれているシート名が存在しない(たとえば空白セルとか)とエラーになります。
シート名の存在チェックをして、存在OKのものだけWith以下の処理をするようにすればいいのではないでしょうか?

【11258】Re:変数の指定方法について
回答  アイエネス  - 04/3/3(水) 22:36 -

引用なし
パスワード
   ちなみに、正しくは

Sub 転記()

For i = 2 To 1179 Step 11
sheetname = Worksheets("氏名一覧").Cells((i - 2) / 11 + 1, 1)
With Worksheets(sheetname)
 .Range("c3") = "=集計!b" & i
 .Range("d10") = "=集計!e" & i + 1
 .Range("e10") = "=集計!g" & i + 1
 .Range("g10") = "=集計!k" & i + 1
 .Range("h10") = "=集計!p" & i + 1
End With
Next i

End Sub

こういうコードですだと思います。
「nextの後にi」と「stepの後ろにスペース」です。
まぁ、ただの写し間違いかとは思いますが。

>このコードだと氏名一覧シートのA1〜A108に書かれているシート名が存在しない(たとえば空白セルとか)とエラーになります。

シート名または氏名一覧シートの名前の前後どちらかにスペースが含まれていたりしないですか?
シート名にスペースが入っているとタブの大きさが違うので一目でわかりますが、セル内のデータは後ろにスペースが入っていると見ただけではわからないので要注意です。

【11259】Re:変数の指定方法について
回答  かみちゃん  - 04/3/3(水) 23:16 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>>このコードだと氏名一覧シートのA1〜A108に書かれているシート名が存在しない(たとえば空白セルとか)とエラーになります。
>
>シート名または氏名一覧シートの名前の前後どちらかにスペースが含まれていたりしないですか?
>シート名にスペースが入っているとタブの大きさが違うので一目でわかりますが、セル内のデータは後ろにスペースが入っていると見ただけではわからないので要注意です。

シート名存在チェック関数を思い出したので、紹介しておきます。
シート名が存在した場合には、氏名一覧のシートの右側に○、存在しない場合は×を表示するようにしています。
また、変数の宣言などもついでにしてあります。

Option Explicit

Sub 転記()
 Dim i As Integer
 Dim SheetName As String
 
 For i = 2 To 1179 Step 11
  SheetName = Worksheets("氏名一覧").Cells((i - 2) / 11 + 1, 1)
  If chkSHEET(SheetName) Then
   Worksheets("氏名一覧").Cells((i - 2) / 11 + 1, 2) = "○"
   With Worksheets(SheetName)
    .Range("C3") = "=集計!b" & i
    .Range("D10") = "=集計!e" & i + 1
    .Range("E10") = "=集計!g" & i + 1
    .Range("G10") = "=集計!k" & i + 1
    .Range("H10") = "=集計!p" & i + 1
   End With
  Else
   Worksheets("氏名一覧").Cells((i - 2) / 11 + 1, 2) = "×"
  End If
 Next i
End Sub

'シート名存在チェック
'http://www.ken3.org/backno/backno_vba19.html#92
' シート名を受け取り、
' 判断結果をTrue , Falseで返す関数
'
'関数名:  chkSHEET
'入力値:  シート名文字列
'戻り値 :シートの有無・結果
'処理概要: シート名をパラメーターで受け取り、
'     アクティブなブック内に存在するかを確認し、
'     結果のTrue , Falseを戻り値で返す
Function chkSHEET(strSNAME As String) As Boolean

   Dim MyObject As Object

   'ブック内のシート名の比較を行う
   For Each MyObject In ThisWorkbook.Sheets
     '.Nameプロパティのシート名と受け取ったstrSNAMEを比べる
     If MyObject.Name = strSNAME Then
      chkSHEET = True '名前が一致したのでTrueをセット
      Exit Function  'もうすること無いので関数を抜ける
     End If
   Next

   '一つも一致しなかったので、Falseをセット
   chkSHEET = False

End Function

しかし、そもそも、「氏名一覧」と「集計」シート以外の全シートを処理するという方法ではいけないのでしょうか?

【11297】ありがとうございます。
お礼  初心者です  - 04/3/4(木) 21:08 -

引用なし
パスワード
   お忙しいところいろいろとありがとうございました。
結局プログラムは合っているのにと思って見直していたら、問題点は解決しました。

氏名一覧に書かれた名前をもとに数百人分のシートを一気に作ってしまうマクロを組んでいたのに、それを実行していませんでした。

自分でもあほらしくて・・・。
すいませんでした。

かみちゃんさんのシート名存在チェックは何かの機会に使えそうです。
ありがとうございます。

全シートの処理の意味がいまいち分からないのですが、もしかしたら、集計シートに散らばっている各個人の名前の所のデータを、名前を参照して名前のシートに飛ばしたりできるのでしょうか?(このマクロでは、シートの名前の順番に集計のデータを名前順に並べ替えないといけないのです。)
でも、これは難しそうなので、もうちょっと勉強します。

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