代码之家 › 专栏 › 技术社区 › Yan LimaBenua

使用VBA中的名称的索引匹配函数出错

excel-formula excel-vba vba excel

0

Yan LimaBenua · 技术社区 · 8 年前

请帮助处理下面的代码。我在粗体代码行中发现一个错误:

更新所有代码:

Private Sub CommandButton2_Click() 'save
    Dim sname As String
    Dim yrow As Long, ws As Worksheet
    sname = ComboBox1.Value
    Set ws = Sheets(sname)
    yrow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
If (ComboBox1.Value = "DP" Or ComboBox1.Value = "WD") Then
    If (ComboBox7 = "" Or TextBox1 = "") Then
        MsgBox ("Masih ada kolom yg belum di isi")
        Exit Sub
    Else
        ws.Range("A" & yrow).Value = "=ROW()-1"
        ws.Range("B" & yrow).Value = Sheets("TRX").Range("B2")
        ws.Range("C" & yrow).Value = ComboBox7
        ws.Range("D" & yrow).Value = TextBox1.Value
    End If
End If
If (ComboBox1.Value = "BELI") Then
    If (ComboBox3 = "" Or ComboBox5 = "" Or ComboBox6 = "") Then
        MsgBox ("Masih ada kolom yg belum di isi")
        Exit Sub
    Else
        ws.Range("A" & yrow).Value = "=ROW()-1"
        ws.Range("B" & yrow).Value = Sheets("TRX").Range("B2")
        ws.Range("C" & yrow).Value = ComboBox3
        **ws.Range("D" & yrow).Value = Application.WorksheetFunction.Index(harga, Application.WorksheetFunction.Match(ComboBox3, koin, 0)).Value**
    End If
End If

End Sub

感谢您的回复!

1 回复 | 直到 8 年前

1

0

Vityata 8 年前

一步一步地运行这段代码 F8 ,在新的Excel文件上:

Option Explicit

Public Sub TestMe()

    Dim ws As Worksheet: Set ws = Worksheets(1)
    Dim harga As Range: Set harga = ws.Range("A1:C10")
    Dim comboBox3 As String
    Dim koin As Range: Set koin = ws.Range("D1:D10")

    comboBox3 = "Something"
    Range("D3") = "Something"
    Range("A3") = "Something"

    With WorksheetFunction
        Debug.Print .Match(comboBox3, koin, 0)
        ws.Range("D5") = .Index(harga, .Match(comboBox3, koin, 0), 1)
    End With

End Sub

它显示了 Match() 和 Index() 使用。在所讨论的代码中,可能没有 Match 从而导致错误。如果您希望返回错误,而不是“抛出”,则尝试 Application.Match() 和 Application.Index() :

Public Sub TestMe()

    Dim ws As Worksheet: Set ws = Worksheets(1)
    Dim harga As Range: Set harga = ws.Range("A1:C10")
    Dim comboBox3 As String
    Dim koin As Range: Set koin = ws.Range("D1:D10")

'    comboBox3 = "Something"
'    Range("D3") = "Something"
'    Range("A3") = "Something"

    With Application
        Debug.Print .Match(comboBox3, koin, 0)
        ws.Range("D5") = .Index(harga, .Match(comboBox3, koin, 0), 1)
    End With

End Sub