Snasui Excel Forum

Code: Select all

Option Explicit
Private Sub ReadInput_Click()
Call RdINP("problem 1.xlsx")
End Sub

Code: Select all

Option Explicit
Dim NumCate As Integer 
Dim NumN() As Integer
Dim NumCapa() As Integer 
Dim NumFixedCost() As Integer 
Dim NumVarCost() As Double
Dim NumLCost() As Double
Dim NumSpeed() As Double 
Dim NumCust() As Integer 
Dim NumX() As Integer 
Dim NumY() As Integer 
Dim NumNeed() As Integer 
Dim NumTimeStart() As Integer 
Dim NumTimeFinis() As Integer 
Dim NumTimeTra() As Integer 
Dim NumFine() As Integer 
Dim i As Integer
Dim j As Integer
Public Sub RdINP(Fn As String)
Dim FolderName As String
FolderName = "C:\VRP\" & Fn
Dim wbk As Workbook
Set wbk = Workbooks.Open(FolderName)

NumCate = wbk.Worksheets("Sheet1").Range("B1")
    ReDim NumN(1 To NumCate)
    ReDim NumCapa(1 To NumCate)
    ReDim NumFixedCost(1 To NumCate)
    ReDim NumVarCost(1 To NumCate)
    ReDim NumLCost(1 To NumCate)
    ReDim NumSpeed(1 To NumCate)
        For i = 1 To NumCate
            NumN(i) = wbk.Worksheets("Sheet1").Range("B6").Offset(i, 0)
            NumCapa(i) = wbk.Worksheets("Sheet1").Range("C6").Offset(i, 0)
            NumFixedCost(i) = wbk.Worksheets("Sheet1").Range("D6").Offset(i, 0)
            NumVarCost(i) = wbk.Worksheets("Sheet1").Range("E6").Offset(i, 0)
            NumLCost(i) = wbk.Worksheets("Sheet1").Range("F6").Offset(i, 0)
            NumSpeed(i) = wbk.Worksheets("Sheet1").Range("G6").Offset(i, 0)
        Next i
NumCust = wbk.Worksheets("Sheet1").Range("B2")
    ReDim NumX(1 To NumCust)
    ReDim NumY(1 To NumCust)
    ReDim NumNeed(1 To NumCust)
    ReDim NumTimeStart(1 To NumCust)
    ReDim NumTimeFinis(1 To NumCust)
    ReDim NumTimeTra(1 To NumCust)
    ReDim NumFine(1 To NumCust)
        For i = 1 To NumCust
            NumX(i) = wbk.Worksheets("Sheet1").Range("J6").Offset(i, 0)
            NumY(i) = wbk.Worksheets("Sheet1").Range("K6").Offset(i, 0)
            NumNeed(i) = wbk.Worksheets("Sheet1").Range("L6").Offset(i, 0)
            NumTimeStart(i) = wbk.Worksheets("Sheet1").Range("M6").Offset(i, 0)
            NumTimeFinis(i) = wbk.Worksheets("Sheet1").Range("N6").Offset(i, 0)
            NumTimeTra(i) = wbk.Worksheets("Sheet1").Range("O6").Offset(i, 0)
            NumFine(i) = wbk.Worksheets("Sheet1").Range("P6").Offset(i, 0)
        Next i
MsgBox "Done"

End Sub