PDA

View Full Version : [SOLVED] Subscript out of range problem



Kerry H
04-17-2019, 02:11 PM
Can you see why the following code generates a "subscript out of range" error at the last line?
The Sheets(Sh) convention works elsewhere. The Rng1A.copy seems to have taken place ok.


Dim Rng1A, Rng2A, Rng1I, Rng2I As Range 'range blocks to copy
Dim N as Long, D As Date, Sh As String
N = ActiveWorkbook.Sheets("NAVIGATION").Range("G12") ' value is correct
D = ActiveWorkbook.Sheets("NAVIGATION").Range("H13") ' value is correct
Sh = "Sheet" & N ' value is correct
With ActiveWorkbook.Sheets("INV ACC")
Set Rng1A = Range("D15:I214")
Set Rng2A = Range("M15:W214")
End With
With ActiveWorkbook.Sheets("INV INPUT")
Set Rng1I = Range("H15:L214")
Set Rng2I = Range("S15:Y214")
End With
Rng1A.Copy
Workbooks("Centris Periods.xlsm").Sheets(Sh).Range("D15:I214").PasteSpecial xlPasteValuesAndNumberFormats

Thank very much.

p45cal
04-17-2019, 03:23 PM
Is the workbook Centris Periods.xlsm open?
Is Centris Periods.xlsm the name of the workbook exactly right?
Is what's in Sh (a sheet name)? Is it spelt exactly correctly (no missing/extra leading/trailing spaces)?

Kerry H
04-17-2019, 04:45 PM
Yes, yes, yes, yes

david000
04-17-2019, 08:16 PM
Workbooks("Centris Periods.xlsm")
Have you tried it without the file extension?

大灰狼1976
04-17-2019, 09:39 PM
Hi Kerry!
Although the value of variable Rng1A may be wrong, it does not affect the running of the program.
There are only a few reasons for errors
1. target workbook was not opened
2. Wrong target workbook name
3. the value of variable Sh is wrong

Why not upload attachments?


--Okami

p45cal
04-18-2019, 02:03 AM
It's 99% certain to be one of the things mentioned by Okami/myself.

Try adding these three lines directly after your .Copy line (but before the PasteSpecial line):

Workbooks("Centris Periods.xlsm").Activate 'errors if you have the workbook name wrong or the book isn't open.
ActiveWorkbook.Sheets(Sh).Activate 'errors if the sheet name is wrong.
Range("D15:I214").Select 'this will fail if the code is in a sheet's code module and that sheet isn't Workbooks("Centris Periods.xlsm").Sheets(Sh)
These three lines are just temporary for debugging, you should remove them later.


As an aside, both your With…End With blocks are currently redundant; you're missing some dots:

With ActiveWorkbook.Sheets("INV ACC")
Set Rng1A = .Range("D15:I214")
Set Rng2A = .Range("M15:W214")
End With
With ActiveWorkbook.Sheets("INV INPUT")
Set Rng1I = .Range("H15:L214")
Set Rng2I = .Range("S15:Y214")
End With

snb
04-18-2019, 02:42 AM
Why not simply using ?


ActiveWorkbook.Sheets("INV ACC")Range("D15:I214").copy Workbooks("Centris Periods.xlsm").Sheets("sheet" & [navigation!G12]).Range("D15")


or


Workbooks("Centris Periods.xlsm").Sheets("sheet" & [navigation!G12]).Range("D15:I214")=ActiveWorkbook.Sheets("INV ACC")Range("D15:I214").value

Kerry H
04-18-2019, 07:49 AM
It's 99% certain to be one of the things mentioned by Okami/myself.

Try adding these three lines directly after your .Copy line (but before the PasteSpecial line):

Workbooks("Centris Periods.xlsm").Activate 'errors if you have the workbook name wrong or the book isn't open.
ActiveWorkbook.Sheets(Sh).Activate 'errors if the sheet name is wrong.
Range("D15:I214").Select 'this will fail if the code is in a sheet's code module and that sheet isn't Workbooks("Centris Periods.xlsm").Sheets(Sh)
These three lines are just temporary for debugging, you should remove them later.


As an aside, both your With…End With blocks are currently redundant; you're missing some dots:

With ActiveWorkbook.Sheets("INV ACC")
Set Rng1A = .Range("D15:I214")
Set Rng2A = .Range("M15:W214")
End With
With ActiveWorkbook.Sheets("INV INPUT")
Set Rng1I = .Range("H15:L214")
Set Rng2I = .Range("S15:Y214")
End With


Do you mean write it as:

Rng1I = Workbooks("Centris").Sheets("INV INPUT").Range("H15:L214")

p45cal
04-18-2019, 07:55 AM
Do you mean write it as:

Rng1I = Workbooks("Centris").Sheets("INV INPUT").Range("H15:L214")

Most definitley NOT. For 2 reasons:
1. Your current code is only missing 4 dots (as shown in red).
2. Your line's missing the Set word.
and possibly a 3rd: Is the workbook called Centris, or Centris Periods?

How did you get on with adding those 3 temporary debugging lines?

Kerry H
04-18-2019, 08:12 AM
I was referring to your comment about redundancy, which I didn't understand.
I did add the dots to the code I had previously.

Alternatively,does this work?

Set Rng1I = Workbooks("Centris").Sheets("INV INPUT").Range("H15:L214")

If so, are 2 of these statements better than the "With ...End" With structure?
BTW I didn't need the error tracking code. But I've saved it for another time!
When is the "Set" instruction used, generally?

Thanks foryour help.

Kerry H
04-18-2019, 08:18 AM
Why not simply using ?


ActiveWorkbook.Sheets("INV ACC")Range("D15:I214").copy Workbooks("Centris Periods.xlsm").Sheets("sheet" & [navigation!G12]).Range("D15")


or


Workbooks("Centris Periods.xlsm").Sheets("sheet" & [navigation!G12]).Range("D15:I214")=ActiveWorkbook.Sheets("INV ACC")Range("D15:I214").value


I guess I'm trying to keep things straight in my mind, as a newbie to VBA. Never sure at this point in my experience what will work and what not. Thankis for the suggestions.
Kerry

p45cal
04-18-2019, 09:51 AM
I was referring to your comment about redundancy, which I didn't understand.
I did add the dots to the code I had previously.Good, that's all you needed to do to stop the redundancy; you used With ~~~, but never used the dot notation that goes with it, so the With…End With wouldn't have worked.


Alternatively,does this work?

Set Rng1I = Workbooks("Centris").Sheets("INV INPUT").Range("H15:L214")It should do except you've got to get the workbook's name correct; is it Centris or Centris Periods?
It's little inaccuracies like these that lead to Subscript out of range errors.


If so, are 2 of these statements better than the "With ...End" With structure?They save you time when writing the code in several ways; you don't have to keep retyping the references, just use a dot. If you want to change something in the references you only need do it it one place. I've heard that it's less resource hungry because the references don't have to be interpreted every time.

BTW I didn't need the error tracking code. But I've saved it for another time!WHAT??!! The subject of this thread is Subscript out of range problem. Those 3 lines of code were designed to find which of the subscripts was out of range… nothing else. Have you solved the problem without telling us?!

When is the "Set" instruction used, generally?Something along the lines of Set being needed when the variable is an object variable as opposed to being a plain old variable. The variable in this case is very probably an object, because it looks like you intended them to be ranges with the line:

Dim Rng1A, Rng2A, Rng1I, Rng2I As Range 'range blocks to copy
which only Dims Rng2I as a range. You probably intended:

Dim Rng1A As Range, Rng2A As Range, Rng1I As Range, Rng2I As Range 'range blocks to copy

Kerry H
04-18-2019, 12:55 PM
Good, that's all you needed to do to stop the redundancy; you used With ~~~, but never used the dot notation that goes with it, so the With…End With wouldn't have worked.

It should do except you've got to get the workbook's name correct; is it Centris or Centris Periods?
It's little inaccuracies like these that lead to Subscript out of range errors.
The names of the WBs are correct. As I indicated earlier I added the dots.

They save you time when writing the code in several ways; you don't have to keep retyping the references, just use a dot. If you want to change something in the references you only need do it it one place. I've heard that it's less resource hungry because the references don't have to be interpreted every time.
Will adopt this approach.

WHAT??!! The subject of this thread is Subscript out of range problem. Those 3 lines of code were designed to find which of the subscripts was out of range… nothing else. Have you solved the problem without telling us?!
As part of earlier post I thanked everyone for their replies and indicated that the problem had been solved. I had the wrong WB active when running the Sub. That message doesn't seem to have "taken". Perhaps part of a reply was erased before I sent it.

Something along the lines of Set being needed when the variable is an object variable as opposed to being a plain old variable. The variable in this case is very probably an object, because it looks like you intended them to be ranges with the line:

Dim Rng1A, Rng2A, Rng1I, Rng2I As Range 'range blocks to copy
which only Dims Rng2I as a range. You probably intended:

Dim Rng1A As Range, Rng2A As Range, Rng1I As Range, Rng2I As Range 'range blocks to copy

I have read that "Dim x, y, z as Long" works. Not with Ranges?

Thanks very much for your help, p45cal, as always. It is very much appreciated.
Thanks also to all who replied. Your suggestions will be useful in the future.

Paul_Hossler
04-18-2019, 02:01 PM
I have read that "Dim x, y, z as Long" works. Not with Ranges?


For ALL Dim-ed variables unless you explicitly state "As ……." the default is to type them as Variant

It will compile and probably run, but it might not be what you wanted

In the above, "x" and "y" as Variants, and "z" is a Long




Dim Rng1A, Rng2A, Rng1I, Rng2I As Range 'range blocks to copy

Likewise. Rng1A, Rng2A, and Rng1I are Variants, while Rng2I is a Range

You lose the Intellisense if you do not type objects (especially) as what the really are


All object variables need 'Set'

Kerry H
04-18-2019, 04:45 PM
Thanks Paul.