Referencing Variables in a Different Sub Statement. Subscript Out of Range

user_11724743 0
Viewed 67

First time programmer here, started teaching myself VBA a few days ago to write this. The goal is to have the code be able to reference two workbooks that are not constants. One is selected by the user and the other is running the macro. I have defined the workbooks in a sub statement previous, but when I try to reference it in a sub statement further down the line I get error '9' "subscript out of range." I have tried using call but it also came up with undefined errors (it could be that I don't understand the 'call' statement).

If you have an additional moment to look over my formula and make sure it is formatted correctly that would be a big help as well. I just know it is going to be a huge problem when I get there.

P.S. I just noticed that I have been spelling reference wrong in my code this entire time. Go ahead, laugh.

'''
Sub Openfile()

    Dim FileToOpen As Variant, wbRefrence As Workbook
    Dim wbOracle As Workbook
    Set wbOracle = ThisWorkbook

FileToOpen = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", Title:="Open Database File")
    If FileToOpen = False Then
    MsgBox "No file selected, cannot continue." 'If the user does not open a file this message is displayed
    Exit Sub 'If no file is selected the program stops running
    End If

    Set wbRefrence = Workbooks.Open(FileToOpen)

    Workbooks.Open (FileToOpen) 'If a file is selected it opens that file.

Call LoopTest1

End Sub


Sub LoopTest1()

Dim BlankCell As Boolean
Dim i As Long
    'Loop until a blank cell is encountered
    Do While BlankCell = False
    i = i + 1

    If Cells(i, "C").Value = "" Then
    BlankCell = True 'When it reaches a blank cell BlankCell will now be true which ends the do while formula.
    End If

    Application.Workbooks("wbOracle").Sheets("Cancel Requisition Lines").Range("C16").Select

'Formula for "do while" condition
Selection.Formula = "=IF(INDEX(['wbRefrence']Sheet1!'A2000:M2000',MATCH(1,(['wbRefrence']Sheet1!'D:D'=['wbOracle']'Cancel Requisition Lines'!'C16')*(['wbRefrence']Sheet1!'E:E'=['wbOracle']'Cancel Requisition Lines'!'I16')*(['wbRefrence']Sheet1!'F:F'=['wbOracle']'Cancel Requisition Lines'!'J16'),0),9)>=['wbOracle']'Cancel Requisition Lines'!M:M, ""materials supplied"","""")"

Loop

End Sub

'''
vba excel
1 Answers

You've got a great start to your code, so here are a few things to help get you on your way...

  1. Always use Option Explicit.
  2. Try to define your variables as close as possible to its first use (your current code is short enough to not matter much, its just a habit to get into).
  3. The Call usage has been deprecated and it's not needed. If you want to call a function or sub, just use the name of that routine.
    • Also, if you have a sub call that is by itself in a single statement, the parens are NOT required to enclose the parameters. If you're making the call in a compound or assignment statement, you MUST use the parens.
  4. A good habit is to always make it clear what workbook, worksheet, and range you are referencing with a fully qualified reference every single time. This one thing trips up so many VBA users.

For example, in your LoopTest1 code, you are referring to Cells. Without any qualifying reference, the VBA code assumes you are referring to the currently active worksheet (whichever and whereever that is). So define some intermediate variables and make it clear (see example below).

  1. To help clear up any confusion in your LoopTest1 sub, I added some parameters so that you can work on any two workbooks you choose.
  2. My own preference is to build up a complicated formula in a separate string variable so that I can examine it in the debugger and make sure it's exactly right. So you can see I defined a formulaText string and built up your formula.
  3. I "corrected" a few things I found in the formula (but I cannot tell you it will work), including:
    • Using the FullName property of both workbooks in the formula (so you never have it hard-coded)
    • Using the Name property of the worksheet (so you never have it hard-coded)
    • Properly arranging the single-tick marks for the proper workbook/worksheet reference (overall, you were using too many single-ticks in your formula)

Only you can determine if the formula is actually what you want and if it works. But that might be a separate question :)

Option Explicit

Sub Openfile()
    Dim wbOracle As Workbook
    Set wbOracle = ThisWorkbook

    Dim FileToOpen As Variant
    FileToOpen = Application.GetOpenFilename( _
                          FileFilter:="Excel Workbooks (*.xls*),*.xls*", _
                          Title:="Open Database File")
    If FileToOpen = False Then
        MsgBox "No file selected, cannot continue."
        Exit Sub
    End If

    Dim wbReference As Workbook
    Set wbReference = Workbooks.Open(FileToOpen)
    Workbooks.Open FileToOpen

    LoopTest1 wbOracle, wbReference, "Cancel Requisition Lines"
End Sub

Sub LoopTest1(ByRef wbOracle As Workbook, _
              ByRef wbReference As Workbook, _
              ByVal oracleSheetName As String)
    Dim wsOracle As Worksheet
    Set wsOracle = wbOracle.Sheets(oracleSheetName)

    Dim wsReference As Worksheet
    Dim referenceCell As Range
    Set wsReference = wbReference.Sheet1
    Set referenceCell = wsReference.Range("C1")

    Dim formulaText As String
    Do While Not IsEmpty(referenceCell)
        formulaText = "=IF(INDEX('[" & wbReference.Name & _
                      "]Sheet1'!A2000:M2000,MATCH(1,(['" & wbReference.FullName & _
                      "]Sheet1'!D:D=['" & wbOracle.FullName & _
                      "]" & wsOracle.Name & "'!C16)*('[" & wbReference.FullName & _
                      "]Sheet1!E:E=[" & wbOracle.FullName & _
                      "]" & wsOracle.Name & "'!'I16')*([" & wbReference.FullName & _
                      "]Sheet1!F:F=[" & wbOracle.FullName & _
                      "]" & wsOracle.Name & "'!'J16'),0),9)>=[" & wbOracle.FullName & _
                      "]" & wsOracle.Name & "'!M:M, ""materials supplied"","""")"
        wsOracle.Range("C16").Formula = formulaText
        Set referenceCell = ReferenceCell.Offset(1, 0)
    Loop
End Sub
user_47177550
Related