need help finde how I break my vba code
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
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