CODEWITHSUNDEEP
vbaexcelexcel-formula
Yan LimaBenua
Yan LimaBenua

Reputation: 85

Error with the index match function using the name in VBA

Please, help with the code below I found an error in the bold code line:

Update code for all :

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

Thank you for your response!

Upvotes: 0

Views: 54

Answers (1)

Vityata
Vityata

Reputation: 43595

Run this piece of code step-by-step with F8, on a new Excel file:

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

It shows how Match() and Index() are used. In the code in question, there is probably nothing to Match, thus it results an error. If you want the errors to be returned, and not "thrown", then try Application.Match() and 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

Upvotes: 1

Related Questions