Solved

Another subscript out of range

Posted on 2011-02-20
25
850 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
  • 13
  • 11
25 Comments
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
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:cdebel
Comment Utility
if you can post your workbook necessary to run your code, it would help us too.
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
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
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
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
Comment Utility
"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:cdebel
Comment Utility
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:cdebel
Comment Utility
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
Comment Utility
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
Comment Utility
Which line no of your code do you get the error?
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
Line 6
0
 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
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:cdebel
Comment Utility
Then sheet stats doesn't exist
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:Stephen Byrom
Comment Utility
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
Comment Utility
Maybe it's to do with the "Replace" statement
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
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:cdebel
Comment Utility
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
Comment Utility
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
Comment Utility
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:cdebel
Comment Utility
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:cdebel
Comment Utility
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
Comment Utility
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:cdebel
Comment Utility
There is a space in your sheet name

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

Accepted Solution

by:
cdebel earned 500 total points
Comment Utility
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
Comment Utility
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:cdebel
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now