VocExcel(単語帳) / VBA Tips
 
 [Key] エクセル / Excel / VBA /マクロ


<--- 戻る

検索画面を作る

サンプルファイル







Private Sub CommandButton6_Click()



Dim theRange As String
Dim myWord As String
Dim myData
Dim myRng


'一回目の検索ボタン (先頭から)


'===== 検索を実行
On Error Resume Next

If Trim(TextBox1.Text) <> "" Then

Cells(99, "H").Select




Set myData = Range("H99:K65536") '' 検索範囲

If CheckBox1.Value = True Then '完全一致だったら

myWord = Trim(TextBox1.Value)
Set myRng = myData.Find(myWord, LookAt:=xlWhole)

Else
myWord = "*" & Trim(TextBox1.Value) & "*"
Set myRng = myData.Find(myWord, LookAt:=xlPart)
End If








If Not myRng Is Nothing Then

Application.GoTo Cells(myRng.Row, 1), True



Range("C" & ActiveCell.Row & ":M" & ActiveCell.Row).Select


If ActiveCell.Row <= 100 Then

Cells(101, "H").Select

End If



End If


End If

CommandButton5.SetFocus


End Sub


------------------------------------------------------------------------


Private Sub CommandButton5_Click()

Dim theRange As String
Dim myWord As String
Dim myData
Dim myRng


'次候補


'===== 検索を実行

On Error Resume Next



If Trim(TextBox1.Text) <> "" Then


'一行下から検索範囲にする
theRange = "H" & ActiveCell.Offset(1, 0).Row & ":H" & Range("H65536").End(xlUp).Row



Set myData = Range(theRange)

If CheckBox1.Value = True Then '完全一致だったら

myWord = Trim(TextBox1.Value)
Set myRng = myData.Find(myWord, LookAt:=xlWhole)

Else
myWord = "*" & Trim(TextBox1.Value) & "*"
Set myRng = myData.Find(myWord, LookAt:=xlPart)
End If







If myRng <> "" Then



Application.GoTo Cells(myRng.Row, 1), True



Range("C" & ActiveCell.Row & ":M" & ActiveCell.Row).Select

If ActiveCell.Row <= 100 Then

Cells(101, "H").Select

End If


End If


End If

End Sub


--