Excel VBA質問箱 IV

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

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


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

【62024】行の挿入について(続き) mori 09/6/17(水) 13:17 質問[未読]
【62031】Re:行の挿入について(続き) kanabun 09/6/17(水) 14:18 発言[未読]
【62033】Re:行の挿入について(続き) mori 09/6/17(水) 19:39 発言[未読]
【62034】Re:行の挿入について(続き) kanabun 09/6/17(水) 19:51 発言[未読]
【62035】Re:行の挿入について(続き) kanabun 09/6/17(水) 20:22 回答[未読]
【62049】Re:行の挿入について(続き) mori 09/6/18(木) 21:19 お礼[未読]
【62037】Re:行の挿入について(続き) kanabun 09/6/17(水) 21:52 発言[未読]
【62036】Re:行の挿入について(続き) かみちゃん 09/6/17(水) 20:58 発言[未読]
【62050】Re:行の挿入について(続き) mori 09/6/18(木) 21:25 お礼[未読]

【62024】行の挿入について(続き)
質問  mori  - 09/6/17(水) 13:17 -

引用なし
パスワード
   >kanabunさん

何度も何度もありがとうございます。
取り急ぎ、概要を提示します。

元データはCSVファイルです。
CSVファイルを読み込んで、bookの一番最後に「TMP1」としてシートを作ってあります。
このシートはCSVデータそのままです。

  摘要   数量
(1) 文房具類         
  A4画用紙 200  枚  200    4000
  えんぴつ 200  本  100    2000
  消しゴム 200  個   50    1000
  荷造費   1  式        2000
  諸経費   1  式        1000
  合計               10000

このCSVデータを下記のようにします。
(書式は既にあるシートです)

行番号 摘要   数量  単位  単価  金額
(1)   文房具類
    A4画用紙 200   枚   200   4000
    えんぴつ 200   本   100   2000
    消しゴム 200   個   50   1000

    荷造費   1   式       2000

    諸経費   1   式       1000
    合計               10000
    (この下に1行空白行)

概要としては、行番号の()は'を付けて文字列扱いにする。
単位欄に「式」・単価欄がブランクであれば、上に1行挿入する。
合計欄の下に1行挿入する。
その他の文字などはそのまま入力する。
です。

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

【62031】Re:行の挿入について(続き)
発言  kanabun  - 09/6/17(水) 14:18 -

引用なし
パスワード
   ▼mori さん:

>概要としては、行番号の()は'を付けて文字列扱いにする。
>単位欄に「式」・単価欄がブランクであれば、上に1行挿入する。
>合計欄の下に1行挿入する。
>その他の文字などはそのまま入力する。
>です。

シートのレイアウトが出てきたのでずいぶん分かりやすくなりましたね(^^

それなら、こうしたらどうですか?
Else のなかに
もうひとつ If文をおいて、そこに
>単位欄に「式」・単価欄がブランクであれば、上に1行挿入する。
特別処理を挿入しておく。

Sub CreateMitsumori▲5()

 Dim i          As Long
 Dim j          As Long
 Dim lngMaxRow      As Long
 Dim newLine       As Long
 Dim str行番号 As String
 Dim str適用 As String
 Dim str数量 As String
 Dim str単位 As String
 Dim str単価 As String
 Dim str金額 As String
 Dim Trim適用 As String '適用項目 トリミング後

 With Worksheets(3)
  '一時シートから行数を取得
  lngMaxRow = Worksheets("TMP1").Cells(Rows.Count, 1).End(xlUp).Row
  j = 1
  For i = 31 To lngMaxRow
   If j = 1 Then
   ElseIf j Mod 29 = 1 Then
    'A列〜U列まで 29行をCopy
    .Range("A4").Resize(29, 21).Copy .Cells(j + 3)
    With .Cells(j + 3).Resize(29, 21)
      .RowHeight = 27
      Worksheets(3).PageSetup.PrintArea = .Cells
    End With
   End If
   j = j + 1
  Next

  newLine = 4 '最初の貼り付け先行番号
  For i = 31 To lngMaxRow
   
   str行番号 = Worksheets("TMP1").Cells(i, 1).Value
   str適用 = Worksheets("TMP1").Cells(i, 2).Value
   str数量 = Worksheets("TMP1").Cells(i, 3).Value
   str単位 = Worksheets("TMP1").Cells(i, 4).Value
   str単価 = Worksheets("TMP1").Cells(i, 5).Value
   str金額 = Worksheets("TMP1").Cells(i, 6).Value

   '転記開始
   '行番号欄に記載のあったとき
   If Len(str行番号) > 0 Then
     'マイナス行番号を カッコ付き行番号にします
     If str行番号 Like "-*" Then
      str行番号 = "'(" & Mid(str行番号, 2) & ")"
     End If
     .Cells(newLine, 1).Value = str行番号
   End If
   
   '摘要欄の文字列チェック
   Trim適用 = Application.Trim(str適用)
   If InStr(Trim適用, "値引") Then '値引があれば
    Trim適用 = "値引"       '値引を抜き出します
   End If
   
   If Trim適用 = "合計" Then
    .Cells(newLine, 2).Value = str適用 '合計
    .Cells(newLine, 6).Value = str金額
    newLine = newLine + 1      '◆下に空行挿入
   
   ElseIf Trim適用 = "値引" Then
    newLine = newLine + 1      '◆上に空行挿入
    .Cells(newLine, 2).Value = str適用
    .Cells(newLine, 6).Value = str金額
   
   Else
    '---------------------------------------------- 挿入
    '単位欄が「式」で 単価欄に記載がないときは
    If str単位 = "式" And str単価 = "" Then
      newLine = newLine + 1    '◆上に空行挿入
    End If
    '---------------------------------------------------
    .Cells(newLine, 2).Value = str適用
    .Cells(newLine, 3).Value = str数量
    .Cells(newLine, 4).Value = str単位
    .Cells(newLine, 5).Value = str単価
    .Cells(newLine, 6).Value = str金額
   End If
   
   newLine = newLine + 1       '◆次の転記行

  Next

 End With

End Sub

あいかわらず、検証してませんので、不具合があれば
ご指摘ください。

【62033】Re:行の挿入について(続き)
発言  mori  - 09/6/17(水) 19:39 -

引用なし
パスワード
   kanabun さん

ご回答をありがとうございます。
早速、やってみました。

(前略)
> With Worksheets(3)
>  '一時シートから行数を取得
>  lngMaxRow = Worksheets("TMP1").Cells(Rows.Count, 1).End(xlUp).Row
>  j = 1
>  For i = 31 To lngMaxRow
>   If j = 1 Then
>   ElseIf j Mod 29 = 1 Then
>    'A列〜U列まで 29行をCopy
>    .Range("A4").Resize(29, 21).Copy .Cells(j + 3) ←この部分でエラーが出ます
>    With .Cells(j + 3).Resize(29, 21)
>      .RowHeight = 27
>      Worksheets(3).PageSetup.PrintArea = .Cells
>    End With
>   End If
>   j = j + 1
>  Next

.Range("A4").Resize(29, 21).Copy.Cells(j + 3)
A4から29行・21列コピーする
pasteがないからか?と思ったのですが、うまくいきませんでした・・・

【62034】Re:行の挿入について(続き)
発言  kanabun  - 09/6/17(水) 19:51 -

引用なし
パスワード
   ▼mori さん:

>(前略)
>> With Worksheets(3)
>>  '一時シートから行数を取得
>>  lngMaxRow = Worksheets("TMP1").Cells(Rows.Count, 1).End(xlUp).Row
>>  j = 1
>>  For i = 31 To lngMaxRow
>>   If j = 1 Then
>>   ElseIf j Mod 29 = 1 Then
>>    'A列〜U列まで 29行をCopy
>>    .Range("A4").Resize(29, 21).Copy .Cells(j + 3) ←この部分でエラーが出ます
>>    With .Cells(j + 3).Resize(29, 21)
>>      .RowHeight = 27
>>      Worksheets(3).PageSetup.PrintArea = .Cells
>>    End With
>>   End If
>>   j = j + 1
>>  Next
>
>.Range("A4").Resize(29, 21).Copy.Cells(j + 3)
>A4から29行・21列コピーする
>pasteがないからか?と思ったのですが、うまくいきませんでした・・・

ごめんなさい。
その部分はノーチェックでした。
(ちょっと、この部分、何をやってるのか分からなかったです。)


オリジナルの

> With Worksheets(3)
>  '一時シートから行数を取得
>  lngMaxRow = Worksheets("TMP1").Range("A1").SpecialCells(xlLastCell).Row

>  j = 1
>  For i = 31 To lngMaxRow
>   If j = 1 Then
>   ElseIf j Mod 29 = 1 Then
>    .Range("A4:U32").Copy
>    lngRowY = j + 3
>    lngRowX = j + 31
>    strRangeValue = "A" & lngRowY & ":" & "U" & lngRowX
>    .Range(strRangeValue).PasteSpecial xlPasteAll
>    .Range(strRangeValue).RowHeight = 27
>    .PageSetup.PrintArea = .Range(strRangeValue)
>   End If
>   j = j + 1
>  Next

に差し替えてお試しください。
(変数の宣言 strRangeValue や lngRowY lngRowX も必要です)

【62035】Re:行の挿入について(続き)
回答  kanabun  - 09/6/17(水) 20:22 -

引用なし
パスワード
   逆にこちらから質問なんですけど、
オリジナルのコードにあるこの部分は
何をしている部分なんでしょうか?

想像するに転記先シートWorksheets(3) の
.Range("A4:U32") に印刷用書式の設定された表のひな型
があって、「TMP1」のデータ行数が29行以上あるときは
超過分に必要な行数(29の倍数)だけ、このひな型29行を
下方にコピーしているようですが?

> With Worksheets(3)
>   '一時シートから行数を取得
>   lngMaxRow = Worksheets("TMP1").Range("A1").SpecialCells(xlLastCell).Row
>   blnFlag = False
>   j = 1
>   For i = 31 To lngMaxRow  
>    If j = 1 Then
>    ElseIf j Mod 29 = 1 Then
>     .Range("A4:U32").Copy
>     lngRowY = j + 3
>     lngRowX = j + 31
>     strRangeValue = "A" & lngRowY & ":" & "U" & lngRowX
>     .Range(strRangeValue).PasteSpecial xlPasteAll
>     .Range(strRangeValue).RowHeight = 27
>     .PageSetup.PrintArea = .Range(strRangeValue)
>    End If
>    j = j + 1
>   Next
>   

そうすると、
> 行番号 摘要   数量  単位  単価  金額
という見出し行は 何行目にあるのですか?
4行目ですか?
3行目ですか?

また、項目数は 6項目なので
> .Range("A4:U32")
U列までコピーする必要はないような気がするのですが、これは
なぜでしょう?

よろしければ、教えてください。

【62036】Re:行の挿入について(続き)
発言  かみちゃん  - 09/6/17(水) 20:58 -

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

横から失礼します。
前回のスレッドでYukiさんのアドバイスに基づき解決されているのではないでしょうか?
一方で、kanabunさんのアドバイスでも解決を勧められているようですが、何がまだ
解決できないのでしょうか?

>取り急ぎ、概要を提示します。

概要というものは、最初の質問で、
・シートレイアウト
・現在のコード
・何に困っているのか?
 エラーが出るなら、エラーメッセージとどのコードでエラーが出ているのか
・期待している結果
を示すべきだと思います。

つまり、概要を出すのが今ごろですか?
という感じなのです。

moriさんのシートやデータは、掲示板を見ている者には、見えないので、
今後は、そのあたりのことを十分配慮していただきたいと思います。

さて、小言はこのあたりにしておいて、

CSVファイルですが、

"摘要","数量"
"(1)","文房具類"
,"A4画用紙",200,"枚",200,4000
,"えんぴつ",200,"本",100,2000
,"消しゴム",200,"個",50,1000
,"荷造費",1,"式",,2000
,"諸経費",1,"式",,1000
,"合計",,,,10000

というものなのですか?

それを以下のようにしたいということなのでしょうか?

   A     B    C    D    E    F
1 行番号 摘要   数量  単位  単価  金額
2 (1)   文房具類
3     A4画用紙 200   枚   200   4000
4     えんぴつ 200   本   100   2000
5     消しゴム 200   個   50   1000
6
7     荷造費   1   式       2000
8
9     諸経費   1   式       1000
10     合計               10000
11    (この下に1行空白行)

行番号の質問なわけですから、何行目にデータがあるのかくらいはきちんと説明
していただきたいと思います。

ちなみに、投稿欄の右下にある「等幅」というところにチェックょすると、
等幅フォントになり、シートレイアウトなどが綺麗に見えると思います。

【62037】Re:行の挿入について(続き)
発言  kanabun  - 09/6/17(水) 21:52 -

引用なし
パスワード
   こんばんは。

あと、ま、これは蛇足ですけど、
コピー元のデータを 配列 に入れておいて、
もうひとつの 配列 に 条件付きで行処理をして転記して
最後にこの配列をまとめて転記先の範囲に値だけ書き込む
という手もありまして。。。

配列内でデータを再配置するのは高速化の常用手段ですので
参考までにサンプルをアップしておきます。

新規「標準モジュール」を追加してそこに
Option Explicit 行からコピーして お試しください。
(注)
 ・CSVデータは「TMP1」シートの31行目からあるものと仮定。
 ・転記先シートは Worksheets(3) としています。
 ・このシートの3行目に項目見出しがあり、
   4行目以降にデータを貼り付けるものと仮定しています。
 このあたりは 適宜修正してください。

'-------------------------------------- 新規Module
Option Explicit

Enum 列番号
 行番号 = 1
 適用 = 2
 数量 = 3
 単位 = 4
 単価 = 5
 金額 = 6
End Enum


Sub 見積り作成6()
 Dim WS As Worksheet
 Dim i  As Long
 Dim j  As Long
 Dim newLine As Long
 Dim PostAddLine As Long
 Dim v 'もとのデータ
 Dim u '転記用データ

 With Worksheets("TMP1") '一時シートのデータを配列に入れる
  v = .Range("A31", .Cells(Rows.Count, 2).End(xlUp)) _
    .Resize(, 6).Value
 End With
 
 '転記先シートの書式Copy  'この部分 吟味されてません
 Set WS = Worksheets(3)
 If UBound(v) > 29 Then
   With WS.Range("A4")
     .Resize(29, 6).Copy .Offset(29)
     .Offset(29).Resize(29, 6).RowHeight = 27
   End With
 End If
 
 ReDim u(1 To 29, 1 To 金額)
 newLine = 1
 For i = 1 To UBound(v)
   PostAddLine = 0
   '行番号欄に記載のあったとき
   str行番号 = v(i, 行番号)
   If Len(str行番号) > 0 Then
     'マイナス行番号を カッコ付き行番号にします
     If str行番号 Like "-*" Then
      v(i, 行番号) = "'(" & Mid(str行番号, 2) & ")"
     End If
   End If
   
   '転記行 条件チェック
   If v(i, 適用) Like "*合*計*" Then
    PostAddLine = 1         '◆下に空行挿入
   
   ElseIf v(i, 適用) Like "*値*引*" Then '「値引」の文字あり
    newLine = newLine + 1      '◆上に空行挿入
   
   ElseIf v(i, 単位) Like "式" And IsEmpty(v(i, 単価)) Then
    '単位欄が「式」で 単価欄に記載がないときは
      newLine = newLine + 1    '◆上に空行挿入
   End If
   '指定の行に値転記
   For j = 1 To 金額
    u(newLine, j) = v(i, j)
   Next
   newLine = newLine + 1 + PostAddLine  '◆次の転記行
 Next
  
 WS.Range("A4").Resize(newLine - 1, 6).Value = u

End Sub

【62049】Re:行の挿入について(続き)
お礼  mori  - 09/6/18(木) 21:19 -

引用なし
パスワード
   kanabun さん

レスが遅くなってしまって、申し訳ありません。
何度も何度も本当にありがとうございます。

>想像するに転記先シートWorksheets(3) の
>.Range("A4:U32") に印刷用書式の設定された表のひな型
>があって、「TMP1」のデータ行数が29行以上あるときは
>超過分に必要な行数(29の倍数)だけ、このひな型29行を
>下方にコピーしているようですが?
ご想像の通りです。
Worksheets(3)に印刷用の書式がある雛形が1ページ分のみ
ある状態です。

>そうすると、
>> 行番号 摘要   数量  単位  単価  金額
>という見出し行は 何行目にあるのですか?
>4行目ですか?
>3行目ですか?
見出し行は3行目・4行目にあります。
1行目・2行目には、この印刷書式に必要なものが書いてあります。
(固定値です

>また、項目数は 6項目なので
>> .Range("A4:U32")
>U列までコピーする必要はないような気がするのですが、これは
>なぜでしょう?
この印刷書式はA3になっており、G列以降にはCSVからのデータでは
ないものを、別途書き込むようになっています。
それでU列までのコピーが必要でした。

本当に長い間、ご迷惑をおかけして申し訳ありませんでした。
ありがとうございました。

【62050】Re:行の挿入について(続き)
お礼  mori  - 09/6/18(木) 21:25 -

引用なし
パスワード
   かみちゃん さん

至らないところばかりで、ご迷惑をおかけして大変申し訳ありませんでした。
本当にありがとうございました。
以後、この掲示板に相談することがありましたら、かみちゃん さんのご指摘の
点を踏まえて、相談します。
申し訳ありませんでした。

Yukiさんのアドバイスは下から上へ・・・と言うものだったのですが、
kanabunさんのアドバイスは上から下へ・・・と言うものだったので、
お聞きしたかったのです。


私が表現したかったのは、かみちゃん さんのレイアウトの通りです。
CSVデータもかみちゃん さんが表して頂いた状態でした。

長い間、本当にありがとうございました。

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