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
Solved! Go to Solution.
Solved by Ralf_Krieg. Go to Solution.
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)
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
Can't find what you're looking for? Ask the community or share your knowledge.