Excel VBA 2016 32bit - Userform - Runtime Error 9 - Error message "Script out of Range"

Question

I have created a new project Userform in a new workbook and copied the code below from another project, which is operating perfectly with this code. I copied the code into this new workbook project, but I keep getting the Runtime error, even after I modified the textbox name.

The purpose of the code is to automatically generate a Work Order number in the textbox txt_Work_Order_No prefixed by "SWC" and then by year, month and then the number format. The final output should look like this SWC201706-00001 (incrementing each time the form is opened).

 Private Sub UserForm_Initialize()
 '** WORK ORDER NUMBER IN TEXTBOX

                    Me.txt_Work_Order_No.Enabled = True
                        Dim irow As Long
                        Dim ws As cnServiceLog
                        Set ws = cnServiceLog

                       'find last data row from database'
                       irow = ws.Cells(Rows.Count, 1).End(xlUp).Row
                       Me.txt_Work_Order_No = "SWC" & Year(Date) & Format(Month(Date), "00") & "-" & Format(Split(ws.Cells(irow, 1).Value, "-")(1) + 1, "00000")

I stepped through the code, and broke down to the code line that was giving me grief and I commented the rest of it out and everything was fine, but obviously it won't do want I would like it to do.

The area which causes the Runtime error is right after the double zero(Month(Date,"00")

Me.txt_Work_Order_No = "SWC" & Year(Date) & Format(Month(Date), "00") & "-" & Format(Split(ws.Cells(irow, 1).Value, "-")(1) + 1, "00000")

I hope someone would be able to help me out .... I have searched the internet for days and still haven't been able to resolve whatever problem it seems to have. With gratitude, TheShyButterfly

Just a quick check - if you do it like this, would it work? Me.txt_Work_Order_No = "SWC" & Year(Date) & Format(Month(Date), "00") & "-" & Format(Split(ws.Cells(irow, 1).Value, "-")(0) + 1, "00000") — Vityata
Thank you for your quick response. What you suggested is exactly what I do have, but it doesn't work. The code is on it's own full line (not wrapped as it appears to be here) I can't understand why it's causing me grief. It works perfectly in the other project. At first I thought it was because I was previously on Excel 2016 64bit (which gave me grief because the project was initially developed on 2013 32bit), so I uninstalled 64bit and installed Excel 32bit .. but the problem remains — TheShyButterfly
It is not what you have, I have changed (1) to (0) in your code. Use this to see the differences textdiff.com — Vityata
My apologies, I didn't see that. I have changed it, and now I get the Runtime 13 error - Type Mismatch :( — TheShyButterfly
So the problem is in the value that you have in ws.Cells(irow,1). Probably it is a string. Thus, change it to Me.txt_Work_Order_No = "SWC" & Year(Date) & Format(Month(Date), "00") & "-" & Split(ws.Cells(irow, 1).Value, "-")(0) — Vityata

Vityata Vityata · Accepted Answer · 2017-06-26T15:17:49

In general, I suppose that the problem is that when you split ws.Cells(irow,1), you do not have any - value. Thus, you go out of range of the array. Try like this first:

Me.txt_Work_Order_No = "SWC" & Year(Date) & Format(Month(Date), "00") & "-" & Format(Split(ws.Cells(irow, 1).Value, "-")(0) + 1, "00000")

If it works, then try to put a variable for the (0) value, based on the UBound of the array. Something like this:

Dim lngUbound as long
lngUbound = ubound(Split(ws.Cells(irow,1),"-")
Me.txt_Work_Order_No = "SWC" & Year(Date) & Format(Month(Date), "00") & "-" & Format(Split(ws.Cells(irow, 1).Value, "-")(lngUbound) + 1, "00000")

The idea of the code above is that you are not guessing that you always have at least one -, thus, when you split by this sign, you always take the last item of the created array. Play a bit with the code, it should work.

Excel VBA 2016 32bit - Userform - Runtime Error 9 - Error message "Script out of Range"

1 Answers