☰

VBA Excel/用户窗体，搜索功能不起作用

tag-icon • tag-icon microsoft-excel vba
VBA Excel/用户窗体，搜索功能不起作用

这是我的代码：

Private Sub CmdSearch3_Click()

Dim FerNum As Variant, totRows As Long, i As Long

totRows = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
FerNum = InputBox("Please enter the fermenter number you are looking for")

If FerNum = "" Then
    MsgBox "Enter the fermenter number you wish to search."
End If

For i = 2 To totRows
    If Sheet1.Cells(i, 3).Value <> Trim(FerNum) And i = totRows Then
        MsgBox ("Sorry, the fermenter number " & FerNum & " is not found.")
    End If

    If Sheet1.Cells(i, 3).Value = Trim(FerNum) And i = totRows Then
        MsgBox ("Please enter the data need here.")

        DTPickerActualHarvestDate.SetFocus
        Exit For
    End If
Next i
Exit Sub

Dim wks As Worksheet
Dim AddNew As Range
Set wks = Sheet1

Set AddNew = wks.Range("A65356").End(xlUp).Offset(1, 0)

AddNew.Offset(i, 6).Value = DTPickerActualHarvestDate.Value
AddNew.Offset(i, 7).Value = txtpH.Text
AddNew.Offset(i, 8).Value = cboNumberofCases.Value

AddNew.Offset(i, 10).Value = cboNumberofPails2gal.Text

AddNew.Offset(i, 12).Value = cboNumberofPails5gal.Text

AddNew.Offset(i, 13).Value = txtRetailPouchWeight1.Text
AddNew.Offset(i, 14).Value = txtRetailPouchWeight2.Text
AddNew.Offset(i, 15).Value = txtRetailPouchWeight3.Text
AddNew.Offset(i, 16).Value = txt2galPailsWeight1.Text
AddNew.Offset(i, 17).Value = txt2galPailsWeight2.Text
AddNew.Offset(i, 18).Value = txt2galPailsWeight3.Text
AddNew.Offset(i, 19).Value = txt5galPailsWeight1.Text
AddNew.Offset(i, 20).Value = txt5galPailsWeight2.Text
AddNew.Offset(i, 21).Value = txt5galPailsWeight3.Text

Select Case cboTexture.Value
    Case Is = "Y"
        cboTexture.Value = "N"
    Case Is = "N"
End Select
AddNew.Offset(i, 22).Value = cboTexture.Text

Select Case cboFlavor.Value
    Case Is = "Y"
        cboFlavor.Value = "N"
    Case Is = "N"
End Select
AddNew.Offset(i, 23).Value = cboFlavor.Text

lstSearchResults.ColumnCount = 24
lstSearchResults.RowSource = "B1:AE65356"


End Sub

此代码用于第一步在电子表格中查找发酵罐编号，找到后要求用户输入数据，数据将输入到与发酵罐编号对应的电子表格中。

目前，检查输入发酵罐编号的消息框是否为 Null 的前面部分可以正常工作。但是，即使电子表格中存在发酵罐编号，系统仍会显示未找到编号的框。因此我无法测试以下代码是否有效。

虽然我认为“Next i”不应该跟在“Exit for”和“End if”之后，但这似乎没有意义。我试图改变它们的顺序，但只能在这种情况下起作用。

你能帮我解决这个问题吗？非常感谢！

答案1

我认为问题在于输入框使用字符串，而您想要的是数字。解决方法是将输入转换为数字。尝试：

Dim FerString As String
Dim FerNum as Long

FerString = InputBox("Please enter the fermenter number you are looking for")

If FerString = "" Then
    MsgBox "Enter the fermenter number you wish to search."
End If

FerNum = Val(FerSTring)

相关内容