我使用用户形式(boaterchecklist(将数据输入到Excel表中,我希望在C列中添加一个功能以搜索唯一的ID(plasenumber(,以返回匹配ID以更新和编辑该数据。我很难将发现的行加载回用户表格。我相信我的代码是在C列中找到正确的ID单元格,但是它并没有使其活跃。在工作表上选择的任何单元格是将哪些行数据加载到用户形式的,而不是找到的单元格中。
我不确定在哪里实现" ActiveCell.Value"以返回发现ID的正确行值。任何帮助将不胜感激!
Private Sub SearchForm_Click()
Dim str as String
Dim rgFound as Range
if Permitnumber.text = "" then
msg box "enter a permit number"
exit sub
end if
with worksheets("sheet1")
str = PermitNumber.Value
Set rgFound = .Range("c2:c3000").Find(what:=str)
if rgFound is Nothing then
msgbox "permit not found"
else
DateBox.Text = Cells(ActiveCell.row, 1)
TimeBox.Text = Format(cells(ActiveCell.Row, 2), "hh:mm:ss")
PermitNumber.Text = Cells (ActiveCell.Row, 3)
VesselName.Text = Cells (ActiveCell.Row, 4)
'ect...
end if
end with
end sub
谢谢!
所建议的,使用found
范围作为参考
Option Explicit
Private Sub SearchForm_Click()
Dim str As String, found As Range
If PermitNumber.Text = vbNullString Then
MsgBox "Enter a permit number"
Exit Sub
End If
With Worksheets("Sheet1")
str = PermitNumber.Value
Set found = .UsedRange.Find(What:=str)
If found Is Nothing Then
MsgBox "Permit not found"
Else
DateBox.Text = .Cells(found.Row, 1).Value
TimeBox.Text = Format(.Cells(found.Row, 2), "hh:mm:ss")
PermitNumber.Text = .Cells(found.Row, 3)
VesselName.Text = .Cells(found.Row, 4)
'ect...
End If
End With
End Sub
。
要在搜索时更精确的控件,请指定不应使用默认值的参数
range.find - 方法参数
Required Parameter -------------------------------------------------------------------- What:= - Value to search for (string or any Excel data type) - If What:="" is used, it will return Nothing (no cell found) - If What:="*" is used, it will return first non empty cell
Optional Parameters ------------------------------------------------------------------- After:= - Starts search after (single) cell specified: After:=Cells(1) - It excludes the specified cell, so it starts on Cells(2) - It defaults to upper-left corner of the searched Range - To move to the next found value after the first use: - After:=Cells(1).Offset(1) along with - SearchOrder:=xlByRows or xlByColumns LookIn:= - Formulas, Values, Comments. Defaults to Formulas LookAt:= - xlWhole or xlPart. If not specified it defaults to xlPart SearchOrder:= - xlByRows or xlByColumns. Defaults to xlByRows SearchDirection:= - xlNext or xlPrevious. Defaults to xlNext MatchCase:= - True (search is case sensitive). Default to False MatchByte:= - Used only for double-byte language support - True to have double-byte chars match only double-byte chars - False to have double-byte chars match their single-byte equiv SearchFormat:= - Searches Font and/or cell formatting. Defaults to False * LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method ** Not specifying these parameters, it will use the previous search settings