excel Instr返回意外匹配

bejyjqdl 于 2023-08-08 发布在其他

关注(0)|答案(2)|浏览(109)

我正在构建一个“编辑产品”用户表单，并使用instr从其主要分配代码中查找搜索的产品。问题：搜索返回错误的产品。假设我想要代码为“1”的产品，它给了我代码为“13”的产品（这是测试数据库中最新的13个项目），我认为它从所有结果中获取第一个索引答案，而不是直接匹配。也许我应该使用match而不是instr，因为它在单元格上总是一个整数值？

Private Sub TextBox1_Change()
    On Error Resume Next
    
    Dim Abas As Worksheet
    Dim linha As Integer
    Dim xPesq As String
    Dim xCel As String
    Dim xPlan As String
    
    Sheets("Produtos").Select
    Planilha1.Activate
    xPlan = Planilha1.Name
    xPesq = Edi_Pro.TextBox1.Text
    linha = 3
    
    Set Abas = ThisWorkbook.Worksheets(xPlan)
    With Abas
        
        While .Cells(linha, 2) <> Empty
            
            xCel = .Cells(linha, 2)
            If InStr(1, UCase(xCel), UCase(xPesq), 0) Then
                Edi_Pro.TextBox2.Value = .Cells(linha, 3)
                Edi_Pro.TextBox3.Value = .Cells(linha, 4)
                Edi_Pro.TextBox4.Value = .Cells(linha, 5)
                Edi_Pro.ComboBox1.Value = .Cells(linha, 7)
            ElseIf xCel = "" Then
                Edi_Pro.TextBox2.Value = ""
                Edi_Pro.TextBox3.Value = ""
                Edi_Pro.TextBox4.Value = ""
                Edi_Pro.ComboBox1.Value = ""
            End If
            linha = linha + 1
        Wend
        
    End With
    
End Sub

字符串

excel

来源：https://stackoverflow.com/questions/76805479/instr-returning-unexpected-match

2条答案

按热度按时间

iibxawm41#

尝试了这个代码，它工作了。黑猫是正确的方式，原来的代码循环设置它会给予我总是最后的结果，循环，所以我尝试了另一种方法，这工作。

Private Sub TextBox1_Change()
   On Error Resume Next
   Dim intervalo As Range
   Dim texto As String
   Dim codigo As Integer
   Dim pesquisa

   codigo = TextBox1.Text

   Sheets("Produtos").Select
   Set intervalo = Range("B3:G3000")

   pesquisa = Application.WorksheetFunction.VLookup(codigo, intervalo, 2, False)
   TextBox2 = pesquisa
   pesquisa = Application.WorksheetFunction.VLookup(codigo, intervalo, 3, False)
   TextBox3 = pesquisa
   pesquisa = Application.WorksheetFunction.VLookup(codigo, intervalo, 4, False)
   TextBox4 = pesquisa
   pesquisa = Application.WorksheetFunction.VLookup(codigo, intervalo, 6, False)
   ComboBox1 = pesquisa
   TextBox1.SetFocus
   TextBox1.SetFocus
    If TextBox2.Text = "" Then
       MsgBox "Não foi localizado nenhum valor correspondente ao Código", vbInformation, "Lançamento de Estoque"
   End If
End Sub

字符串

赞(0）回复(0）举报 2023-08-08

vuktfyat2#

也可以使用Match()
如果你不使用WorksheetFunction，就不需要On Error Resume Next--你可以测试返回值。

Private Sub TextBox1_Change()
   
    Dim wsProd As Worksheet, rngMatch As Range
    Dim codigo As Long, m, tb2, tb3, tb4, cb1
    
    codigo = CLng(TextBox1.Text)
    
    Set wsProd = ThisWorkbook.Worksheets("Produtos")
    Set rngMatch = wsProd.Range("B3:B" & wsProd.Cells(Rows.Count, "B").End(xlUp).row)
    m = Application.Match(codigo, rngMatch, 0) 'try to find an exact match
    If Not IsError(m) Then                     'got a match?
        With rngMatch.Cells(m).EntireRow       'the whole row for the matched cell
            tb2 = .Columns("C").Value          'read the results...
            tb3 = .Columns("D").Value
            tb4 = .Columns("E").Value
            cb1 = .Columns("G").Value
        End With
    Else
        MsgBox "Não foi localizado nenhum valor correspondente ao Código", vbInformation, "Lançamento de Estoque"
    End If
    
    TextBox2 = tb2     'show results (or empty values)
    TextBox3 = tb3
    TextBox4 = tb4
    ComboBox1 = cb1
    
    TextBox1.SetFocus
    
End Sub

字符串

赞(0）回复(0）举报 2023-08-08

我来回答

excel Instr返回意外匹配

2条答案

相关问题

热门标签

最新问答