Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subscript Out Of Range 1

Status
Not open for further replies.

MichaelRed

Programmer
Dec 22, 1999
8,410
US
Why oh why this error?

With the code below (first box), I only ever get the "Subscript out of range error" (error 9). I have a break point on the FUNCTION declaration line, and it doesn't even get to this? The second code box below is from the imediate window, so the calculation of the range seems adequate. I can evemn comment out the entire content of the procesure and STILL get the same error!!!!!


Code:
Public Function basMoveBulb()

    Dim rngFrom As Range
    Dim rngTo As Range
    Dim RngColFrom As String
    Dim RngColTo As String
    Dim quo As String * 1
    quo = Chr(34)
    RngColFrom = "G"
    RngColTo = "H"
    
    Dim ColMax As Integer
    Dim Idx As Integer

    ColMax = 714
    Idx = 1
    With Application.ActiveSheet
        For Idx = 1 To ColMax
            rngFrom = (quo & RngColFrom & ":" & Idx & quo)
            Debug.Print rngFrom
            Stop
            Idx = Idx + 1
        Next
    End With

Code:
Idx = 1
Quo = """
rngColFrom = "G"
rngFrom = (quo & RngColFrom & ":" & Idx & quo)
? rngFrom
"G:1"

MichaelRed


 
hmmmmmmmmmmm ... did this as best I (reasonably) can. Workbook/worksheet created by 'others'. Asked me to insert a col and cutcopy part of content of an existing column and paste to the new one. Obviously has the 700 + rows already.

I copied the rows to a NEW workbook, recreated the code in the new workbook. same song Nth verse.



MichaelRed


 
I c/p your code, and only got "error 91" - object errors I believe it was. BUT! unlike your delima, I did not have a problem trying to get the function to at least start. I was able to use F8 to step.

Did you try using "Repair" from the "add/remove prg" panel?

Did you try typing the same code in another workbook?

--Miggyd

--> It's a bird! It's a plane! No, it's an OS update patch! Ahh!! <--
 
I did another copy paste re-create and finally got some of it to work. As is common / usual with spreadsheet users, the conventions for adding information was ... inconsistient ... but I was able to programatically move ~~ 80% of the "stuff" over and just did the remainder manually (save the peace).

Excel seems to treat three periods together as an ?'Ellipsis'? and I cnn not see how to then find either the single character ("." - aka Chr(46)?) programatically, so there remain MANY of the three characters treated as one symbols. Otherwise satisfaction is achieved.





MichaelRed


 
What about Chr(133) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
thanks, PHV. Made the user fix it beforer the answer, but I'll certainly remember this one.

Some individuals should not be allowed to use a keyboard at all!!!



MichaelRed


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top