Excel (VBA)

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

 
(Windows 10 Home : Excel 2016)
計算結果の取得を固定列行から範囲での列行に変更したい
投稿日時: 18/04/15 23:16:06
投稿者: RiceGod

実現したいことは以下のB列(計算結果)取得になります。
シートのフォーマット
     A列    B列(計算結果)
1 10000    10800 
2 11000    11880
3 12000 12960
 
@現在列行指定でのVBAはできましたが、上記の様に各行に計算をさせたい
A出来れば、A列に数値入力の度の結果が表示できればと思います
※Privat イベントでできればなお嬉しいです。
 
Sub 税込み価格計算()
   Dim 税抜き価格 As Long
   Dim 税込み価格 As Double
   '「税抜き価格」の値を取得する
   税抜き価格 = Range("A2").[/i]Value   '("A2")をA列の何れかに入力された時に変更 
   '取得した「税抜き価格」を元に、「税込み価格」を計算する
   税込み価格 = 税抜き価格 * 1.08
   '計算した価格=「税込み価格」を出力する
   Range("B2").Value = 税込み価格    '("B2")をA列の数値の隣へ変更 
End Sub
宜しくお願いします。

回答
投稿日時: 18/04/16 04:08:21
投稿者: もこな2

質問の内容がよくわからないです。
単純に読むなら

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tmp As Range
    
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        For Each tmp In Intersect(Target, Columns(1))
            If tmp.Value <> "" And IsNumeric(tmp.Value) Then
                tmp.Offset(0, 1).Value = tmp.Value * 1.08
            End If
        Next tmp
    End If
End Sub

こんな感じかなとおもうんですが、Evaluateメソッドを角カッコで代用されるようなスキルをお持ちの方がするような質問とも思えないので、私が質問のとらえ違いをしてるような気がします
 
とりえあず上記コードは、A列のどこかに数値の入力があったら、1.08をかけて同じ行のB列に出力するといった仕掛けになっています。(なってる・・・・ハズ)
参考になれば幸いです。

投稿日時: 18/04/16 21:11:43
投稿者: RiceGod

もこな2さんご回答ありがとうございます。
私の意図することができました。感謝します。
ご質問でアップしたVBAは色々なサイトの引用で意味も分からず作成したものです。
 
もなこ2さん、ご面倒でも、今回のVBAの解説など頂けるとありがたいのですが?
 
Rangeオブジェクトの場合も「Set tmp=〇〇」の様に範囲等を記載するサイトが多いのですが
このSetに代わるものはどの部分なのでしょうか?・・・無意味な質問かもしれませんが宜しくお願いします。
 
サイト引用で自分なりに注釈を付けてみましたが、自分でも意味不明です。
※65歳のオヤジがVBAに挑戦中なので、意味不明な質問ご容赦下さい。
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tmp As Range
      '※tmp(変数)'セル及びセル範囲
     
    If Not Intersect(Target, Columns(2)) Is Nothing Then
    'Columeは列を指定する関数
    '戻り値がNothingでないときを判定するには、If Not Intersect(引数1, 引数2) Is Nothing Thenと記述します。
        For Each tmp In Intersect(Target, Columns(2))
                'For Eachは操作対象とするオブジェクトはInの後に指定します。Next tmp迄
             
            If tmp.Value <> "" And IsNumeric(tmp.Value) Then
             
            'IsNumeric(tmp.Value) Thenは文字列を数値に変換できるかどうか調べる
                              
               tmp.Offset(0, 2).Value = tmp.Value * 1.08
            '変数(tmpをOffset(0,2)にtmp(入力された値)*1.08された値を表示する
             
                         
            End If
                 
        Next tmp
         
    End If
 
End Sub

回答
投稿日時: 18/04/16 22:14:57
投稿者: よろずや

RiceGod さんの引用:
Rangeオブジェクトの場合も「Set tmp=〇〇」の様に範囲等を記載するサイトが多いのですが
このSetに代わるものはどの部分なのでしょうか?
For Each tmp In Intersect(Target, Columns(2))
ここで
Set tmp = Intersect(Target, Columns(2)).Cells(i)
と同等のことをしています。

投稿日時: 18/04/16 22:36:59
投稿者: RiceGod

よろずやさん、ありがとうございます。
全くのド素人ですみません。
 
.Cells(i)セル「i」は変数になるのでしょうか?
 
 
For Each tmp In Intersect(Target, Columns(2))
ここで
Set tmp = Intersect(Target, Columns(2)).Cells(i)

回答
投稿日時: 18/04/16 23:06:19
投稿者: もこな2

RiceGod さんの引用:
ご質問でアップしたVBAは色々なサイトの引用で意味も分からず作成したものです。
なるほどそうだったんですね。
私も完璧に理解してるわけではないのでわかる範囲で解説します。
(しばらく待ってみるとほかの回答者さんが補足してくれるかもです)
 
Private Sub Worksheet_Change(ByVal Target As Range)
まず、「イベントでできればなお嬉しいです。」とのことだったので、おそらくA列のどこかに(入力)があったら、B列に値を出したいのだろうと推測しました。
 
このように、入力したら〜、ダブルクリックしたら〜、右クリックしたら〜などのように、特定の動作をマクロの発動条件にするには、「イベント」というものを使います。(よくある例では、ボタンがクリックされたらマクロを実行するClickイベントとかでしょうか)
※今回は、シートのどこかに入力(値の変化)があったらマクロが発動する「Change」イベントを使用。
 
【参考】
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_event.html
 
 
Dim tmp As Range
そのまま、「tmp」という名前のRange(オブジェクト型)変数を宣言しています。
 
 
If Not Intersect(Target, Columns(1)) Is Nothing Then
この部分は、先に「Intersect」が何かということを理解しておいたほうがよいとおもいますので、↓を見てから読み進めてください。
 
【参考】
http://officetanaka.net/excel/vba/tips/tips118.htm
 
Changeイベントでは、どのセルに入力(値の変化)があっても、マクロが動いてしまいます。
しかし、実際にはA列に入力があった時だけ動いてほしいわけですから、変更のあったセルのうちA列だけ実行するようにしなければいけませんし、もっと言えばA列以外に入力したときは、処理をしないように設計する必要があります。
 
なので、この部分では値の変化があったセル(範囲)にA列が含まれているかを確認し、A列が含まれている時だけ処理をするように条件分岐を行っています。
 
 
For Each tmp In Intersect(Target, Columns(1))
 (略)
Next tmp
この部分では、For Each〜Nextステートメントという記述方法をつかってループ処理をさせています。
 
ご質問から、複数のセルを一度に入力することがあるのか無いのかわからなかったので、どちらにも対応できるようにこのような記述をしています。
また、Columns(1)については、Range("A:A")でもよかったんですが、格好よくしてみました!
 
これらを具体例をあげて説明すると、
A1:D5というセル範囲が一度に値が変更された場合

TargetにRange("A1:D5")がセットされた状態でマクロがスタート

Intersect(Target, Columns(1))の部分で「A1:D5」とA列が交わる部分、つまりA1:A5というセル範囲を求めて

For Each tmp Inの部分で、A1〜A5というセルの集まりから1つずつ取り出して、tmpにセットしてループ
 
というような動きになります。
 
 
If tmp.Value <> "" And IsNumeric(tmp.Value) Then
この部分では、見てわかる通り2つの条件を判定しています。
1つ目の「tmp.Value <> ""」は、そもそもChangeイベントは値の変化があったときに、動作するものなので、入っていた値をクリアしただけも動作してしまいます。
しかし、今回のケースでいえば、A列に入力した値に1.08をかけたいのであって、ブランクへの掛け算はする必要がないというより、ブランクへの掛け算って意味不明ですよね。なので、ブランクでないことを条件の1つにしています。
 
2つ目の「IsNumeric(tmp.Value)」は、たとえば、「あいうえお」という文字列に1.08を掛けようとおもっても数字じゃないから無理ですよね。
なので、tmpの値が数値であることを条件の2つ目にしています。
 
 
tmp.Offset(0, 1).Value = tmp.Value * 1.08
見たままですが、tmpから見て0行下、1列右のセルに、tmpの値に1.08を掛けた値を設定しています。
 
ふまえて、↓のようにしてもよいかもですね。興味があればステップ実行して研究してみてください。
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tmp As Range
    
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Stop
        Debug.Print "変更のあったセルは" & Target.Address(0, 0)
        Debug.Print "処理対象は" & Intersect(Target, Columns(1)).Address(0, 0)
        
        For Each tmp In Intersect(Target, Columns(1))
            With tmp
                Debug.Print "tmpのアドレス→" & .Address(0, 0)
                Select Case True
                    Case .Value = ""
                        .Offset(, 1).Value = ""
                    Case IsNumeric(.Value)
                        .Offset(0, 1).Value = .Value * 1.08
                End Select
            End With
        Next tmp
    End If
End Sub

投稿日時: 18/04/16 23:32:13
投稿者: RiceGod

もこな2さん
ありがとうございます。大変具体的な解説を付けて頂き大変助かります。
このご回答をコピーし保存させて頂きます。
感謝・感謝です
 
本当にありがとうございました。
 
また、ご質問させて頂きたいと思います。
宜しくお願い致します。
 
RiceGod