Subscript Out Of Range 1

[MichaelRed]

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!!!!!

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

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

MichaelRed

 

[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

 

[MiggyD]

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!! <--

 

[MichaelRed]

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

 

[PHV]

What about Chr(133) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MichaelRed]

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