why does it not work ... get price form Excel

[Darkforce_the_ilogic_guy]

I got this code .. it seens to work fint ... without this this linie of code 

RawMatrNo = getcellvalue(oWS, "SizeandPrice", RawMaterial)

see complete code below

and I get this error

Private Sub CommandButton1_Click()
Dim familyName(18, 1) As String
Dim familyName2(18, 1) As String
Dim RawMaterial As String

familyName(1, 0) = "3095006016"
familyName(2, 0) = "3095006020"
familyName(3, 0) = "3095006025"
familyName(4, 0) = "3095006030"
familyName(5, 0) = "3095006035"
familyName(6, 0) = "3095006040"
familyName(7, 0) = "3095006050"
familyName(8, 0) = "3095006060"
familyName(9, 0) = "3095006070"
familyName(10, 0) = "3095006075"
familyName(11, 0) = "3095006080"
familyName(12, 0) = "3095006090"
familyName(13, 0) = "3096006012"
familyName(14, 0) = "3096006025"
familyName(15, 0) = "3096006040"
familyName(16, 0) = "3096006050"
familyName(17, 0) = "3096006060"
familyName(18, 0) = "3096006070"

Dim oExcelApp
Set oExcelApp = CreateObject("Excel.Application")
oExcelApp.Visible = False
oExcelApp.DisplayAlerts = False

Dim ExcelPath As String
ExcelPath = "C:\Working Folder\CAD\Kallesoe\Kallesoe iLogic\Underdevelopment\Priclist\ShowPrices.xlsm"

Dim ExcelSheet As String
ExcelSheet = "Price"

Dim oWB
Set oWB = oExcelApp.Workbooks.Open(ExcelPath)

Dim oWS
For Each oWS In oWB.Sheets
If oWS.Name = ExcelSheet Then Exit For
Next

For i = 1 To 18
Dim RawMatrNo As String
RawMaterial = familyName(i, 0)

RawMatrNo = getcellvalue(oWS, "SizeandPrice", RawMaterial)
familyName2(i, 0) = RawMatrNo

Next
ShowPrices.ListBox1.List = familyName2

oWB.Close (False)
oExcelApp.Quit

End Sub

[A.Acheson]

It looks like you are missing the custom function as per error message which is retrieving the values from the excel sheet. You are calling the function with arguments of worksheet object, column header and cell value of raw material. Do you have the function for testing? 

Custom Function Call: 

RawMatrNo = getcellvalue(oWS, "SizeandPrice", RawMaterial)

[Ralf_Krieg · Accepted solution]

Hello

You forgot to copy the function from the old thread.

EDIT: Link to the old thread

Function getcellvalue(oWS As WorkSheet, ct As String, rt As String) As String
    
    Dim colNo As Long
    colNo = Application.WorksheetFunction.Match("NO_", oWS.Rows(1), 0)
    
    Dim rowNo As Long
    rowNo = Application.WorksheetFunction.Match(rt, oWS.Columns(colNo), 0)
    
    colNo = Application.WorksheetFunction.Match(ct, oWS.Rows(1), 0)
    
    getcellvalue = oWS.Cells(rowNo, colNo)
    
End Function