By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,650 Members | 812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,650 IT Pros & Developers. It's quick & easy.

Excel VBA : Loop Sheets that meets the value in the range

wassup
P: 34
Hey Guys,

Good Morning. I have probelm here.

I have 2 workbooks, 1 is Master.xls (my format to collect all the data), another 1 is Machine.xls (data collect from).

Inside Master.Range("B3:B5") have a data (etc: Line1, Line2, Line3), Machine.xls have a few sheets (etc: Line1, Line2, Line3....Line10).

Now my concern is I want the macro loop Master.Range("B3:B5") , if Master.Range = Line1 then it will loop Machine.xls untill found the sheets name is Line1 then collect data from it. It will loop Master.Range("B3:B5") untill equal to blank.

Thanks for your advice.
Jun 23 '08 #1
Share this Question
Share on Google+
2 Replies


kadghar
Expert 100+
P: 1,295
with a couple of IFS/THEN/ELSE and FOR/NEXT it can be done. To make reference to the workbooks, you can use their name or their index (the order they were open)

Check this little example:

Expand|Select|Wrap|Line Numbers
  1. If workbooks("myBook1").worksheets("sheet1).range("B1") = workbooks("myBook2").worksheets("sheet1).range("B1") then
  2. workbooks("myBook1").worksheets("sheet2).range("A4") = "Hello World"
  3. else
  4. workbooks("myBook2").worksheets("sheet2).range("C4") = "Goodbye Cruel World"
  5. end if
You can use Indexes or CELLS to put anything into a loop, eg:

Expand|Select|Wrap|Line Numbers
  1.  for i = 1 to 5
  2.     workbooks("myBook1").worksheets(i) = "Hello World"
  3. next
and you can always check the content or use the END method to go to the blank space, eg:

Expand|Select|Wrap|Line Numbers
  1. i=1
  2. do
  3.     i=i+1
  4. loop until cells(i,1) = ""
HTH
Jun 23 '08 #2

wassup
P: 34
with a couple of IFS/THEN/ELSE and FOR/NEXT it can be done. To make reference to the workbooks, you can use their name or their index (the order they were open)

Check this little example:

Expand|Select|Wrap|Line Numbers
  1. If workbooks("myBook1").worksheets("sheet1).range("B1") = workbooks("myBook2").worksheets("sheet1).range("B1") then
  2. workbooks("myBook1").worksheets("sheet2).range("A4") = "Hello World"
  3. else
  4. workbooks("myBook2").worksheets("sheet2).range("C4") = "Goodbye Cruel World"
  5. end if
You can use Indexes or CELLS to put anything into a loop, eg:

Expand|Select|Wrap|Line Numbers
  1.  for i = 1 to 5
  2.     workbooks("myBook1").worksheets(i) = "Hello World"
  3. next
and you can always check the content or use the END method to go to the blank space, eg:

Expand|Select|Wrap|Line Numbers
  1. i=1
  2. do
  3.     i=i+1
  4. loop until cells(i,1) = ""
HTH
Thanks kadghar,

Its make me many confusing because to many For, Do, Loop.... but I have done it. Have a nice day.
Jun 24 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.