Solved

Index/Match range inside of For Each Selected worksheet

Posted on 2011-02-22
4
214 Views
Last Modified: 2012-05-11
set wb = workbooks.open(myFile)
for each wksht in wb.Worksheets
  for i = 2 to myCount
    wksht.Range("C1:T5000") ' This will actually be an index/match
  next
next

Open in new window


Trying to figure out how to select a range inside of the for each loop of a set of worksheets. Need this for an index/match function that I'm creating, but I'm getting an "Object doesn't support this property or method" error.

Thanks!
0
Comment
Question by:keats88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 34956053
Did you try using the select method?

wksht.Range("C1:T5000").Select

Of course, after you select, you'll need to do something with Selection or it will be a useless task.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 125 total points
ID: 34956186
You cannot select a range on an inactive sheet. If you really want to select it you must to wksht.Activate first.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34956194
You might need to activate the workbook too.

However, it is good practice to avoid selecting if you can (and you usually can).
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34956454
Agree with Tommy about selecting.  You can simply reference any cell you want with the Cells method, then the cells are not selected but you can read/write anything you want to them, even on other sheets.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question