Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
On Error Resume Next
    Dim rAll As Range
    Dim lMatch As Long
    Dim lCountif As Long
    
    With Sheets("Variable")
        Set rAll = .Range("G:G")
        lCountif = Application.CountIf(rAll, CbB11.Text)
        rAll.NumberFormat = "@"
            If lCountif > 0 Then
                 If IsNumeric(CbB11.Text) Then
                      lMatch = Application.Match(CDbl(CbB11.Text), rAll, 0)
                 Else
                      lMatch = Application.Match(CbB11.Text, rAll, 0)
                 End If
                    TB12.Text = .Range("G" & lMatch).Offset(0, 1)
                    TB13.Text = .Range("G" & lMatch).Offset(0, 2)
                    TB14.Text = .Range("G" & lMatch).Offset(0, 3)
                    TB15.Text = .Range("G" & lMatch).Offset(0, 4)
                    TB16.Text = .Range("G" & lMatch).Offset(0, 6)
        Else
             MsgBox " ¡ÃسҾÔÁ¾ìàÅ¢»ÃШӵÑÇ¡è͹ËÃ×ÍàÅ¢»ÃШӵÑÇäÁèÁÕã¹°Ò¹¢éÍÁÙÅ", vbExclamation, "°Ò¹¢éÍÁÙźؤ¤Å"
             CbB11.Text = ""
        End If
    End With
End Sub

Code: Select all

'Other code
Set rAll = .Range("G:G")
rAll.NumberFormat = "@"
lCountif = Application.CountIf(rAll, CbB11.Text)
'Other code

Code: Select all

Private Sub CommandButton1_Click()
    On Error Resume Next
    Dim rAll As Range
    Dim lMatch As Long
    Dim lMatch1 As Long
    Dim lMatch2 As Long
    Dim lCountif As Long
    
    With Sheets("Variable")
        Set rAll = .Range("G:G")
        rAll.NumberFormat = "@"
        lCountif = Application.CountIf(rAll, CbB11.Text)
            If lCountif > 0 Then
                    lMatch1 = Application.Match(CbB11.Text, rAll, 0)
                    lMatch2 = Application.Match(CDbl(CbB11.Text), rAll, 0)
                    lMatch = lMatch1 + lMatch2
                    TB12.Text = .Range("G" & lMatch).Offset(0, 1)
                    TB13.Text = .Range("G" & lMatch).Offset(0, 2)
                    TB14.Text = .Range("G" & lMatch).Offset(0, 3)
                    TB15.Text = .Range("G" & lMatch).Offset(0, 4)
                    TB16.Text = .Range("G" & lMatch).Offset(0, 6)
             Else
             MsgBox " ¡ÃسҾÔÁ¾ìàÅ¢»ÃШӵÑÇ¡è͹ËÃ×ÍàÅ¢»ÃШӵÑÇäÁèÁÕã¹°Ò¹¢éÍÁÙÅ", vbExclamation, "°Ò¹¢éÍÁÙźؤ¤Å"
             CbB11.Text = ""
        End If
    End With
End Sub