Excel VBA でテキストボックスの値をセルA列から検索

質問

いつもお世話になります。
Private Sub CommandButton3_Click()
Dim 行 As String
Dim 列 As String
Dim 最終行 As String
Dim 検索行 As String
Dim メッセージ As Integer
Dim 一致 As Range
Dim myNO As Variant
Dim i As Long
Sheets(3).Select
最終行 = Range("A2").End(xlDown).Offset(1).Select
行 = ActiveCell.Row
列 = ActiveCell.Column
myNO = TextBox2.Value
検索行 = Range("A2").End(xlDown).Select
※・・・Set 一致 = Range("A2:検索検").Findwhat:=TextBox2,lookat:=xlWhole)
If 一致 Is Nothing Then
MsgBox "データがありません。新規コード入力します。"
Cells(行, 列 + 0) = UserForm1.TextBox2.Value
Cells(行, 列 + 1) = UserForm1.ComboBox7.Value
Else
i = Cells(行 - 1, "A")
Cells(i, 列 + 0) = UserForm1.TextBox2.Value
Cells(i, 列 + 1) = UserForm1.ComboBox7.Value
End If
End Sub

「エラー1004'Range'メソッドは失敗しました'Global'オブジェクト」とでます。※印が黄色になっています。
ユーザーフォーム1のテキストボックスの値をシート3のA列から検索して、一致すれば、A列の一致セルに上書き入力して、一致が無い場合はA列の空白セルに追加入力したいのです。よろしくお願い致します。

回答No.2

ham_kamo · ベストアンサー

えっと、いろいろ改善の余地があるマクロなので、気がついたところを指摘していきます。

Dim メッセージ As Integer

この変数は使われてないので宣言は不要かと思います。（もし質問文のVBAが抜粋で、後で使うのなら別ですが）

最終行 = Range("A2").End(xlDown).Offset(1).Select

この「最終行」に範囲をSelectした結果を代入しているのは、意味がありません。最終行には True が代入されます。最終行のセル番地を代入したいのであれば、

最終行 = Range("A2").End(xlDown).Offset(1).Addresss

とする必要がありますが、その次の２行で行と列を取得しており、そして変数「最終行」はプロシージャの最後まで使われていません。

myNO = TextBox2.Value

この変数も最後まで使われていません。Findの引数として使おうとしたのかと思いますが、Findではもう一度TextBox2と書いてしまっています。

検索行 = Range("A2").End(xlDown).Select

これも先の最終行と同じく、Selectした結果を代入しているため、Trueが代入されてしまいます。今までの指摘は、マクロの動作に影響を与えるものではありませんでしたが、ここは違います。この後で検索範囲の一番下のセルとして使おうとしても、これではその番地が代入されないのです。

その次の、

Set 一致 = Range("A2:検索検").Findwhat:=TextBox2,lookat:=xlWhole)

は書式が崩れてますがですが、多分書きたいことは

Set 一致 = Range("A2:検索行").Find(what:=TextBox2,lookat:=xlWhole)

ですよね。しかし"A2:検索行"という範囲指定はできません。「検索行」は変数なので文字列に含めてしまってはいけません。「検索行」にセル番地が入っているのであれば、Range("A2:" & 検索行) あるいは Range("A2",検索行") のようにしないと正しく領域を指定できません。

以上を踏まえて、Range型の変数を使うようにして書き直してみました。

Private Sub CommandButton3_Click()
　Dim 最終行 As Range 'Stringから変更
　Dim 検索行 As Range 'Stringから変更
　Dim 一致 As Range
　Sheets(3).Select
　Set 検索行 = Range("A2").End(xlDown)
　Set 最終行 = 検索行.Offset(1)
　Set 一致 = Range("A2", 検索行).Find(what:=TextBox2, lookat:=xlWhole)
　If 一致 Is Nothing Then
　　MsgBox "データがありません。新規コード入力します。"
　　最終行.Value = UserForm1.TextBox2.Value
　　最終行.Offset(0, 1).Value = UserForm1.ComboBox7.Value
　Else
　　一致.Value = UserForm1.TextBox2.Value '同じ値なので本来は不要
　　一致.Offset(0, 1).Value = UserForm1.ComboBox7.Value
　End If
End Sub

お礼

ありがとうございました。
エラー無しに動きました。

ご指摘のとおりです。
宣言するところが理解できていません。
Set 一致 = Range("A2:検索").Findwhat:=TextBox2,lookat:=xlWhole)
のところも選択できなくて、自分なりに改良に改良をかさねて、
訳がわからなくなっていました。
VBAは本を買ってはじめてのもののわからないところばかりで・・・。
今後ともよろしくお願いいたします。

回答No.1

ape5

オブジェクト（コンボとか）をたくさん使ってるようなので、少しにして検証しました。で、以下のようにすると一応動きます。
============================================================
Private Sub CommandButton1_Click()
'変更前　Dim 行 As String
Dim 行 As Integer
'変更前　Dim 列 As String
Dim 列 As Integer
Dim 最終行 As String
Dim 検索行 As String
Dim メッセージ As Integer
Dim 一致 As Range
Dim myNO As Variant
'変更前　Dim i As Long
Dim i As Variant

Sheets(1).Select
最終行 = Range("A2").End(xlDown).Offset(1).Select
行 = ActiveCell.Row
列 = ActiveCell.Column
myNO = TextBox1.Value
’A2から、データのあるところまでを選択するように変更しました
検索行 = Range(Cells(2, 1), Cells(行 - 1, 列)).Select
’選択しているセルを検索するように変更しました
Set 一致 = Selection.Find(what:=TextBox1.Text, lookat:=xlWhole)
If 一致 Is Nothing Then
MsgBox "データがありません。新規コード入力します。"
Else
i = Cells(行 - 1, 1).Value
End If
End Sub
============================================================

お礼

せっかくお答え頂きましたが、うまく入力はされませんでした。
入力済みのA2～最終行が選択された状態でした。
書いて頂いた、構文を参考にさせて頂きます。
ありがとうございました。