Results 1 to 10 of 13

Thread: Error when running VLookup

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. 02-24-2014, 11:38 AM #1
    mcbrocks
    mcbrocks is offline
    Junior Member
    Join Date
    Feb 2014
    Posts
    8
    Rep Power
    0

    Error when running VLookup

    I've written a program which looks up a value which the user inputs together with the different option the user choose, I have to lookup this input value at different columns worksheets and return the values accordingly. Everything works fine until I add in the VLookup line. I get a RunTime Error 9 Subscript Out of RangeI've tried using a For Next loop instead of the VLookup but it gave an error too. Hopefully I've explained myself clearly.

    Here's the code:

    Code:
    Private Sub UserForm_Initialize()
    SingleACText.Text = 230
    ThreeACText.Text = 400
    CaSCText.Text = "Table"
    CaOLText.Text = "Table"
    CgText.Text = "Table"
    CiText.Text = 0.5
 
    
'    Call SetBackColor(DataTable.TabIndex(0), vbLightOrange)
'    Call SetBackColor(DataTable.TabIndex(1), vbSkyBlue)
'    Call SetBackColor(DataTable.TabIndex(2), vbYellow)
'    Call SetBackColor(DataTable.TabIndex(3), vbGold)
    
    DataTable.ViewOnlyMode = True
    DataTable.Cells.Clear
    Worksheets("Ca").Range("A1:D19").Copy
    DataTable.Range("A1:D19").Paste
    DataTable.Selection.EntireColumn.AutoFit
    DataTable.Range("B7").Select
    DataTable.ActiveWindow.FreezePanes = True
    DataTable.Range("A1").Select
End Sub
Private Sub Tables_Change()
    Dim n As Integer
    DataTable.ViewOnlyMode = True
    n = Tables.SelectedItem.Index
'    Call SetBackColor(MultiPage1.Pages(0), vbYellow) 'Yellow.
'    Call SetBackColor(MultiPage1.Pages(1), RGB(255, 0, 0)) 'Red.
'    Call SetBackColor(MultiPage2.Pages(0), vbGreen) 'Green.
'    Call SetBackColor(MultiPage2.Pages(1), vbMagenta) 'Purple.
    Select Case n
        Case 0
            DataTable.Cells.Clear
            Worksheets("Ca").Range("A1:D19").Copy
            DataTable.Range("A1:D19").Paste
            DataTable.Selection.EntireColumn.AutoFit
            DataTable.Range("B7").Select
            DataTable.ActiveWindow.FreezePanes = True
            DataTable.Range("A1").Select
    
        Case 1
            DataTable.Cells.Clear
            Worksheets("Ca").Range("E1:H19").Copy
            DataTable.Range("A1:D19").Paste
            DataTable.Selection.EntireColumn.AutoFit
            DataTable.Range("B7").Select
            DataTable.ActiveWindow.FreezePanes = True
            DataTable.Range("A1").Select
            
        Case 2
            DataTable.Cells.Clear
            Worksheets("Cg").Range("A1:I24").Copy
            DataTable.Range("A1:I24").Paste
            DataTable.Selection.EntireColumn.AutoFit
            DataTable.Range("B7").Select
            DataTable.ActiveWindow.FreezePanes = True
            DataTable.Range("A1").Select

        Case 3
            DataTable.Cells.Clear
            Worksheets("ISO-AWG").Range("A1:C36").Copy
            DataTable.Range("A1:C36").Paste
            DataTable.Selection.EntireColumn.AutoFit
            DataTable.Range("6:6").Select
            DataTable.ActiveWindow.FreezePanes = True
            DataTable.Range("A1").Select

    End Select
End Sub
Private Sub ReturnCableLengthText_Change()
    ReturnCableLengthSpin.Value = Val(ReturnCableLengthText.Text)
End Sub
Private Sub ReturnCableLengthSpin_Change()
    ReturnCableLengthText.Text = ReturnCableLengthSpin.Value
End Sub
Private Sub LoadCurrentText_Change()
    LoadCurrentSpin.Value = Val(LoadCurrentText.Text)
End Sub
Private Sub LoadCurrentSpin_Change()
    LoadCurrentText.Text = LoadCurrentSpin.Value
End Sub
Private Sub OverloadCurrentText_Change()
    OverloadCurrentSpin.Value = Val(OverloadCurrentText.Text)
End Sub
Private Sub OverloadCurrentSpin_Change()
    OverloadCurrentText.Text = OverloadCurrentSpin.Value
End Sub
Private Sub CgText_Change()
    CgSpin.Value = Val(CgText.Text) * 100
End Sub
Private Sub CgSpin_Change()
    CgText.Text = CgSpin.Value * 0.01
End Sub
Private Sub CiText_Change()
    CiSpin.Value = Val(CiText.Text) * 100
End Sub
Private Sub CiSpin_Change()
    CiText.Text = CiSpin.Value * 0.01
End Sub


Private Sub Armoured_Change()
    Select Case Armoured.Value
        Case True: SingleCore.Enabled = False
        Case False: SingleCore.Enabled = True
    End Select
End Sub
Private Sub NonMagneticArmour_Change()
    Select Case NonMagneticArmour.Value
        Case True: MultiCore.Enabled = False
        Case False: MultiCore.Enabled = True
    End Select
End Sub
'Private Sub SingleCore_Change()
'    Select Case SingleCore.Value
'        Case True: Armoured.Enabled = False
'        Case False: Armoured.Enabled = True
'    End Select
'End Sub
'Private Sub MultiCore_Change()
'    Select Case MultiCore.Value
'        Case True: NonMagneticArmour.Enabled = False
'        Case False: NonMagneticArmour.Enabled = True
'    End Select
'End Sub
Private Sub TwoCoresCables_Change()
    Select Case TwoCoresCables.Value
        Case True: ThreeAC.Enabled = False
        Case False: ThreeAC.Enabled = True
    End Select
End Sub
Private Sub ThreeFourCoresCables_Change()
    Select Case ThreeFourCoresCables.Value
        Case True: DC.Enabled = False
                   SingleAC.Enabled = False
        Case False: DC.Enabled = True
                    SingleAC.Enabled = True
    End Select
End Sub
'Private Sub DC_Change()
'    Select Case DC.Value
'        Case True: OneTrefoil.Enabled = False
'                   ElevenTrefoil.Enabled = False
'                   TwelveTrefoil.Enabled = False
'        Case False: OneTrefoil.Enabled = True
'                    ElevenTrefoil.Enabled = True
'                    TwelveTrefoil.Enabled = True
'    End Select
'End Sub
'Private Sub DC_Change()
'    Select Case DC.Value
'        Case True: ThreeFourCoresCables.Enabled = False
'        Case False: ThreeFourCoresCables.Enabled = True
'    End Select
'End Sub
'Private Sub SingleAC_Change()
'    Select Case SingleAC.Value
'        Case True: ThreeFourCoresCables.Enabled = False
'        Case False: ThreeFourCoresCables.Enabled = True
'    End Select
'End Sub
'Private Sub ThreeAC_Change()
'    Select Case ThreeAC.Value
'        Case True: TwoCoresCables.Enabled = Fasle
'        Case False: Two CoresCables.Enabled = True
'    End Select
'End Sub
Private Sub Method1_Change()
    Select Case Method1.Value
        Case True: Select Case ThreeAC.Value
                       Case True: OneTrefoil.Enabled = True
                       Case False: OneTrefoil.Enabled = False
                   End Select
        Case False: OneTrefoil.Enabled = False
    End Select
End Sub
Private Sub Method11_Change()
    Select Case Method11.Value
        Case True: Select Case ThreeAC.Value
                       Case True: ElevenTrefoil.Enabled = True
                       Case False: ElevenTrefoil.Enabled = False
                   End Select
        Case False: ElevenTrefoil.Enabled = False
    End Select
End Sub
Private Sub Method12_Change()
    Select Case Method12.Value
        Case True: Select Case ThreeAC.Value
                       Case True: TwelveTrefoil.Enabled = True
                       Case False: TwelveTrefoil.Enabled = False
                    End Select
                   Horizontal.Enabled = True
                   Vertical.Enabled = True
        Case False: TwelveTrefoil.Enabled = False
                    Horizontal.Enabled = False
                    Vertical.Enabled = False
    End Select
End Sub
Private Sub TwelveTrefoil_Change()
    Select Case TwelveTrefoil.Value
        Case True: Horizontal.Enabled = False
                   Vertical.Enabled = False
        Case False: Horizontal.Enabled = True
                    Vertical.Enabled = True
    End Select
End Sub
Private Sub Horizontal_Change()
    Select Case Horizontal.Value
        Case True: TwelveTrefoil.Enabled = False
        Case False: TwelveTrefoil.Enabled = True
    End Select
End Sub
Private Sub Vertical_Change()
    Select Case Vertical.Value
        Case True: TwelveTrefoil.Enabled = False
        Case False: TwelveTrefoil.Enabled = True
    End Select
End Sub


Private Sub OKButton_Click()
    Dim Msg As String
    Dim Ct As Single
'    Dim WorksheetName As String
'    Dim WorksheetNumber As Integer

    
    'Display Messages
    If Aluminium Then Msg = Msg & Aluminium.Caption
    If Copper Then Msg = Msg & Copper.Caption

    If PVC Then Msg = Msg & " " & PVC.Caption
    If PVC Then WorksheetName = "DataPVC"
    'If Rubber Then Msg = Msg & vbNewLine & Rubber.Caption
    If Thermosetting Then Msg = Msg & " " & Thermosetting.Caption
    If Thermosetting Then WorksheetName = "DataThermo"

    If Armoured Then Msg = Msg & vbNewLine & Armoured.Caption
    If Armoured Then WorksheetNumber = "3"
    If NonArmoured Then Msg = Msg & vbNewLine & NonArmoured.Caption
    If NonArmoured Then WorksheetNumber = "1"
    If NonMagneticArmour Then Msg = Msg & vbNewLine & NonMagneticArmour.Caption
    If NonMagneticArmour Then WorksheetNumber = "3"
    
    If Method1 Then Msg = Msg & " " & Method1.Caption
    If Method3 Then Msg = Msg & " " & Method3.Caption
    If Method4 Then Msg = Msg & " " & Method4.Caption
    If Method11 Then Msg = Msg & " " & Method11.Caption
    If Method12 Then Msg = Msg & " " & Method12.Caption
    If Method13 Then Msg = Msg & " " & Method13.Caption
    If OneTrefoil Then Msg = Msg & " " & OneTrefoil.Caption
    If ElevenTrefoil Then Msg = Msg & " " & ElevenTrefoil.Caption
    If TwelveTrefoil Then Msg = Msg & " " & TwelveTrefoil.Caption
    If Horizontal Then Msg = Msg & " " & Horizontal.Caption
    If Vertical Then Msg = Msg & " " & Vertical.Caption
    
    If MultiCore Then WorksheetNumber = WorksheetNumber + 1
    If SingleCore Then WorksheetNumber = WorksheetNumber + 0

    WorksheetName = WorksheetName & WorksheetNumber

    If TwoCoresCables Then
        Select Case MultiCore.Value
            Case True: Msg = Msg & vbNewLine & "2 Core - " & MultiCore.Caption & " Cables"
            Case False: Select Case SingleCore.Value
                                Case True: Msg = Msg & vbNewLine & "2 " & SingleCore.Caption & " Cables"
                                Case False: Msg = Msg
                        End Select
        End Select
    End If
    If ThreeFourCoresCables Then
        Select Case MultiCore.Value
            Case True: Msg = Msg & vbNewLine & "3/4 Core - " & MultiCore.Caption & " Cables"
            Case False: Select Case SingleCore.Value
                                Case True: Msg = Msg & vbNewLine & "3/4 " & SingleCore.Caption & " Cables"
                                Case False: Msg = Msg
                        End Select
        End Select
    End If
    
    If DC Then Msg = Msg & vbNewLine & DC.Caption & " " & DCText.Text & "V"
    If SingleAC Then Msg = Msg & vbNewLine & SingleAC.Caption & " " & SingleACText.Text & "V"
    If ThreeAC Then Msg = Msg & vbNewLine & ThreeAC.Caption & " " & ThreeACText.Text & "V"
    
    Msg = Msg & vbNewLine
    
    If ReturnCableLengthText.Text = Empty Then ReturnCableLengthText.Text = "0"
    Msg = Msg & vbNewLine & "Return Cable Length: " & ReturnCableLengthText.Text & " m"
    If LoadCurrentText.Text = Empty Then LoadCurrentText.Text = "0"
    Msg = Msg & vbNewLine & "Load Current:             " & LoadCurrentText.Text & " A"
    If OverloadCurrentText.Text = Empty Then OverloadCurrentText.Text = "0"
    Msg = Msg & vbNewLine & "Overload Current:      " & OverloadCurrentText.Text & " A"
    
    Msg = Msg & vbNewLine
    
    If CaSCText.Text = Empty Then CaSCText.Text = "0"
    Msg = Msg & vbNewLine & "CaSC: " & CaSCText.Text
    If CaOLText.Text = Empty Then CaOLText.Text = "0"
    Msg = Msg & vbNewLine & "CaOL: " & CaOLText.Text
    If CgText.Text = Empty Then CgText.Text = "0"
    Msg = Msg & vbNewLine & "Cg:     " & CgText.Text
    'CiText.Text preset to 0.5
    Msg = Msg & vbNewLine & "Ci:       " & CiText.Text
    If MCB Then Ct = 1
    If SemiEnclosedFuse Then Ct = 0.725
    Msg = Msg & vbNewLine & "Ct:       " & Ct
    
    
    'Reference Data Calculation
'    Dim Row As Integer
    Dim Column As Integer
    Dim Multiplier As Integer
    Dim i As Integer
    Dim CurrentCarryingCapacity As Variant
    Dim VoltageDrop As Integer
    
    If TwoCoresCables Then Column = 2
    If ThreeFourCoresCables Then Column = 38
    
    If DC Then Column = Column + 0
    If DC Then Multiplier = 1
    If SingleAC Then Column = Column + 8
    If SingleAC Then Multiplier = 4
    If ThreeAC Then Column = Column + 0
    If ThreeAC Then Multiplier = 4
    
    If Method1 Then Column = Column + Multiplier * 0
    If Method3 Then Column = Column + Multiplier * 1
    If Method4 Then Column = Column + Multiplier * 2
    If Method11 Then Column = Column + Multiplier * 3
    If Method12Horizontal Then Column = Column + Multiplier * 4
    If Method12Vertical Then Column = Column + Multiplier * 5
    If Method13 Then Column = Column + Multiplier * 6
    If OneTrefoil Then Column = Column + Multiplier * 7
    If ElevenTrefoil Then Column = Column + Multiplier * 8
    If TwelveTrefoil Then Column = Column + Multiplier * 9
    
    If DC Then VoltageDrop = 9
    If DC Then ReturnColumn = VoltageDrop - Column + 1
    If SingleAC Then VoltageDrop = Column + 3
    If ThreeAC Then VoltageDrop = Column + 3
    
    Msg = Msg & vbNewLine & VoltageDrop
    
    If Aluminium Then
            CurrentCarryingCapacity = WorksheetFunction.VLookup(LoadCurrentText.Text, Worksheets("WorksheetName").Range(Cells(10, Column), Cells(26, VoltageDrop)), ReturnColumn, False)
    End If
    If Copper Then
            CurrentCarryingCapacity = WorksheetFunction.VLookup(LoadCurrentText.Text, Worksheets("WorksheetName").Range(Cells(28, Column), Cells(49, VoltageDrop)), ReturnColumn)
    End If


    Msg = Msg & vbNewLine & Column
    Msg = Msg & vbNewLine & i
    Msg = Msg & vbNewLine & VoltageDrop
    Msg = Msg & vbNewLine & WorksheetName
    Msg = Msg & vbNewLine & CurrentCarryingCapacity
 
    Display9.Text = Msg
    Display9.MultiLine = True
End Sub
Private Sub CancelButton_Click()
    Unload CableSizingSelection
End Sub
    Last edited by mcbrocks; 02-24-2014 at 12:01 PM.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Macro Running Very Slow When Applying Data Validation In Cells
    By dhivya.enjoy in forum Excel Help
    Replies: 1
    Last Post: 10-30-2013, 10:27 PM
  2. Macro stops running if date is not in past...
    By Carlos Arruda in forum Excel Help
    Replies: 4
    Last Post: 03-10-2013, 04:33 PM
  3. Saving and Running Macro For Multiple Files / Users
    By Charles_ in forum Excel Help
    Replies: 1
    Last Post: 01-07-2013, 09:10 AM
  4. Workbook Event running VBA question
    By jamilm in forum Excel Help
    Replies: 4
    Last Post: 12-29-2012, 12:12 AM
  5. Running a VBA in all excel files
    By msiyab in forum Excel Help
    Replies: 3
    Last Post: 12-26-2012, 01:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules