ExcelVBAを用いて日付を検索して別のシートへ転記

sheet1,sheet2(1，2という名前)がありA1からE1まで順にID、名前、コメント、前回日、次回日という列がありこのうち次回日(E1)の日付を検索し、その日付と一致したsheet2の行の1から5列目までをsheet1に転記したい。なお検索ワードとなる日付はuserformのcombobox1にリストから選択していれるようにしています。
実際に書いたコード

Private Sub CommandButton1_Click()

    Dim LastRowX As Long
    LastRowX = Worksheets("1").Cells(Rows.Count, 1).End(xlUp).Row
    Dim LastRowY As Long
    LastRowY = Worksheets("2").Cells(Rows.Count, 1).End(xlUp).Row
    
      Range("A2", Cells(LastRowX, 5)).ClearContents
   
    Dim myRange As Range
    Dim myObj As Range
    Dim KeyDate
 　　 Set myRange = Worksheets("2").Range(Worksheets("2").Cells(2, 5), 　　　　
　　　　　　　　　　　Worksheets("2").Cells(LastRowY, 5))


    KeyDate = CDate(ComboBox1.Text)
    Set myObj = myRange.Find(KeyDate, LookIn:=xlFormulas)                
    
   　 If myObj Is Nothing Or ComboBox1 = "" Then
   　　　 MsgBox ComboBox1 & "はいません。"
    　　　Exit Sub
     Else
    
    Dim mycell As Range
    Set mycell = myObj
    
    Do
       Worksheets("2").Range(Cells(mycell.Row, 1), Cells(mycell.Row, 5)).Copy
       Worksheets("1").Range(Cells(LastRowX + 1, 1), Cells(LastRowX + 1, 5)).Paste
       Set mycell = myRange.FindNext(mycell)
    Loop While mycell.Row <> myObj.Row
     End If

End Sub

以上になります。
直してもエラーが出ての繰り返しで訳が分からなくなってしまいました。ちなみに素人です。
この状態ですとエラー13　方の不一致のエラーが出てしまいます。
場所は KeyDate = CDate(ComboBox1.Text)です。

このコードが思うように作動するにはどうすればいいか教えてください。

行動規範の内容に同意します

回答2件

ベストアンサー

コンボボックスにはどのような文字列を登録しているのでしょうか？
おそらくCDateで変換できない日付文字列になっていると思います。

投稿2020/05/22 08:22

ttyp03

総合スコア16998

Apoll047

2020/05/22 15:06

失礼しました肝心なことを書き損じておりました。以下のサイトを参考に第2.4木曜日の日付を取得してます。 https://vba-labo.rs-techdev.com/archives/170 As Dateであっております。シリアル値に統一した方が分かりやすいと思いシリアル変換にしております。ただそれを行っても Set myRangeがアプリケーション定義またはオブジェクト定義のエラーです。(1004)が出てしまいます。

ttyp03

2020/05/22 23:55

Date型で目的の日付を得ているのはわかりました。ではそれをコンボボックスにはどのような書式の文字列に変換して表示しているのですか？またここに来てエラーが出る場所がCDateではなくSet myRangeに情報が置き換わっています。どちらですか？ステップ実行させればよくわかるので確認してみてください。 Set myRangeの方は特に問題らしい問題はなさそうに見えますが。

Apoll047

2020/05/26 08:31

以下の変更を加えたところ正常に作動しました。 Dim KeyDate→Dim KeyDate　As Date KeyDate = CDate(ComboBox1.Text)→KeyDate = CDate(ComboBox1.Value) しかし以下のエラーが発生しました。 Do以下の Worksheets("2").Range(Cells(mycell.Row, 1), Cells(mycell.Row, 5)).Copy この２行についてアプリケーション定義またはオブジェクトエラーです。（＃1004）が発生しました。私の知識では .select selection.copy のような記述は使用できないとある記事で見かけましたのでそうはせず直接コピペしています。それがいけないような気がしていますが、どう改善するのがベストなのかいまいちわかりません。ご教授願いたいです。

ttyp03

2020/05/26 08:55

Cellsがどのシートを指しているか曖昧だからですね。次のようにしてください。 Worksheets("2").Range(Worksheets("2").Cells(mycell.Row, 1), Worksheets("2").Cells(mycell.Row, 5)).Copy 次行も同様に。尚、毎回Worksheets("2")と長々書くのは可読性が悪いので、ワークシートのオブジェクトを作ってそちらを使うようにするとよいですよ。 Dim sh2 As Worksheet Set sh2 = Worksheets("2") sh2.Range(sh2.Cells(mycell.Row, 1), sh2.Cells(mycell.Row, 5)).Copy

Apoll047

2020/05/26 09:24

ありがとうございます。上記のように改変できましたがしかし、オブジェクトが必要です（424）とのエラーを吐いてしまいました。試しに Distinationで貼り付け先を指定しても同じでした。そしてもう一つ最初から出ていたと思われる不本意な動作に気が付きましたので併せてお伺いしたいです。８行目 Range("A2", Cells(LastRowX, 5)).ClearContents なのですが先ほどの回答を元に Sh1.Range(sh.1Cells(2,1), sh2.Cells(LastRowX, 5)).ClearContents に変更してものすごく見やすくなりました。ありがとうございます。メンテナンスのためにも使用させていただきます。ですがなぜか１行目まで消えてしまいます。やりたいことはセルのAーE列の記入のあるセルをクリアしたいのです。

ttyp03

2020/05/27 00:10

1個目うーん、わかりません。最新のコードを貼ってもらったほうがいいかもしれません。 sh1かsh2にSetする前に使ってるとか？ 2個目 Sh1.Range(sh.1Cells(2,1), sh2.Cells(LastRowX, 5)).ClearContents sh1のRangeでsh2が登場してます。見直してみてください。

行動規範の内容に同意します