need help finde how I break my vba code

need help finde how I break my vba code

Darkforce_the_ilogic_guy
Advisor Advisor
332 Views
3 Replies
Message 1 of 4

need help finde how I break my vba code

Darkforce_the_ilogic_guy
Advisor
Advisor

I have this code . and get this error ,  I do not understand it because I have the it to work .. or at less almost the same code working .. I made an change ... and it stop working . sind I didn´t seens to find the problem .. I did my best to roll the code make to what I believe the code was before ... but I get this error and I do not understand why... can you help

 

bt_0-1637558122089.png

 

 

 

 

 

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 1
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

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

0 Likes
333 Views
3 Replies
Replies (3)
Message 2 of 4

Michael.Navara
Advisor
Advisor

I don't understand what "Application" is in your function "getcellvalue". For example here: 

 

colNo = Application.WorksheetFunction...

 

0 Likes
Message 3 of 4

Darkforce_the_ilogic_guy
Advisor
Advisor

what I am trying to do is the find other value that are in den same row in an excel. 

 

I am trying to get price form an excel sheet. So the uses have up to date price on material. So it is easy to choss the cheapest that can get the job done

 

 

 

0 Likes
Message 4 of 4

Michael.Navara
Advisor
Advisor

Try to use application instance form Worksheet instead of new use of entry point Application. 

Function GetCellValue(oWS As Worksheet, ct As String, rt As String) As String
    'Use application instance form Worksheet
    'instead of new use of entry point Application
    Dim xlApp As Excel.Application
    Set xlApp = oWS.Application
   
    Dim colNo As Long
'    colNo = Application.WorksheetFunction.Match("NO_", oWS.Rows(1), 0)
    colNo = xlApp.WorksheetFunction.Match("NO_", oWS.Rows(1), 0)
    
    Dim rowNo As Long
'    rowNo = Application.WorksheetFunction.Match(rt, oWS.Columns(colNo), 0)
    rowNo = xlApp.WorksheetFunction.Match(rt, oWS.Columns(colNo), 0)
    
'    colNo = Application.WorksheetFunction.Match(ct, oWS.Rows(1), 0)
    colNo = xlApp.WorksheetFunction.Match(ct, oWS.Rows(1), 0)
    
    GetCellValue = oWS.Cells(rowNo, colNo)

End Function