Excel sheet as variable – Looking for help

Asked By 30 points N/A Posted on -
qa-featured

Hi everyone,

I got a program that supposedly have to select a preferred sheet of excel from a workbook for importing in a table.

I got no trouble for the table creation from where to select the tab. Those are looking like the following things:

'ABC Homes$'                                                
                                               

After selected it is passed to a variable with this;    
                                                        

store customer to mcust                                
                                                           

When it is tried to select the sheet with this;            
                                  

.Application.Worksheets(mcust).Activate  && Select sheet
                                                           

OEL Error code 0x8002000b: Invalid index               
                                                           

Any ideas how to solve this thing? It is found that at the moment of storing value of a number over the variables then I get none error though there is no chance of correlating this sheet number along with names of sheet. It is highly appreciable for your support in advance.

Thank you

  • Answer Accepted: No
  • Question Category:
SHARE
Answered By 0 points N/A #140972

Excel sheet as variable – Looking for help

qa-featured

MAKE THE RANGE ADDRESS VARIABLE

You may wish to make the range references in the formulas variable depending on which worksheet is chosen from our list in the named range
"SheetNames"

Go back to the Worksheet we added and called "Worksheets" in C1 Enter the name "Range". In C2 downwards add range references that you want to correspond to each Worksheet name. For example, A1: G7 may correspond to Sheet1 in B2 and so would go in C2, G9: M15 may
correspond to Sheet2 in B3 ands so would go in C3 etc. You can use range names in place of cell addresses.

Select C1 and highlight down until the last formula row in Column "C". Nowgo to Insert>Name>Create. Ensure only "Top Row" is checked and click OK. Excel will now have created the named range "Range" and omitted C1. Now select B1: C<last row> (don't Start from A1) and click in the Name Box (left of formula bar) and type: MyTable and then push Enter.

Come back to the Worksheet (Formulas) we added the range name "SheetNames" to. In the cell next to this (B2) add this formula

=VLOOKUP(SheetNames, MyTable, 2, FALSE)

Click back in this formula cell and name it "RangeLook". Now use the two formulas below in place of the VLOOKUP and SUM formulas shown above

=VLOOKUP("Sales", INDIRECT(SheetNames&RangeLook), 2, FALSE)

=SUM(INDIRECT(SheetNames&RangeLook)).

About Loius Smit
Questions
2
Answers
9
Best Answers
2
  • Posted on -
  • Question Category:

From online database retrieving help of tracking audio CD data

Previous

Difficulty in Importing My Map Files

Next

Related Questions