Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 863
  • Last Modified:

Another subscript out of range

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
Stephen Byrom
Asked:
Stephen Byrom
  • 13
  • 11
1 Solution
 
Christian de BellefeuilleProgrammerCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
if you can post your workbook necessary to run your code, it would help us too.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Christian de BellefeuilleProgrammerCommented:
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
 
Stephen ByromWarehouse/ShippingAuthor Commented:
"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
 
Christian de BellefeuilleProgrammerCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
 
dev00790Commented:
Which line no of your code do you get the error?
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Line 6
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
I end up on the open workbook at the correct cell address stated in Line5 of the above code snippet
0
 
Christian de BellefeuilleProgrammerCommented:
Then sheet stats doesn't exist
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Sheets "Stats" is where I end up at the correct cell address, but no data is entered
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Maybe it's to do with the "Replace" statement
0
 
Christian de BellefeuilleProgrammerCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
olorin: if the error is at line 6, then your workbook 2 should already be opened, and therefore it's not the error.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
Christian de BellefeuilleProgrammerCommented:
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
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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
 
Christian de BellefeuilleProgrammerCommented:
There is a space in your sheet name

It's "Link_Up " and not "Link_Up"
0
 
Christian de BellefeuilleProgrammerCommented:
Just rename your sheet properly by removing this space at the end of the sheet name and it should work.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
ARRRRRGH!!!!!!
how frustrating !!!

Thank you for pointing out the insane error!
and also the time you spent helping me.
0
 
Christian de BellefeuilleProgrammerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now