Solved: VBA: 'Subscript out of range' Error ...

[romainaccor]

Hi guys!!!

I am trying to reach and activate a worksheet within a workbook located in a folder stored on a server. So heres my code line but when I run it, it displays the 'Subscript out of range' error....

Sheets("\\servername Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\H3600.XLS[SOI]").Activate

servername : I couldnt write it all cuz the website prevents posting with active links...so just assume its correctly written in my code and see if the mistake can come from smw else...

H3600.XLS being the name of the workbook and SOI being the name of the worksheets, Ive proceeded to some test and those names are the REAL ones. So can you see any mistakes in my code?

Thanks!!!

[Bob Phillips]

If the workbook is open, just activate the book without the directory path, then activate the sheet.

If it isn't, open it, then do the previous.

[romainaccor]

Hi

The workbook wont be open... I have hundreds of those workbooks..so it has to go get the values within those workbooks on the server... I know its possible ive done it under a different form...and I cant figure this one out !!! Any idea?

[p45cal]

Hi

The workbook wont be open... I have hundreds of those workbooks..so it has to go get the values within those workbooks on the server... I know its possible ive done it under a different form...and I cant figure this one out !!! Any idea?

Then I don't believe you can use the command like that.
Perhaps instead:
[vba]Workbooks.Open "\\servername\Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\H3600.XLS"
'it has become tha active workbook at his point
Sheets("SOI").activate[/vba]
You're saying you want to get values from within a closed workbook?
A worksheet formula can do this with a formula like:

='\\servername\Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\[H3600.XLS]SOI'!$C$10

but vba can't do it directly (indirectly it could do it by placing such a formula in a cell then reading the value of that cell).

[Bob Phillips]

... I know its possible ive done it under a different form...

I think we would all like to see that form and its code.

[romainaccor]

Ill send it to u tomorrow, from the office.

[romainaccor]

Then I don't believe you can use the command like that.
Perhaps instead:
[vba]Workbooks.Open "\\servername\Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\H3600.XLS"
'it has become tha active workbook at his point
Sheets("SOI").activate[/vba]
You're saying you want to get values from within a closed workbook?
A worksheet formula can do this with a formula like:

='\\servername\Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\[H3600.XLS]SOI'!$C$10

but vba can't do it directly (indirectly it could do it by placing such a formula in a cell then reading the value of that cell).

Hello

Thx for answering,

well actually after the selection of this sheet I will use the "find" command, to find a cell within this sheet and return the value of the cell next to it...

I have used this path previously in a vba sub that i ran and it worked perfectly. I used it with vlookup function that I modified in VBA.

I dont know if this can help you! I will try your hints tomorow at the office.
Thanks a lot guys its already a relief to have people answering my questions!

[geekgirlau]

A worksheet formula can do this with a formula like:

='\\servername\Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\[H3600.XLS]SOI'!$C$10

Why don't you just create a vlookup formula using a reference to the closed workbook?

[romainaccor]

Why don't you just create a vlookup formula using a reference to the closed workbook?

That would work but the value I have to return is on the left from the identified value ... and I dont think we can put a negative number to shift to the left in the vlookup formula...?

[romainaccor]

I think we would all like to see that form and its code.

Here s the code which works within the vlookup formula...

[VBA]For R_Look = 5 To 10
For C_Look = 6 To 12 'trouver la formule pour qu il sarrete a la colonne avant TOTAL

Sheets("MTD").Cells(C_Look, R_Look).FormulaR1C1 = _
"=VLOOKUP(RC[" & 3 - (R_Look) & "],'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\" & Year & "\" & Month & "\[Flash Report Stat_End.xls]Brand - AED'!R7C1:R35C14," & R_Look & ",FALSE)"
Next
Next[/VBA]

[geekgirlau]

Okay, so to clarify your code is creating a vlookup formula, with a reference to a range within a closed workbook. It doesn't actually need to "activate" the closed workbook. You can have formulas referring to a closed workbook, but it is not possible to activate a workbook without opening it first.

I would suggest that you stick with a vlookup formula as in your example, which is going to be much quicker than opening the workbook and finding a value within a sheet.

It would also be a good idea to set some constants here (path and workbook name) if you are going to refer to them often - it will make the formula much easier to read.

[vba]
Dim R_Look As Integer
Dim C_Look As Integer
Dim strFullPath As String

Const cPATH = "\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\"
Const cWORKBOOK = "Flash Report Stat_End.xls"


strFullPath = cPATH & Year & "\" & Month & "\[" & cWORKBOOK & "]"

For R_Look = 5 To 10
For C_Look = 6 To 12 'trouver la formule pour qu il sarrete a la colonne avant TOTAL

Sheets("MTD").Cells(C_Look, R_Look).FormulaR1C1 = _
"=VLOOKUP(RC[" & 3 - (R_Look) & "],'" & strFullPath & "Brand - AED'!R7C1:R35C14," & R_Look & _
",FALSE)"
Next
Next
[/vba]

[romainaccor]

So heres the whole code Im trying to make work... can you see where the problem is??

[vba]Sub try()
Workbooks.Open "\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009_
\10_October\P&L\H3600.XLS"
Sheets("SOI").Activate
Dim Val As String
Val = "total net revenue"

ActiveSheet.Cells.Find(What:=Val, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate


End Sub
[/vba]
It displays the following error message : Object variable or with block variable not set .

[romainaccor]

==> Geekgirlau

Thank you. That would save time and memory not to open every single workbook thats right...!! Could help me make the find function work within this closed workbook to go get a value which is located in a cell 2 columns on the left from the target cell that I identify with the find function. If you can help me with this function and help me understand how it works you will have made not only my day but my month !!!

[geekgirlau]

What is the structure of the data in the closed workbook? Is your target always going to be in the same column?

[romainaccor]

Yes all the time, they are in the column G (the target), and then the values I have to return are located in column E

[geekgirlau]

Your formula will be something like this (obviously you have to build in the workbook name, sheet name and the range containing the lookup value):

[vba]
=INDEX(TargetSheet!$E:$G,MATCH(SearchValue,TargetSheet!$G:$G,False),0)
[/vba]

INDEX: Range, Row, Column - returns a single value from the given row and column within a range. In this case we're using the MATCH function to get the row number, and the first column within that range.

MATCH: Return the number of the cell containing the search value within a specific range. The SearchValue is the value you are searching for, and False means that we are looking for an exact match (rather than the closest match). Because we're looking in a single column, this will return the first row in that column that matches your search value.

If the search value is not found, you'll get an #N/A error.

[romainaccor]

range("w6").FormulaR1C1 =INDEX(\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\[H3573.xls]SOI!$E:$G,MATCH("total net revenue",\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\[H3573.xls]SOI!$G:$G,False),0)


Error : Expected expression..
Have I written stg wrong?

[romainaccor]

oops sorry heres the formula I wrote

range("w6")=INDEX(\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\[H3573.xls]SOI!$E:$G,MATCH("total net revenue",\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\[H3573.xls]SOI!$G:$G,False),0)

[geekgirlau]

The quotation marks are going to trip you up on this.

[vba]
Dim strFullPath As String

Const cPATH = "\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\"
Const cWORKBOOK = "H3573.xls"

strFullPath = "'" & cPATH & Year & "\" & Month & "\P&L\[" & cWORKBOOK & "]"

MySheet.Range("w6").Formula = "=INDEX(" & strFullPath & "SOI'!$E:$G," & _
"MATCH(""total net revenue""," & strFullPath & "SOI'!$G:$G,False),0)"
[/vba]

A simple way to work this out is to create the formula manually in a workbook. Try this:

• Open the workbook H3573.xls
• Create a new workbook, go to a blank cell and type an equals sign
• Navigate to H3573.xls and click on any cell
• Press [Enter] to finish your formula
• Close H3573.xls

Now have a look at the formula:

• It starts with an equals sign, which your formula didn't have.
• In code you have to treat it as a string, which means it has to have double quotation marks around it.
• Because your formula also needs double quotation marks around the total net revenue, you need to put a double set in as in my example above.
• Look at where Excel puts the single quotes in the formula - this is because your path contains spaces.
• I can't stress enough how much easier it is working with variables and constants rather than inserting your full path into the code. Your formula has enough going on already without having to decipher the path every time you're trying to work out what's going on.

It takes practice getting your head around working with strings and variables combined. I find if you work in several stages it's easier. For example, you can use placeholders where your variables are supposed to go. Start with:

[vba]
MySheet.Range("w6").Formula = "=INDEX(MyPath[Myfile]SOI'!$E:$G," & _
"MATCH(searchtext,MyPath[Myfile]SOI'!$G:$G,False),0)"
[/vba]

Insert the next bit:

[vba]
MySheet.Range("w6").Formula = "=INDEX(MyPath[Myfile]SOI'!$E:$G," & _
"MATCH(""total net revenue"",MyPath[Myfile]SOI'!$G:$G,False),0)"
[/vba]

Now work out where the constant for your path is going to go:

[vba]
MySheet.Range("w6").Formula = "=INDEX(" & x & "SOI'!$E:$G," & _
"MATCH(""total net revenue""," & x & "SOI'!$G:$G,False),0)"
[/vba]

Finally insert the actual variables:

[vba]
MySheet.Range("w6").Formula = "=INDEX(" & strFullPath & "SOI'!$E:$G," & _
"MATCH(""total net revenue""," & strFullPath & "SOI'!$G:$G,False),0)"
[/vba]

[romainaccor]

What a perfect answer ! Thank you so much for having taken the time to not only give me the procedure but also to have explained it ! I made it fit to my code and it works perfectly !!
I hope one day Ill be able to help you out with some VBA issues !
Have a great day