[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『キーを検索し上にあるセルをキーの下と入れ替えたい』（麦茶）
シフト作成で9：00〜15：00までA列にセルが並んでいます。
値がブロックのセルの上の「出」をブロックの下へ移動したいのですができません。下記では「出」がコピーされるだけなのですがどこを変更したら良いかおしえてください。
Sub Sample()
Dim fnd As Range, key
key = "ブロック"
Set fnd = Range("A:A").Find(key, LookAt:=xlWhole)
If fnd Is Nothing Then Exit Sub
fnd.Offset(-1).Copy
fnd.Offset(-1).Insert Shift:=xlDown
End Sub
A
９_出
10_出
11_出
12_ブロック
13_昼
14_出
15_出
16_出
17_出
＜ 使用 Excel：unknown、使用 OS：unknown ＞
Sub test()
Dim r As Long
r = WorksheetFunction.Match("ブロック", Range("B1").Resize(Cells(Rows.Count, "A").End(xlUp).Row), 0)
Cells(r - 1, 1).Resize(, 2).Cut
Cells(r + 1, 1).Resize(, 2).Insert Shift:=xlDown
End Sub
（フォーキー） 2023/08/01(火) 19:42:19
すみません、麦茶さんのコード見てませんでした。 途中まで完成してましたね。 マクロの記録をすれば分かると思いますが、行列の入れ替えはコピーではなくカットです。 あとオフセットの引数は省略しないほうがいいらしいです。（office田中さんが言ってた気がします）
Sub Sample2()
Dim fnd As Range, key
key = "ブロック"
Set fnd = Range("A:A").Find(key, LookAt:=xlWhole)
If fnd Is Nothing Then Exit Sub
fnd.Offset(-1, 0).Cut
fnd.Offset(1, 0).Insert Shift:=xlDown
End Sub
（フォーキー） 2023/08/01(火) 19:47:18
セルの値だけ入れ替えれば良いのなら、こんなことで済むのでは？
Sub Sample()
Dim fnd As Range
Dim key As String
Dim v As Variant
key = "ブロック"
Set fnd = Range("A:A").Find(key, LookAt:=xlWhole)
If fnd Is Nothing Then Exit Sub
v = fnd.Offset(-1).Value
fnd.Offset(-1).Value = fnd.Value
fnd.Value = v
End Sub
（xyz） 2023/08/01(火) 20:17:52
（麦茶） 2023/08/01(火) 22:54:56
おわりの3行を
v = fnd.Offset(-1).Value
fnd.Offset(-1).Value = fnd.Offset(1).Value
fnd.Offset(1).Value = v
とすればよいのでは？
というよりも私の最初の回答が間違っていたのかな。 他人に回答に先に目が行き、質問をよく読んでいなかったです。 失礼しました。 # 回答修正。
（xyz） 2023/08/01(火) 23:08:57
v = fnd.Offset(-1).Value
fnd.Offset(-1).Value = fnd.Offset(1).Value
fnd.Offset(1).Value = v
この3行を順番に動かすなんてイメージできませんでした。
ありがとうございます。助かりました！
（麦茶） 2023/08/02(水) 00:16:57
Sub さんぷる()
Dim fnd As Range
Set fnd = Range("A:A").Find("ブロック", LookAt:=xlWhole)
If Not fnd Is Nothing Then
fnd.Cut
fnd.Offset(-1).Insert Shift:=xlDown
End If
End Sub
（もこな２） 2023/08/02(水) 07:55:10
（麦茶） 2023/08/02(水) 16:12:18
C＿＿＿＿D＿＿＿E・・・・
９_ 出
10_ 出
11_出 出 出
12_ブロック 出 ブロック
13_昼 休
14_出
15_休
16_出
17_出
Sub sumple3で検索範囲をRange("C4:C14")だけだと入れ替えができるのですが
Sub sumple3()
Dim fnd1 As Range, fnd2 As Range
Dim key1 As String, key2 As String
Dim v As Variant, i As Variant
key1 = "ブロック"
key2 = "休"
Set fnd1 = Range("C4:C14").Find(key1, LookAt:=xlWhole)
Set fnd2 = Range("C4:C14").Find(key2, LookAt:=xlWhole)
If fnd1.Offset(-1) = "出" Then
If fnd1 Is Nothing Then Exit Sub
v = fnd1.Offset(-1).Value
fnd1.Offset(-1).Value = fnd2
fnd2.Value = v
End If
End Sub
sumple4で検索範囲を横に広げたつもりの下記だと実行時エラー91 オブジェクト変数または With ブロック変数が設定されていません。とエラーになります。解決策を教えてください。
Sub sumple4()
Dim fnd1 As Range, fnd2 As Range
Dim key1 As String, key2 As String
Dim v As Variant, i As Variant
key1 = "ブロック"
key2 = "休"
For i = 3 To 9
Set fnd1 = Range(Cells(4, i), Cells(14, i)).Find(key1, LookAt:=xlWhole)
Set fnd2 = Range(Cells(4, i), Cells(14, i)).Find(key2, LookAt:=xlWhole)
If fnd1.Offset(-1) = "出" Then
If fnd1 Is Nothing Then Exit Sub
v = fnd1.Offset(-1).Value
fnd1.Offset(-1).Value = fnd2
fnd2.Value = v
End If
Next
End Sub
（麦茶） 2023/08/03(木) 23:53:52
サンプルデータにもD列には「ブロック」や「休」がありませんから
ここで検索をかけるとfnd1，fnd2は空のまま。
それに対してoffsetやValueをつけるとエラーになります。
（火災報知器） 2023/08/04(金) 01:50:53
エラーの原因は火災報知器さんの指摘通りだと。
Findしたあと、結果が Nothing でないか確認してから次の処理に進むようにしましょう。
あと、Forループ内で Exit Sub しちゃうと「ブロック」が見つからないとき、 それ以降の列の処理が実行されません。
For i = 3 To 9
Set fnd1 = Range(Cells(4, i), Cells(14, i)).Find(key1, LookAt:=xlWhole)
If Not fnd1 Is Nothing Then
If fnd1.Offset(-1) = "出" Then
Set fnd2 = Range(Cells(4, i), Cells(14, i)).Find(key2, LookAt:=xlWhole)
If Not fnd2 Is Nothing Then
v = fnd1.Offset(-1).Value
fnd1.Offset(-1).Value = fnd2
fnd2.Value = v
End If
End If
End If
Next
（hatena） 2023/08/04(金) 02:57:08
お二方から回答いただいたとおりかと思います。
いくつか蛇足を。
(1)変数の型
当初、セルの内容が色々なものがあるかと想像し、dim v As Variantとしましたが、
このケースでは As Stringが適切でした。
なお、Dim i As Variantは意味不明。Dim i As Longがよいでしょう。列番号という数値なので。
(2)"休"は"ブロック"のあとにある"休"という意図でしょうから、
Set fnd2 = Range(Cells(4, i), Cells(14, i)).Find(key2, After:=fnd1, LookAt:=xlWhole)
として、"fnd1の次から検索する"のだ、という指示を出しておいたほうが安全です。
(3)
v = fnd1.Offset(-1).Value
fnd1.Offset(-1).Value = fnd2.Value
fnd2.Value = v
の3行は、この種の話で出てくるお決まりのコードです。
その都度書くのは面倒なので、
「二つのセルの内容を入れ替える」というサブプロシージャにするとよいかもしれません。
Sub myExchange(r1 As Range, r2 As Range)
Dim v As Variant '数値文字列混在も考慮してあえてVariant
v = r1.Value
r1.Value = r2.Value
r2.Value = v
End Sub
を追加作成します。
こうしておけば、
Call myExchange(fnd1.Offset(-1), fnd2)
の一行で交換が完了します。 また、コードを見ただけで意味(意図かな)が明確に読み取れます。一考してみては？
(4)【まったく勝手な感想です。スキップいただいて結構です】 全般に、色々なパターンがあるでしょうから、それらを逐一コードにしていると、 どれを使うか迷いませんか？ すべてをロジックに落とし込むことができれば、効率的ですが、 所詮そういうものは、アドホックな処理になるので、 フリーハンドの領域と割り切ることも有効かもしれません。
例えば、 ・Ctrlキーを押しながら二つのセルを選択して、 ・その二つ値を交換するマクロを書き、 それをなんらかのショートカットキーに登録しておいて それを使いまわすといった、原始的な方法のほうが、 却って人間には使い勝手がいいかもしれません。
（xyz） 2023/08/04(金) 09:04:40
（麦茶） 2023/08/04(金) 10:39:31
> Set 変数名 = セットするオブジェクト > If Not 変数名 Is Nothing Then > これでオブジェクトの状態を空にすることを理解しました。
「オブジェクトの状態を空にする」のではなく、 「変数にオブジェクトが入っているかどうか」を判定してます。 入っていないとエラーになるので、入っている場合のみ次の処理に進むようにしてます。
（hatena） 2023/08/04(金) 11:02:47
After:=fnd1 の追加は、休がふたつあったら有効ですが、 一巡りすれば、上にある"休"も検索してしまうので、 検索範囲を限定して、fnd1.Rowから14行目までを検索するようにしないとダメですね。 余り伝わっていないようですけど。
>サブルーチン化是非チャレンジ というか、もう示したんですけどねえ。 まあ、頑張ってください。 （xyz） 2023/08/04(金) 14:11:30
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.