HOME > Q&A > Excel （VBA）

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

 
(Windows 11全般 : Excel 2021)
必要項目だけ抜き出して請求書を作成したい
投稿日時: 23/08/24 13:43:08
投稿者: mikkkkka

お世話になります。
質問させていただきます。
 
VBAを用いて、請求書を作成しようとしています。
 
請求書：請求書フォーマットシート
商品名：商品名・数量・単価などを入力してあるシートです
 
購入のなかった商品（数量0の商品）は反映されないようにしたいと思い、以下のように入力したのですが、
下から６番目の
「 Sheets("請求書").Range("B16").Resize(UBound(A, 1)) = A '品名」の部分がどうしても
エラー"g"となり、前に進めません。
請求書シートのB16セルに原因があるのでしょうか？
パット見問題なさそうなのですが…
 
どこを修正したらよいか教えていただきたいです。
宜しくお願い致します。
 
 
Sub 請求書作成()
    Range("A1").AutoFilter Field:=2, Criteria1:="<>" '数量で絞り込み
      
  　 Dim A, B, C, D
  With Sheets("商品名").Range("A1").CurrentRegion.Offset(1, 0)
    A = .Resize(.Rows.Count - 1).Columns(2) '品名
    B = .Resize(.Rows.Count - 1).Columns(3) '数量
    C = .Resize(.Rows.Count - 1).Columns(4) '単価
    D = .Resize(.Rows.Count - 1).Columns(5) '金額
     
  End With
   
  Sheets("請求書").Range("B16").Resize(UBound(A, 1)) = A '品名
  Sheets("請求書").Range("C16").Resize(UBound(B, 1)) = B '数量
  Sheets("請求書").Range("D16").Resize(UBound(C, 1)) = C '単価
  Sheets("請求書").Range("E16").Resize(UBound(D, 1)) = D '金額
  
End Sub
回答
投稿日時: 23/08/24 15:28:07
投稿者: taitani
投稿者のウェブサイトに移動

引用:
エラー g ？

 
実行時エラー '9':
インデックスが有効範囲にありません。
 
ではないでしょうか。
 
エラーで VBA が止まっているとき、
 
Sheets("請求書").Range("B16").Resize(UBound(A, 1)) = A '品名
 
A にマウスオーバーして、何が代入されているか確認してみてはいかがでしょうか。
あと、
 
引用:
Dim A, B, C, D

 
だと、A〜D の箱に何を入れるか指定されていません。
※まぁ指定しなくても、valiant になりますが。。。
 
A　は String
B 〜 D は Long と指定しましょう。
回答
投稿日時: 23/08/24 18:01:11
投稿者: simple

エラーの原因はコードだけでなく、データにも依存することもあり、原因はちょっとわかりません。
 
コードを拝見しての気づきです。
(1)そのやりかたですと、数量を絞り込んだ結果だけでなく、全体が転記対象になります。
   コピーペイストを使うと、オートフィルタがかかっているときには、
   自動的に、絞り込まれたものだけが対象になりますから、これを利用したほうがいいでしょう。
(2)連続した列を対象にするなら、列単位にする必要はなく、まとめて転記したほうが良いでしょう。
 
(3)商品名シートのA列は空白列なんでしょうか？ちょっと紛らわしいですね。
   以下はA列が空白だとして、B1を起点にすることにしています。
 
こんな感じではどうですか？ 

Sub 請求書作成2()
    Sheets("商品名").Range("B1").AutoFilter Field:=2, Criteria1:="<>" '数量で絞り込み

    'コピーの場合
    Sheets("商品名").Range("B1").CurrentRegion.Offset(1, 0).Copy _
            Sheets("請求書").Range("B16")

    'もし、値だけコピーするなら
    'Sheets("商品名").Range("B1").CurrentRegion.Offset(1, 0).Copy
    'Sheets("請求書").Range("B16").PasteSpecial Paste:=xlPasteValues
    
    'AutoFilterの解除
    Sheets("商品名").Range("B1").AutoFilter
End Sub

回答
投稿日時: 23/08/24 22:04:18
投稿者: simple

もし、
> 実行時エラー '9':
> インデックスが有効範囲にありません。
というエラーなら、
請求書シートのシート名の前後に半角スペースが入っていたりしませんか？
ありがちなエラーです。
念のため確認してください。
回答
投稿日時: 23/08/25 11:34:33
投稿者: simple

繰り返しになりますが、
・どうしても配列を経由したいのであれば、SpecialCellsを使った可視セルの限定が必要です。
・コピーペイストならそれは不要です。（フィルタの場合は、Excel側がそれを忖度してくれます）
 
なお、
(1)飛び飛びの列なら、列単位でコピーペイストするとよいでしょう。
(2)もし、フィルタ結果で該当なしとなる可能性があるのなら、Subtotal関数などを使って、
   有無チェックするとよいと思います。
   該当なしの場合、それにも拘わらず、全件がコピーされることがあります。
 （割と有名な振る舞いかもしれません。）

投稿日時: 23/08/26 09:58:57
投稿者: mikkkkka

お二方ともご回答ありがとうございます！
 
エラーｇではなく、９だったんですね！(笑)
また、
 > 請求書シートのシート名の前後に半角スペースが入っていたりしませんか？
 
半角スペース入っておりました…うっかりしていました( ；∀；)
 
結果としては、オートフィルターをかけたあと、
値をコピーするやり方で実行するとうまくいきました！
本当に助かりました！ありがとうございました！