Solved

Index/Match range inside of For Each Selected worksheet

Posted on 2011-02-22
4
202 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
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now