[[20190808050847]]　『シート間のコピペについて』（なのれい）　ページの最後に飛ぶ

『シート間のコピペについて』（なのれい）

いつもお世話になっております。

2つのシート間で変数、検索を用いてコピペをしたく思っております。

（作成シート）と（固定シフト）
の二つのシートがございます。

固定シフトのＡ3から下へ名前が入っております。
固定シフトのＢ3から下へＡ、Ｂ、休、空白の4パターンが入ります。

固定シフトのＢ3から空白かを判断し、空白なら下のセルへ、そうでなければＡ列の名前とＢ列のシフトを取得

作成シートへ移動

Ｂ列に名前が入っておりますので、取得した名前を検索し何行目かを取得
上記の行とＦ列のセルにさきほどのシフトをコピペ

これを固定シフトの最終行まで行いたいです。

Sub 練習1()

    Dim i As Integer
    Dim n As Integer
    Dim s As String
    Dim m As String

    n = Worksheets("固定シフト").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 3 To n

    Worksheets("固定シフト").Activate

      If Range("B" & i) = "" Then

      Else

      m = Range("B" & i).Value
      s = Range("A" & i).Value

      End If

Worksheets("作成シート").Activate

Range("F5").Value = m

    Next i

End Sub

現在出来ているのはここまでです。

困っている事として、
1　Next iの入れるタイミングがよくわからない。シートを行ったり来たりになる。
2　検索のコードの書き方が分からない。

アドバイス宜しくお願い致します。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 おはようございます　＾＾
わたしも修行中のみでございましてあまりえらそぉな事は申し上げられませんが
↑、きっともっとスマートな方法はあると思います　＾＾；。。。で、まっ
わたしなら。。。ということで　m(_ _)m
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
お困りの事案
１．Ｆｏｒ　～　Ｎｅｘｔ　の間を指定した回数を繰り返します。
２．もう一度作成シートのＢ列をループするか、Ｍａｔｃｈ、Ｆｉｎｄ等で名前の位置（行）を取得
とかで。。。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

 以下何かの足しにでも。
１．最初に固定シフトを配列に格納
２．1.を基に作成シートを操作

 Option Explicit
Sub 練習2IZ()
    Dim i As Integer
    Dim n As Integer
    Dim s As String
    Dim m As String
    Dim Buf As Variant
    Dim j As Long
    With Worksheets("固定シフト")
        n = .Cells(.Rows.Count, 1).End(xlUp).Row
        Buf = Intersect(.UsedRange, .Range(.Rows(3), .Rows(n)))
    End With
    With Worksheets("作成シート")
        n = .Cells(.Rows.Count, 2).End(xlUp).Row
        For i = 1 To UBound(Buf, 1)
            If Buf(i, 2) <> "" Then
                m = Buf(i, 2)
                s = Buf(i, 1)
                For j = 1 To n
                    If s = .Range("B" & j) Then
                        .Range("F" & j) = m
                    End If
                Next j
            End If
        Next i
    End With
End Sub
（隠居じーさん） 2019/08/08(木) 07:32

 回答ではありません

 作成シートにVLOOKUP関数を入力して、固定シフト　から　引っ張ってくればいいのでは？

 マクロの勉強でやっているのなら別ですけど
（渡辺ひかる） 2019/08/08(木) 11:12

みなさんありがとうございます。

自分なりに改良して上手く処理する事が出来ました。
ありがとうございます。
（なのれい） 2019/08/08(木) 18:03

◆１
＞自分なりに改良して上手く処理する事が出来ました。
余計なお世話かもしれませんが、それを提示してみてはどうでしょうか？
内容によっては添削してもらえるかもしれませんし、そうでなくても同じ悩みをもってこのトピックにたどり着いた方へのヒントになるとおもいます。

◆２

 Worksheets("作成シート").Activate 
 Worksheets("固定シフト").Activate

↑はたぶん要らないです。
おそらく、標準モジュールに記述しているとおもいますが、その場合、【シートを省略すると】ActiveSheetを指定したものとしてみなされます。
ここで重要なのは、【省略した場合】の話だということです。
したがってどのシートなのかきちんと指定すれば、いちいちシートを切り替える必要はありません。
なお、何度もWorksheets(○○)と書くのが煩わしければ、オブジェクト型の変数をつかうとか、Withステートメントを使うという手があります。

◆３
＞取得した名前を検索し何行目かを取得
ワークシート関数のmatch関数を使うとか、Findメソッドを使うとかいくつか方法はあるとおもいます。
ただ、いずれの場合も【見つからなかった場合】を考えておいたほうがよいです。
たとえば、↓のような場合、固定シフトの井上さん、榎本さんは、作成シートにありませんから、対策を考えておかないとエラーになっちゃいます。

【固定シフト】

 _____A______B__________
  2  名前  パターン
  3  足立    A
  4  井上    B
  5  上田
  6  榎本    A

【作成シート】

 ______A_____B_________
  1  名前  シフト
  2  鈴木
  3  上田
  4  足立    A

◆４
match関数を使った例を投稿します。
興味があればステップ実行して研究してみてください。
（テストしていないのでミスっていたらごめんなさい）

    Sub 練習2()
        Dim i As Long
        Dim 行 As Variant 'エラー値が格納できるようにVariant型にする

        Dim dstSh As Worksheet
        Set dstSh = Worksheets("作成シート")

        Stop

        With Worksheets("固定シフト")
            For i = 3 To .Cells(.Rows.Count, "A").End(xlUp).Row
                 If .Cells(i, "B").Value <> "" Then

                    'Match関数で検索する
                    行 = Application.Match(.Cells(i, "A").Value, dstSh.Range("A:A"), 0)

                    '変数「行」に格納されているのがエラー値でなければ処理
                    If Not IsError(行) Then
                        dstSh.Cells(行, "B").Value = .Cells(i, "B").Value
                    End If

                End If
            Next i
        End With

    End Sub

なお、どちらのシートでもいえること（特に、作成シート側）ですが、A列に重複があると、match関数では対処できないはずですので、そのような場合は別アプローチが必要だと思います。

（もこな２） 2019/08/09(金) 02:02

コメント返信：

[ 一覧(最新更新順) ]

[[20190808050847]] 『シート間のコピペについて』（なのれい） ページの最後に飛ぶ

[[20190808050847]]　『シート間のコピペについて』（なのれい）　ページの最後に飛ぶ