[[20200815092356]]　『vbaで特定の範囲で置換する方法』（とむ）　ページの最後に飛ぶ

『vbaで特定の範囲で置換する方法』（とむ）

エクセルvbaで特定の範囲で文字を置換する方法を教えてください。

１行目（A～D）はタイトル行になっており、２行目以降にデータが入っています。

　日付　　時間　　値　　状態
2020/8/1　1：00　 25　　a
2020/8/1　2：00　 20　　b
2020/8/1　3：00　 25　　b
2020/8/1　4：00　 85　　a
2020/8/1　5：00　 21　　b
2020/8/1　6：00　 10　　a

このようにデータが続くのですが、2020/8/1の2：00～5：00までの間のb→aに置換したいです。

・A1を検索開始位置にして、そこから「2020/8/1」をfindで検索するようにして、A2が該当する。
・そこから、offset（1,3）からoffset（4,3）を範囲とする。
・bをaに置換するようにreplaceする。
という流れかと思ったのですが、作り方が分かりませんでした。

もちろん、A列が「2020/8/1」、B列が「2：00」のときのD列（D3）～A列が「2020/8/1」、B列が「5：00」のときのD列（D6）というように時間も含めて検索して、範囲を特定できる方法があれば良かったのですが、やり方が分からなかったので、上の方法を考えました。

（注）
・D列すべてをb→aを置換するのではありません。あくまで特定の期間（2020/8/1の2：00～5：00）だけ変更したいです。
・その都度データ数が異なります。そのため、2020/8/1～2020/8/10がデータとなるときや、2020/7/1～2020/8/20がデータとなるときがあります。そのため、上の例のように常にD2～D6が範囲となるとは限らないので、「2020/8/1の2：00のデータの場所」から「2020/8/1の5：00のデータの場所」と検索する方法にしてください。

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

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

 整っているデーダですので、下記の手順でマクロの記録試してみてください
 オートフィルタで日付と時間をフィルター
 ジャンプ機能で、可視セルを選択
 置換

 FINDより簡単かと思います
（稲葉） 2020/08/15(土) 10:18

直接の回答ではないですが、関数ではダメなのでしょうか？

状態(new)の列をE列に作って
=IF(AND(A2+B2>=DATEVALUE("2020/8/1")+TIMEVALUE("2:00"),A2+B2<=DATEVALUE("2020/8/1")+TIMEVALUE("5:00")),"b",D2)

もっと言えば、『2020/8/1 2:00』と『2020/8/1 5:00』をどこかのセル（例えば、G1,G2）に入力しておけば、
=IF(AND(A2+B2>=$G$1,A2+B2<=$G$2),"b",D2)
とできます。

シートやブックが大量にあるため、vbaでおこなう必要があるということであれば、
上記アルゴリズムをそのまま式にすると、

 For i = 2 To 7
     If Cells(i, 1) + Cells(i, 2) >= #8/1/2020 2:00:00 AM# And _
         Cells(i, 1) + Cells(i, 2) <= #8/1/2020 5:00:00 AM# Then
         Cells(i, 4) = "b"
     End If
 Next i
といったところでしょうか。
一案として。
速度的には、おそらく稲葉さんご提案のオートフィルタの方が早いと思います。
ただ個人的に、オートフィルタのVBAコードはやや難しいかなと思ったので、
文法が比較的簡単なものを提案いたしました。
（DS） 2020/08/15(土) 17:05

稲葉様

ご回答ありがとうございます。
オートフィルターで作成したらうまくいきました。

例示した場合ですと、2020/8/1しかbが出てこないのですが、実際は2020/8/2のbもaに変換したいです。
すると、まずはA列は2020/8/1、B列は置換したい時間をオートフィルターしてb→aに置換した後、A列は2020/8/2、B列は置換したい時間をオートフィルターしてb→aと２日に分けてマクロを書くことになりますでしょうか。
（2020/8/1と2020/8/2で同じ時間があります）

よろしくお願いいたします。
（とむ） 2020/08/16(日) 00:35

DS様

ご回答ありがとうございます。
D列上で置換したいので、E列にデータ作成すると、そのあとの手順に不都合なのでダメです。
一時的にE列を使用し、最終的にD列に反映させて、一時的に作成したE列は削除するというマクロだったらＯＫです。
（とむ） 2020/08/16(日) 00:40

 >例示した場合ですと、2020/8/1しかbが出てこないのですが、実際は2020/8/2のbもaに変換したいです。

 意味が解りません。
 オートフィルタの使い方を知らないのでしょうか？
（Why） 2020/08/16(日) 00:55

'Ａ１セルのみ入力　２時から５時までｂに変更
'A1,B1ｾﾙのみ入力その日付と時刻をｂに変更
'A1～D1まで入力その日付と時刻の間をbに変更

Sub macro()
Dim st As Object
Application.ScreenUpdating = False 'true
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

'日付＋時間の検索のための作業列（ＡＡ）に作成
Cells(2, "aa").Value = "検索日付時間1"
Cells(2, "ab").Value = "検索日付時間2"
Range("aa3:aa" & lastrow).Value = "=a3+b3"

'Ａ１セルのみ入力　２時から５時までｂに変更
'A1,B1ｾﾙのみ入力その日付と時刻をｂに変更
'A1～D1まで入力その日付と時刻の間をbに変更

If Cells(1, 2).Value <> "" Then

  If Cells(1, 3).Value <> "" Then
        Cells(1, "aa") = "=a1+b1"
        Cells(1, "ab") = "=c1+d1"
  Else
        Cells(1, "aa") = "=a1+b1"
        Cells(1, "ab") = "=a1+b1"
  End If
Else
    Cells(1, "b") = "2:00"
    Cells(1, "c") = "=a1"
    Cells(1, "d") = "5:00"

    Cells(1, "aa") = "=a1+b1"
    Cells(1, "ab") = "=c1+d1"
End If

Range("aa:ab").NumberFormatLocal = "yyyy/m/d h:mm"
Range("a2:aa" & lastrow).AutoFilter 27, ">=" & Cells(1, "aa"), xlAnd, "<=" & Cells(1, "ab")

On Error Resume Next
Set st = Range("d3:d" & lastrow).SpecialCells(xlCellTypeVisible)
If st Is Nothing Then
MsgBox "該当する日付がありません"
Else
st.Value = "b"
End If

On Error GoTo 0

ActiveSheet.AutoFilterMode = False
Range("aa:ab").Clear
Application.ScreenUpdating = True
End Sub

（110） 2020/08/16(日) 01:08

ごめん　追加です。

a1セルに日付　8/1
b1セルに時間 1:00
c1セルに日付2 8/1
d1セルに時間2 3:00

を入力してください
（110） 2020/08/16(日) 01:17

 >あくまで特定の期間（2020/8/1の2：00～5：00）だけ変更したい
 この条件はいったい？
 8/2も条件に含むなら、オートフィルタに複数条件指定すればできますよ！
 もちろん、ご自身でおっしゃる通り、繰り返し処理もできます。

 2時から5時は決まっていて、日付だけずれる形でしょうか？
 8/1は2時から5時
 8/2は3時から4時など、都度条件がかわるなら、手作業のほうがよろしいのではないでしょうか？
（稲葉） 2020/08/16(日) 08:56

110様

正直、あまり理解できていない部分もあるのですが。。。
一から書いていただきありがとうございます。

（とむ） 2020/08/16(日) 23:35

稲葉様

ご連絡ありがとうございます。
最初に書いた通り、find等を使用してマクロを作るのかと思ったので、実際より条件が簡単なもので記載しました。

たとえば、2020/8/1　1：00～2020/8/5　5：00といったように、日付をまたぐが、連続している場合である場合は、一度の命令で抽出できますか？

よろしくお願いいたします。
（とむ） 2020/08/16(日) 23:44

とむさんへ
オートフィルタの機能では期間で抽出が可能です。
日付の抽出は書式の影響を受けますので、非常にデリケートになります。
?@日付の期間で抽出して
?A時間の期間でさらに絞ることで結果を取得できますが、めんどくさいので、
日付＋時間をＡＡ列に作って日付＋時間～日付＋時間で抽出すると非常にスマートになります。

私の上のコードを貼り付けて
a1セルに日付　8/1
b1セルに時間 1:00
c1セルに日付2 8/5
d1セルに時間2 5:00

を入力してみて、F8キーで確認してもらえらたらイメージしていただけると思います。

（110） 2020/08/17(月) 05:27

あ、ごめん。

1行目が　検索
２行目がﾀｲﾄﾙ
3行目からデータ

とおもっていました。

まずは１行下げて使ってみてください。

ﾀｲﾄﾙ行は不要やったんですね。
（110） 2020/08/17(月) 05:34

 >たとえば、2020/8/1　1：00～2020/8/5　5：00といったように、日付をまたぐが、連続している場合である場合は、一度の命令で抽出できますか？
 条件まとめてもらえませんか？
 こういう場合があるって例を羅列していただければ結構です。
 別に連続していなくとも、条件の指定方法だけ考慮すればよいのでは対応できますよ。
 連続だけでいいなら、110さんのコードそのまま使ってみてください。
 なんとなくですけど、日時以外に判
断する条件がありそうですけどねぇ
（稲葉） 2020/08/17(月) 05:47

Sub main()

    Dim c As Range, r As Range
    For Each c In Range("A:A").SpecialCells(2)
        If c.Value = DateValue("2020/8/1") And IsDate(c.Offset(, 1).Text) Then
            If TimeValue(c.Offset(, 1).Text) >= TimeValue("2:00:00") And TimeValue(c.Offset(, 1).Text) <= TimeValue("5:00:00") Then
                If r Is Nothing Then
                  Set r = c.Offset(, 3)
                Else
                  Set r = Union(r, c.Offset(, 3))
                End If
            End If
        End If
    Next c
    r.Replace What:="b", Replacement:="a"
End Sub
（mm） 2020/08/17(月) 11:17

コメント返信：

[ 一覧(最新更新順) ]

[[20200815092356]] 『vbaで特定の範囲で置換する方法』（とむ） ページの最後に飛ぶ

[[20200815092356]]　『vbaで特定の範囲で置換する方法』（とむ）　ページの最後に飛ぶ