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!

Register Log in
  • Congratulations strongm 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

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


 
Sort by date Sort by votes
Well, apart from the fact that there is no End Function in your code, you are declaring rngFrom as a Range, and then treating it as string in your code.

Then you have to consider that if you want to build a range then "G:1" is not a valid range.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Upvote 0 Downvote
the end function was just chopped off in the copy. I chnged the range to be numeric ... this, however, does not address the basic issue: I do not even get to the declaration line which has a breakpoint. Also, As previously noted, Even when I comment out EVERY LLINE EXCEPT THE DECLARATION LINE AND THE END FUNCTION LINE I STILL get the SAME ERROR!!!

It seems more like what I am NOT doing than what What I am doing wrong?



MichaelRed


 
Upvote 0 Downvote
How are you calling the function ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
....and please do try to calm down - Glen asked valid questions based on what you posted. If you changed things afterwards, how was Glen meant to know that?
Garbage In = Garbage Out

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Does the project compiles properly ?
No event procedures in the workbook ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Try calling it from a sub

Sub Test()
Call basMoveBulb()
End Sub

Use F8 to step through the code, starting from the sub and let control pass to the function. What line does it break at/on ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
The procedure compiles properly. There are no other procedures (either functions or subs) in the workbook.

Sorry if I came across as 'upset', I was just trying to empahise the point ... it gives the same error if i comment out the entire body of the procedure.




MichaelRed


 
Upvote 0 Downvote
Just copied the function to VBE, entered ? basMoveBulb()

hit debug - 2 errors straight away

rngFrom is defined as a RANGE and therefore needs a SET statement when creating it

As well as that, you have not used the RANGE qualifier when attempting to create the range which is causing type mismatch errors within the brackets as VBA is attempting to perform a calc rather than a string concatenation

what is it that you are trying to do here ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Did test

various attempts to call it also return the "subscript out of range error. agaiin, no compile error here, there or elsewhere.

MichaelRed


 
Upvote 0 Downvote
Can't get a subscript out of range error - when I paste it in I get a "Object Variable with block variable not set" error but then again, I am using the initial code you posted.

What is the code you are now using ?

What KIND of module is this in? standard, class, sheet or workbook ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
thanks to all attempting to help.

xlbo , the point is I am NOT getting to this point. I 'think' I can deal with the errors - once the (*&^(*^&(*&^* thing actually gets to that point ... but I just keep getting the subscript out of range thing. It is quite frustrating. It is either a setting or something I'm not doing.

MichaelRed


 
Upvote 0 Downvote
Code: 
Public Function basMoveBulb()

    Dim rngFrom As Range
    Dim rngTo As Range
    Dim RngColFrom As Integer
    Dim RngColTo As Integer
    RngColFrom = 7
    RngColTo = 8

    Dim ColMax As Integer
    Dim Idx As Integer

    ColMax = 714
    With Application.ActiveSheet
        For Idx = 1 To ColMax
            Set rngFrom = Cells(RngColFrom, Idx)
            Debug.Print rngFrom
            Stop
            Idx = Idx + 1
        Next
    End With

End Function
Public Sub Test()
    Call basMoveBulb
End Sub



MichaelRed


 
Upvote 0 Downvote
If it makes any difference, Im on Excel 2003 sp2



MichaelRed


 
Upvote 0 Downvote
Set rngFrom = Cells(RngColFrom, Idx)
So, RngColFrom is a row number (actually initialized to 7) and idx a column number (varying from 1 to 714).
Which version of Excel admits more than 255 columns ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
hmmmmmmmmmmmm ... reasonable quesion ... so switch the args to the Set rngFrom ... :

Set rngFrom = Cells(Idx, RngColFrom)

still stuck back at the subscript thinggy. not even getting to the breakpoint on the declaration line. still/again seems to not be internal to the procesure but something not set in the environment.

What needs to be done for Xl to recognize the procedure?



MichaelRed


 
Upvote 0 Downvote
Michael - that's why I was asking where the function was - I have pasted the code into a new standard module in a new workbook and it runs through ok - no errors....I have tried it in all different types of module and with chart sheets active (so there is no "active cell" but to no avail - I cannot reproduce the error

That being so, I would suggest that you try entering it into a brand new workbook and see what happens there....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
I'd get rid of all code in this workbook, shut down excel, relaunch excel, create a brand new module in the workbook and recreate the function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
Back coloring an entire row if a duplicate is found in a dynamic range 1
Replies
18
Views
682
SkipVought
S
Katto
  • Locked
  • Question
VBA permutation of arrays
2
Replies
22
Views
950
Katto
Katto
IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
422
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Outlook Template replace text 2
Replies
13
Views
2K
Andrzejek
Andrzejek
feipezi
  • Locked
  • Question
Do we have a VBS version of listing files in a folder?
Replies
2
Views
242
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top