[[20120304204945]]　『関数をVBAに』（プレ）　ページの最後に飛ぶ

『関数をVBAに』（プレ）

  関数を入れて休暇などをカウントしているのですが、VBAに入れるにはどうしたらいいのでしょうか？　教えて頂きたいのです。宜しくお願いします。

  現在、入っている関数

  ai7=COUNTIF(C7:AG7,"有休") aj7=COUNTIF(C7:AG7,"計画休") ak7=COUNTIF(C7:AG7,"午前休") al7=COUNTIF(C7:AG7,"午後休") am7=AI7+AK7*0.5+AJ7+AL7*0.5

  同じ関数が、７－１８まで杯っています。

  宜しくお願いします。

 いろんな方法があるけど、もっともＶＢＡらしくないコード（？）

 Sub Sample1()
    Dim z As Variant
    Dim i As Long

    For Each z In Array("有休", "計画休", "午前休", "午後休")
        Range("AI7:AI18").Offset(, i).Formula = "=COUNTIF(C7:AG7,""" & z & """)"
        i = i + 1
    Next

    Range("AM7:AM18").Formula = "=AI7+AK7*0.5+AJ7+AL7*0.5"

    With Range("AI7:AM18")
        .Value = .Value
    End With

 End Sub

 (ぶらっと

 で、ＶＢＡらしいといえばSample1よりＶＢＡらしいかもしれないSample2。
でも、自分が書くならSample1かな？

 Sub Sample2()
    Dim col As Range
    Dim c As Range
    Dim v As Variant
    Dim i As Long
    Dim x As Long

    Application.ScreenUpdating = False

    v = Array("有休", "計画休", "午前休", "午後休")
    For Each col In Range("AI7:AL18").Columns
        For Each c In col.Cells
            i = c.Row
            c.Value = WorksheetFunction.CountIf(Range("C" & i).Resize(, Columns("AG").Column - Columns("C").Column + 1), v(x))
        Next
        x = x + 1
    Next

    For Each c In Range("AM7:AM18")
        With c.EntireRow
            c.Value = .Range("AI1").Value + .Range("AK1").Value * 0.5 + .Range("AJ1").Value + .Range("AL1").Value * 0.5
        End With
    Next

    Application.ScreenUpdating = True

 End Sub

 (ぶらっと)

わかりやすさ優先で（マナ）

 ７とか18の部分に変数使えば、少しはVBAらしくなるかも。
 今のままじゃ修正も大変だし。

 Sub test()
    Range("AI7:AI18").Formula = "=COUNTIF(C7:AG7,""有休"")"
    Range("AJ7:AJ18").Formula = "=COUNTIF(C7:AG7,""計画休"")"
    Range("AK7:AK18").Formula = "=COUNTIF(C7:AG7,""午前休"")"
    Range("AL7:AL18").Formula = "=COUNTIF(C7:AG7,""午後休"")"
    Range("AM7:AM18").Formula = "=AI7+AK7*0.5+AJ7+AL7*0.5"
 End Sub

  ありがとうございます。

  Sheet1を入力用にしてSheet2から2012/4、Sheet3で2012/5・・・・Sheet13で2013/3にしています。
AMに集計した数字をだしてます。2012/4（Sheet2）のAM7で出した合計を、B7にこのように、=入力用!E5-'2011.4'!AM7して数字をだしています。

   最後の、2013/3（Sheet13）B7の数字を有休残数として、入力用（Sheet1）のF7に数字をだしています。
  どうでしょうか？難しいですか？

  （プレ）

 >どうでしょうか？難しいですか？

 VBAでできていることが関数ではできないこともあるけど、関数でできていることはVBAでも同じようにできる。

 ただし、スペックが明確じゃないとできない。
 最初の質問にあった計算をやらせたいのは入力用シート？それとも、2012.4～2013.3シート？それとも、全て？
 B7にある式は、全てのシートにあるの？
 E5 というのも初登場で、そこに何が入っているのかわからないけど気にしなくてもいいの？

 >最後の、2013/3（Sheet13）B7の数字を有休残数として、入力用（Sheet1）のF7に数字をだしています。

 これも、よくわからないけど、単純に、入力用シートのF7に　='2013.3'!B7 といれておけばいいんじゃない？
 単に、これだけならVBAコードで処理する必要もない。
 VBAでやりやいなら、
 Sheets("入力用").Range("F7").Value = Sheets("2013.3").Range("B7").Value だけど。

 (ぶらっと)

Sub test()

    Range("AI7").Formula = "=COUNTIF(C7:AG7,""有休"")"
    Range("AJ7").Formula = "=COUNTIF(C7:AG7,""計画休"")"
    Range("AK7").Formula = "=COUNTIF(C7:AG7,""午前休"")"
    Range("AL7").Formula = "=COUNTIF(C7:AG7,""午後休"")"
    Range("AM7").Formula = "=AI7+AK7*0.5+AJ7+AL7*0.5"

Range("AI8").Formula = "=COUNTIF(C8:AG8,""有休"")"

    Range("AJ8").Formula = "=COUNTIF(C8:AG8,""計画休"")"
    Range("AK8").Formula = "=COUNTIF(C8:AG8,""午前休"")"
    Range("AL8").Formula = "=COUNTIF(C8:AG8,""午後休"")"
    Range("AM8").Formula = "=AI8+AK8*0.5+AJ8+AL8*0.5"

 End Sub

  こんな感じで７－１８まで入っています。

Sub test()

    Range("AI7").Formula = "=COUNTIF(C7:AG7,""有休"")"
    Range("AJ7").Formula = "=COUNTIF(C7:AG7,""計画休"")"
    Range("AK7").Formula = "=COUNTIF(C7:AG7,""午前休"")"
    Range("AL7").Formula = "=COUNTIF(C7:AG7,""午後休"")"
    Range("AM7").Formula = "=AI7+AK7*0.5+AJ7+AL7*0.5"

 End Sub
これを VBAPRojectのSheet入れたのですが、数字が表示されませんでした。
なぜでしょうか？

（プレ）

 まず、お願いしたのは、何をしたいかを言葉で（関数とかＶＢＡコードとかじゃなく）説明してもらえないかな？　ということ。
 で、次に、アップされたコードだけど、私のコードはわかりにくかったとして、(マナ)さんのアドバイスが
 全く活かされてないのが残念。

 ところで、アップされたコードは、どこに記述したのかな？
 標準モジュールとかシートモジュールとか、いくつかあるんだけど。
 今回のテーマなら標準モジュール。ただし、複数シート対応は、もちろん必要だけど。
 なので、計算式を適用するシートは、どれとどれ？　と質問している。

 >これを VBAPRojectのSheet入れたのですが、数字が表示されませんでした。
 >なぜでしょうか？

 コードは、書いただけでは動かない。このプロシジャを実行しなきゃいけないよ。
 ただ、アップされたものでは、期待する結果は得られないと思う。なんとなく。
 じゃぁ、どんなコードがいいんだ？　ということになるけど、それを提案するために、いくつか
 質問しているので、それに回答もらえたらうれしいな。

 (ぶらっと)

  言葉足らずで申し訳ございませんでした。

  シートに関数を入れておきたくないのです。

  有休の管理Sheetを作成しています。

  A7～A18に名前、B7～B18有休残数　C7列～AG7列まで有休などを選択できるように入力規則を入れています。
  C5～AG5までは曜日が入っています。C6～AG6までは日付が入っています。

  その後ろに、書かして頂いた関数を入力してカウントできるようにしたのです。ai7=COUNTIF(C7:AG7,"有休") aj7=COUNTIF(C7:AG7,"計画休") ak7=COUNTIF(C7:AG7,"午前休") al7=COUNTIF(C7:AG7,"午後休") am7=AI7+AK7*0.5+AJ7+AL7*0.5

  上記が、Sheet2から2012/4、Sheet3で2012/5・・・・Sheet13で2013/3に入っています。
  入力用は、最初の有休がいくつあるか入力しています。ここから引くように出来るのではないかと作成したのです。

  今後、VBAを勉強していくうえで、今はメンテしやすいほうがいいのかと思い、（マナ）さんのを参考にさせていただきました。決して（ぶらっと）さんのが良くないとかではありません。

  コードを入力したのは、標準モジュールです。

  ご面倒をお掛けして申し訳ございません。

  （プレ）

 >今はメンテしやすいほうがいいのかと思い、（マナ）さんのを参考にさせていただきました。

 私もそう思います。指摘したのは、（プレ）さんは、｢こんな感じで７－１８まで入っています。｣
 一方、(マナ)さんのコードをよく見てほしいんだけど、
 Range("AI7:AI18").Formula = "=COUNTIF(C7:AG7,""有休"")"
 これは、AI7からAI18に、一挙に12個の式をいれることができる構文。

 シートに式を残したくないんだよね。
 そういう場合は、私のコードでやっているように、式をセットした後、
 当該セル領域.Value = 当該セル領域.Value と記述すれば、式が値に変更される。

 次に、ブックの中のすべてのシートに対応させる場合、たとえば以下のように。
 (マナさんの構文を借用)

 Sub Sample()
    Dim sh As Worksheet

    For Each sh In Worksheets   'ブックの中からシートを1枚ずつとりだす
        '必要なら、sh.Name にシート名が入っているので、それを判定して、このシートは処理しないとか、このシートを処理するといったことが可能。(If 文を使う)
        sh.Range("AI7:AI18").Formula = "=COUNTIF(C7:AG7,""有休"")"
        sh.Range("AJ7:AJ18").Formula = "=COUNTIF(C7:AG7,""計画休"")"
        sh.Range("AK7:AK18").Formula = "=COUNTIF(C7:AG7,""午前休"")"
        sh.Range("AL7:AL18").Formula = "=COUNTIF(C7:AG7,""午後休"")"
        sh.Range("AM7:AM18").Formula = "=AI7+AK7*0.5+AJ7+AL7*0.5"

        sh.Range("AI7:AM18").Value = sh.Range("AM7:AM18").Value '式の結果を値に変換

    Next

 End Sub

 (ぶらっと)

 こんにちは。横から失礼します。

 シートに式を残したくない理由は何ですか？
 ブックが重いからですか？
 数式を他人に見られたくないとか？
 それともなにか他の理由でしょうか。

 ご質問を拝見するに、目的は有給の残日数をリアルタイムで知ることと思われますが
 だとしたら、だれかが休暇を取るたびに計算し直す必要があります。
 そのたびに１年分のシートに(VBAで)数式を入れ 値に変換する……間違いではありませんが
 無駄な作業がおおいと思いませんか。

 ではどうすればいいか、と考えると
 シートに式を残したくない理由によって最適解は変わってきます。
 （ことによると、数式を残すのがいちばん合理的ということもあり得ます。）

 －佳－

コメント返信：

[ 一覧(最新更新順) ]

[[20120304204945]] 『関数をVBAに』（プレ） ページの最後に飛ぶ

[[20120304204945]]　『関数をVBAに』（プレ）　ページの最後に飛ぶ