Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Another subscript out of range

Posted on 2011-02-20
25
Medium Priority
?
860 Views
Last Modified: 2012-05-11
the following code thows up an out of range error.

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Aresponse As Integer
Dim Bresponse As Integer
    If ActiveSheet.Range("WeekNum").Value = 1 Then
    Aresponse = MsgBox("You can only use this button once in the same week" & vbCrLf & _
        "The Scoreboard has already been updated this week." & vbCrLf & vbCrLf & _
        "You may run the routine again from Next Monday onwards." & vbCrLf & vbCrLf & _
        "If you need to edit the Scoreboard you can only do so" & vbCrLf & _
        "by changing the data directly in the Scoreboard Workbook" & vbCrLf & vbCrLf & _
        "Do you want to open the Scoreboard Workbook now?", vbYesNo + vbDefaultButton2, _
        "Function already performed.")
        If Aresponse = vbYes Then
        Application.Workbooks.Open Replace(ThisWorkbook.FullName, "Performance", "Scoreboard", , , vbTextCompare)
        Exit Sub
        End If
        If Aresponse = vbNo Then
        Exit Sub
        End If
    Else
    Bresponse = MsgBox("Have you entered ALL this weeks Variables?", vbYesNo + vbDefaultButton1, _
    "ALL entries complete?")
        If Bresponse = vbYes Then
        ActiveSheet.Range("UpdateWk").Value = ActiveSheet.Range("ActualWk").Value
            Set wb1 = ThisWorkbook
            Set wb2 = Application.Workbooks.Open(Replace(ThisWorkbook.FullName, "Performance", "Scoreboard", , , vbTextCompare))
        FinalRow = wb2.Sheets("Stats").Range("B1048576").End(xlUp).Row
        entryrow = FinalRow + 1
            wb2.Sheets("Stats").Range("B" & entryrow).Value = wb1.Sheets("Link_Up").Range("B2").Value
            wb2.Sheets("Stats").Range("C" & entryrow).Value = wb1.Sheets("Link_Up").Range("E2").Value
            wb2.Visible True
        End If
        If Bresponse = vbNo Then
        Exit Sub
        End If
    End If

I'm trying to copy from wb1 to wb2.

As always, all help is appreciated.
0
Comment
Question by:Stephen Byrom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
25 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937242
Subscript out of range is usually caused by an index that is out of range.   For example, in your sample, you defined some "named range" like "ActualWk", "UpdateWk"... make sure that they exist.

I would also try to trace your code to check what's the entryrow value when it crash.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937244
if you can post your workbook necessary to run your code, it would help us too.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937266
Thanks for the speedy response.
The code refers to two workbooks.
They are not my creations and are in the process of being changed from workbooks to a database.

The named ranges are in the active workbook and update accordingly.
The final row is row 9 which is correct in wb2.
The entry row is 10 which is also correct.
I think it may be the way I am refering to the workbooks and ranges that is in error.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937295
Have you been able to trace your code to see where it hang?

Can you tell me how these ranges have been defined?
UpdateWk
ActualWk
WeekNum

You say that these range update accordingly but i don't see any code that change these range definition.  So make sure that they are valid upon crash (add an On Error to trap the error, and check for their definition when it crash)
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937343
"You say that these range update accordingly but i don't see any code that change these range definition.  So make sure that they are valid upon crash (add an On Error to trap the error, and check for their definition when it crash) "

UpdateWk is a named cell on the activesheet ("Link_Up") it is updated to the ("ActualWk") value when the user has updated all cells and chooses to continue by a yes response to the variable "Bresponse".
ActualWk is a named cell on the activesheet ("Link_Up"). it has the formula (=WEEKNUM(A27)). where A27 has the function (=Today())
WeekNum is a named cell on the activesheet ("Link_Up"). it is set to 1 if UpdateWk = ActualWk, and 0 if the condition is not met.

the following code is the full routine with error handler;
Sub MainButton_Click()
On Error GoTo Err_MainButton_Click
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Aresponse As Integer
Dim Bresponse As Integer
    If ActiveSheet.Range("WeekNum").Value = 1 Then
    Aresponse = MsgBox("You can only use this button once in the same week" & vbCrLf & _
        "The Scoreboard has already been updated this week." & vbCrLf & vbCrLf & _
        "You may run the routine again from Next Monday onwards." & vbCrLf & vbCrLf & _
        "If you need to edit the Scoreboard you can only do so" & vbCrLf & _
        "by changing the data directly in the Scoreboard Workbook" & vbCrLf & vbCrLf & _
        "Do you want to open the Scoreboard Workbook now?", vbYesNo + vbDefaultButton2, _
        "Function already performed.")
        If Aresponse = vbYes Then
        Application.Workbooks.Open Replace(ThisWorkbook.FullName, "Performance", "Scoreboard", , , vbTextCompare)
        Exit Sub
        End If
        If Aresponse = vbNo Then
        Exit Sub
        End If
    Else
    Bresponse = MsgBox("Have you entered ALL this weeks Variables?", vbYesNo + vbDefaultButton1, _
    "ALL entries complete?")
        If Bresponse = vbYes Then
        ActiveSheet.Range("UpdateWk").Value = ActiveSheet.Range("ActualWk").Value
            Set wb1 = ThisWorkbook
            Set wb2 = Application.Workbooks.Open(Replace(ThisWorkbook.FullName, "Performance", "Scoreboard", , , vbTextCompare))
        FinalRow = wb2.Sheets("Stats").Range("B1048576").End(xlUp).Row
        entryrow = FinalRow + 1
            wb2.Sheets("Stats").Range("B" & entryrow).Value = wb1.Sheets("Link_Up").Range("B2").Value
            wb2.Sheets("Stats").Range("C" & entryrow).Value = wb1.Sheets("Link_Up").Range("E2").Value
            wb2.Visible True
        End If
        If Bresponse = vbNo Then
        Exit Sub
        End If
    End If
Exit_MainButton_Click:
    Exit Sub
Err_MainButton_Click:
    MsgBox Err.Description, vbInformation, "Pleae report the Error Description to S.Byrom"
    Resume Exit_MainButton_Click
End Sub

Hope that helps.
Thanks for your time
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937354
That might also look stupid, but make sure that your sheet names exist and that you don't have any typo error.  If the sheet doesn't exist, it does give a subscript out of range too.

So Sheets "Stats", "Link_Up" must exists.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937385
What i meant by add an On Error, is to trap the error of course, but you should set a breakpoint on your MsgBox line, and when it trigger, check for every indexes (sheet names, named cells, and variables index like entryrow and FinalRow).

This is where your error is...
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937433
Ok
I got rid of the Finalrow and entryrow and gone a different route.

see the updated code snippet
If Bresponse = vbYes Then
        ActiveSheet.Range("UpdateWk").Value = ActiveSheet.Range("ActualWk").Value
            Set wb1 = ThisWorkbook
            Set wb2 = Application.Workbooks.Open(Replace(ThisWorkbook.FullName, "Performance", "Scoreboard", , , vbTextCompare))
            wb2.Sheets("Stats").Range("B1048576").End(xlUp).Offset(1, 0).Select
            Selection.Value = wb1.Sheets("Link_Up").Range("B2").Value
            wb2.Visible True
        End If
        If Bresponse = vbNo Then
        Exit Sub
        End If

Open in new window


I end up in the opened workbook at the correct location I want to add data, but it ends up with the same error
0
 
LVL 1

Expert Comment

by:dev00790
ID: 34937442
Which line no of your code do you get the error?
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937457
Line 6
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937460
I end up on the open workbook at the correct cell address stated in Line5 of the above code snippet
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937475
Then sheet stats doesn't exist
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937482
Sheets "Stats" is where I end up at the correct cell address, but no data is entered
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937486
Maybe it's to do with the "Replace" statement
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937507
Sorry, you said line 6.  So WB1 must contain Link_Up sheet

Why do you put wb2.visible?   It should be visible already when you open it.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937510
olorin: if the error is at line 6, then your workbook 2 should already be opened, and therefore it's not the error.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937515
wb1 does contain "Link_Up" sheet

wb2.visible is copied code from another sub. Doesn't need to be there really.
I have removed it now.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937518
wb2 is already open, I think it may be the "replace" line that causes the error. (the refernece to wb1 may be gone after the replace).
I am fairly new to coding so you will have to forgive my ignorance
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937533
If the reference to wb1 is lost, you would get a different error.

It would be "Object variable or With block variable not set" (Runtime error 91)
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937547
If the error is really on this line as you say:
 Selection.Value = wb1.Sheets("Link_Up").Range("B2").Value

The only possible reason would be that "Link_Up" to not exist in wb1.  Are you sure it's "Link_Up" and not "Link Up"?  

Even if your selection would contain multiples cells, this line that you have mentionned shouldn't cause any error.  It would copy the value in every cells of the range.

I know that E-E doesn't like that (and i don't too, because it doesn't give chances for other experts to help), but if you don't want to send your file here for security purpose, you can find my email in my profile.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 34937564
I have attached the files.
As I said earlier, they are a mess.
The button on the sheet "Link_Up" has the code
Plant-2-Performance-Wk-5-2011.xlsm
Plant-2-Scoreboard-WK-5-2011.xlsm
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937595
There is a space in your sheet name

It's "Link_Up " and not "Link_Up"
0
 
LVL 10

Accepted Solution

by:
Christian de Bellefeuille earned 2000 total points
ID: 34937597
Just rename your sheet properly by removing this space at the end of the sheet name and it should work.
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 34937619
ARRRRRGH!!!!!!
how frustrating !!!

Thank you for pointing out the insane error!
and also the time you spent helping me.
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 34937631
lol :)  Glad i helped.   I had this error many times in the past, and usually when it say subscript out of range, it got to do with something with an array.   "Sheets" are contained in an array.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question