Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1807 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【71749】単価計算をLoop処理で行うVBAマクロの作成 つぐみ 12/4/5(木) 21:38 質問[未読]
【71750】Re:単価計算をLoop処理で行うVBAマクロの作... つぐみ 12/4/5(木) 21:40 発言[未読]
【71757】Re:単価計算をLoop処理で行うVBAマクロの作... UO3 12/4/6(金) 5:25 回答[未読]
【71758】Re:単価計算をLoop処理で行うVBAマクロの作... UO3 12/4/6(金) 9:18 発言[未読]
【71760】Re:単価計算をLoop処理で行うVBAマクロの作... つぐみ 12/4/6(金) 10:13 質問[未読]
【71761】Re:単価計算をLoop処理で行うVBAマクロの作... つぐみ 12/4/6(金) 10:24 質問[未読]
【71762】Re:単価計算をLoop処理で行うVBAマクロの作... ちん 12/4/6(金) 10:30 発言[未読]
【71764】Re:単価計算をLoop処理で行うVBAマクロの作... UO3 12/4/6(金) 16:42 発言[未読]
【71780】Re:単価計算をLoop処理で行うVBAマクロの作... UO3 12/4/7(土) 20:49 発言[未読]
【71802】Re:単価計算をLoop処理で行うVBAマクロの作... つぐみ 12/4/9(月) 17:05 お礼[未読]
【71749】単価計算をLoop処理で行うVBAマクロの作成
質問  つぐみ  - 12/4/5(木) 21:38 -
引用なし
パスワード
   VBA超初心者です。
製品の単価を求めるマクロを作る問題です。


型名　　　　　　　数量　単価計算　　　　契約金額合価
PGR3062N6 　　　　1　　　　　　　　288,770
PGBFU71F　　　　　　　　 1 　　　　　　　　77,720
PGBRU4EP7　　　　　　　　 1　　　　　　　　10,880
PGBRM4EP7　　　　　　　　 1　　　　　　　　22,400
PGBARR1S　　　　　　　　 1　　　　　　　　640
PGB2U48HL　　　　　　　　 1　　　　　　　　6,700
PGBHUB05E5　　　　1　　　　　　　　64,640
PGBHDB05E　　　　　　　　 1　　　　　　　　73,600
PGBHDB05E　　 　　　　1　　　　　　　　73,600
PGBHDB05E 　　　　1　　　　　　　　73,600
PGBHDB05E 　　　　1　　　　　　　　73,600
PGBHDB05E 　　　　1　　　　　　　　73,600
PGB228BL　　　　　　　　 1　　　　　　　　16,640
PGB289L1　　　　　　　　 1　　　　　　　　16,640
PG-LCD01　　　　　　　　 1　　　　　　　　12,800
PG-R1TH1　　　　　　　　 1　　　　　　　　3,200
PG-CBLDP15　　　　1　　　　　　　　2,560
GP5-R1UP8　　　　　　　　 1　　　　　　　　74,880
TPABL010M　　　　　　　　 2　　　　　　　　3,120
PG-CBLA008　　　　1　　　　　　　　6,400

＊条件＊
・紙にどのように設計したらいいかを書く事。
・自動マクロを有効活用する。
・セルはRnangeプロパティにて作成する事。
・変数は自分がわかる名前を付ける事。

分かる方いらしたらどうぞ教えてください。
超初心者なので分かりやすく教えていただけたら幸いです。
【71750】Re:単価計算をLoop処理で行うVBAマクロの...
発言  つぐみ  - 12/4/5(木) 21:40 -
引用なし
パスワード
   数量の列がばらばらになってしまいすみません。。。
【71757】Re:単価計算をLoop処理で行うVBAマクロの...
回答  UO3  - 12/4/6(金) 5:25 -
引用なし
パスワード
   ▼つぐみ さん：

おはようございます

処理の方法は、もっと処理時間を少なくする工夫も含めて、いろいろあります。
コードの書き方そのものも、セルの指定の方法も含めて、様々です。
ここでは、基本的な、できるだけわかりやすいコードにしてみました。
コードにコメントを入れてありますが、わからないことがあったら質問してください。
なお、Val関数についてはヘルプで検索して読んでみてください。例も含めてわかりやすく説明されています。
コードの中で使っている Row.Count は、今動かしているエクセルのバージョンでの最大行数です。
2003なら65536、2007以降なら1048576が入っています。End関数は、シートの最終行のセルから上方向に
空白ではないセルを見つけます。

Sub Sample()
　　Dim myRow As Long　　　 '処理する行
　　Dim maxRow　As Long　　 'データの最終行
　　Dim qty As Long　　　　 '数量
　　Dim amt As Long　　　　 '金額
　　Dim prc As Double　　　 '計算結果の単価
　　'データの最終行を求める定番コード
　　maxRow = Range("A" & Rows.Count).End(xlUp).Row
　　'2行目からデータ最終行まで繰り返し処理
　　For myRow = 2 To maxRow
　　　　qty = Val(Range("B" & myRow).Value) '念のため、セルが数値以外の場合でも無理矢理数値に変換
　　　　amt = Val(Range("D" & myRow).Value) '念のため、セルが数値以外の場合でも無理矢理数値に変換
　　　　
　　　　If qty <> 0 Then '単価がゼロでないときのみ処理
　　　　　　prc = amt / qty
　　　　　　'計算結果をシート関数のROUNDを使って小数点以下２桁までで丸める。
　　　　　　'丸めについては、つぐみさんの要件で調整してください。
　　　　　　prc = WorksheetFunction.Round(prc, 2)
　　　　　　Range("C" & myRow).Value = prc　　　'計算結果を転記
　　　　Else
　　　　　　Range("C" & myRow).Value = "計算不能"
　　　　End If
　　Next myRow　'次の行へ繰り返し
　　
　　MsgBox "計算が終了しました"
　　
　　　　　　
End Sub
【71758】Re:単価計算をLoop処理で行うVBAマクロの...
発言  UO3  - 12/4/6(金) 9:18 -
引用なし
パスワード
   ▼つぐみ さん：

いったん追加メモをアップしましたが、説明に不備があったので削除しました。
不備を訂正して、以下、再掲します。

================================================

追加です。

>・自動マクロを有効活用する。

データ最終行を求めるために、End関数でシート最終行から上方向に検索しました。
もう１つ、A列のデータ最終行までに空白セルがまい場合、
A1から下方向に検索して求める方法もあります。

maxRow = Range("A1").End(xlDown).Row


A1を選択して、Shft/↓（下矢印キー）　を押す操作をマクロ記録すると、
このコードの要素も含めたコードが自動生成されます。

また、処理そのもので、提示された例で言いますと以下の方法もあります。
(B列とD列のデータ最終行までに空白セルがないという前提ですが）

１．D2を選択
２．Ctrl/Shift/↓　これで、D2からD列のデータ最終行までのセルが選択されます。
３．Ctrl/c でコピー
４．C1を選択して、Ctrl/v でペースト
３．B2を選択
２．Ctrl/Shift/↓　これで、B2からB列のデータ最終行までのセルが選択されます。
４．C2を選択して、形式を選択して貼り付け。除算にチェックを入れてＯＫ．
５．ESCキーをおして、コピーモードを解除。

こうすることでも、単価が計算されます。
で、これをマクロ記録しますと、フルセットの処理コードが生成されます。
生成されたコードは、データが何行になろうと、利用可能です。
【71760】Re:単価計算をLoop処理で行うVBAマクロの...
質問  つぐみ  - 12/4/6(金) 10:13 -
引用なし
パスワード
   ▼UO3 さん：

回答ありがとうございます。
続けてすみませんが、質問させてください。

私が添付した資料が最終行までに空白がある場合はどうしたらいいのでしょう？
【71761】Re:単価計算をLoop処理で行うVBAマクロの...
質問  つぐみ  - 12/4/6(金) 10:24 -
引用なし
パスワード
   ▼U03さん

すみません、私の補足ミスでした。

D2＝契約金額合価の「288,770」
C1＝「単価計算」
B2＝数量の「１」
C2＝単価計算の「空欄」

と解釈してよろしかったでしょうか？
【71762】Re:単価計算をLoop処理で行うVBAマクロの...
発言  ちん  - 12/4/6(金) 10:30 -
引用なし
パスワード
   ▼つぐみ さん：
▼U03さん　の処理を紙で書くとこんな感じです。
フローチャートです。（1週間以内のUP)

HTTP://firestorage.jp/download/1b9fb15f8b856ae373175928633339b158b866cb
【71764】Re:単価計算をLoop処理で行うVBAマクロの...
発言  UO3  - 12/4/6(金) 16:42 -
引用なし
パスワード
   To ちんさん　わかりやすいフルーチャートありがとうございます。

To つぐみさん

　是非、ちんさんのフルーチャートをダウンロードして、それを追いかけながら
　コードと見比べてください。より、わかりやすく理解できると思います。

　まず、ごめんなさい　というところがあります。

>A1を選択して、Shft/↓（下矢印キー）　を押す操作をマクロ記録すると、
>このコードの要素も含めたコードが自動生成されます。

これは間違いでした。（汗）
Shift/↓　ではなく　Ctrl/↓　でした。

　さて、

　提示したコードや操作の手順は
　・１行目がタイトル行
　・データは２行目から
　・A列：型名、B列：数量、C列：単価、D列：契約金額合計
　こうなっています。これでいいんですよね。
　で、単価計算はC2から下のセル領域ですが、ここに何かはいっていても空白でもかまいません。

>私が添付した資料が最終行までに空白がある場合はどうしたらいいのでしょう？

途中に空白セルがある場合は、コメントしましたように、
最終行のセル.End(xlUp) で求めます。アップしたコードは、このようになっています。
ただ、それを操作で行い、かつマクロ記録するとなると、ちょっと骨が折れます。
（といっても、たいしたことはありません）
・最終行を把握したい列の本当のエクセル最終行のセルを選択します。（たとえば A63356）
・そこから　Ctrl/↑　をおします。空白をはさんだ、本当のデータ最終行のセルが選択されます。
これをマクロ記録しますと、End(xlUp) のコードが生成されます。
この時、生成されるコードの固定で入っている、65536 等の数値は、Rows.Count という変数で
置き換えます。

Range("A65536").select　　--> Range("A" & Rows.Count).Select

で、合わせてアップした、コピペ操作による処理に関しては

１．D2を選択
２．Ctrl/Shift/↓　これで、D2からD列のデータ最終行までのセルが選択されます。

　これは、D2から、D列のデータ最終行（たとえばD20)まで選んで、選択してください。

３．B2を選択
２．Ctrl/Shift/↓　これで、B2からB列のデータ最終行までのセルが選択されます。

　これは、B2から、B列のデータ最終行（たとえばB20)まで選んで、選択してください。

で、この一連の操作によるマクロ記録で生成されたコードは、先に申し上げたものと異なり
以下のように、B20 とか、D20 といった「固定値」がはいっています。
Range("D2:D20").Select　や　Range("B2:B20").Select
この 20 の部分を　別途取得した最終行を格納した変数（たとえば、その変数名を MaxRowとしますと）
Range("D2:D" & MaxRow).Select というように直します。
【71780】Re:単価計算をLoop処理で行うVBAマクロの...
発言  UO3  - 12/4/7(土) 20:49 -
引用なし
パスワード
   ▼つぐみ さん：

こんばんは

マクロ記録について参考情報です。

表題「Loopで」とあります。
アップしたコードはループ構文の基本ですから是非、理解して習得してください。
一方で、課題として「マクロ記録」と書かれていますね。
アップしたコードそのものは、エクセル操作では実現できませんので
エクセル操作をそのままコードに置き換えるマクロ記録もできません。

ですので、後で説明した操作をマクロ記録したケースでお話しします。
(２００３で実行したケースです）

まず、途中に空白セルのある列のデータ最終行。
エクセルシートの本当の最終行を選択して、Ctrl/↑　をおしますと
そこにいたるまでのスクロールバーの動きも記録されますが、それを取り除くと

Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2012/4/7　ユーザー名 : UO3
'

'
　　Range("A65536").Select
　　Selection.End(xlUp).Select
End Sub

このようなコードが生成されます。
上記操作を行いますと、データ最終行のセルが「選択」されますので
そのセル.Select となっていますが、求めたいのは、その行番号ですね。
そのセル.Row が求める行番号です。
それと、前にコメントしたとおり、65536 は2003のシートの最終行番号ですが
2007以降は、この値も変化してきますので、ここは固定値ではなく、
「動いているエクセルのバージョンにおけるシートの最終行」である　Rows.Count を
使うことをおすすめします。

で、ここが重要なんですが、人間の操作をコピーするマクロ記録の宿命で
やたらと　○○○.Select -> Selection.□□□　というペアが生成されます。
これらは、ほとんど　○○○.□□□　と記述できますし、記述すべきです。

ということを加味しますと、Macro1 は以下のようになります。

Sub Macro1補正()
　　Dim maxRow As Long
　　maxRow = Range("A" & Rows.Count).End(xlUp).Row
End Sub

で、次に、ご説明した、コピーによる単価計算。データが20行あったとすれば、
説明した操作でできあがるコードは以下になります。

Sub Macro2()
'
' Macro2 Macro
' マクロ記録日 : 2012/4/7　ユーザー名 : UO3
'

'
　　Range("D2:D20").Select
　　Selection.Copy
　　Range("C2").Select
　　ActiveSheet.Paste
　　Range("B2:B20").Select
　　Application.CutCopyMode = False
　　Selection.Copy
　　Range("C2").Select
　　Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
　　　　:=False, Transpose:=False
　　Application.CutCopyMode = False
End Sub

まず、この　Select/Selection の部分をお化粧直しします。

Sub Macro2補正_1()
　　Range("D2:D20").Copy Range("C2")
　　Range("B2:B20").Copy
　　Range("C2").PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
　　　　:=False, Transpose:=False
　　Application.CutCopyMode = False
End Sub

で、次に、固定値で生成された 20 を、データ最終行としての変数で与えます。
データ最終行を求めるMacro1補正と合成しますと、最終的には以下になりますね。

Sub Macro2補正_2()
　　Dim maxRow As Long
　　maxRow = Range("A" & Rows.Count).End(xlUp).Row
　　Range("D2:D" & maxRow).Copy Range("C2")
　　Range("B2:B" & maxRow).Copy
　　Range("C2").PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
　　　　:=False, Transpose:=False
　　Application.CutCopyMode = False
End Sub
【71802】Re:単価計算をLoop処理で行うVBAマクロの...
お礼  つぐみ  - 12/4/9(月) 17:05 -
引用なし
パスワード
   ▼UO3さん、ちんさん：

お返事が遅くなり申し訳ありませんでした。

自分の少ない知識を振り絞って何とか理解することが出来ました。
本当にありがとうございました。

また何かあればご相談させて頂くかもしれませんがその時はよろしくお願いします。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
1807 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free