Reputation: 85
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
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