VB Question - Cell value from multi worksheets

[H3llB3nt]

Hi All,

I have a workbook with about twenty worksheets. Te last worksheet is a summary page were I would like to display and calculate information from the other worksheets. My problem is I can't figure out how to reference cells from the other worksheets. On the summary page I have a calc button with this code:

Do While Range("B" & variable).value > ""

Here is a couple of things i've tried:

Do While Range("sheet1!B" & variable).value > ""
Do While sheet1.Range("B" & variable).value >""

Could someone tell me the right syntex or a better way to accomplish this.

Thanks!

[mudraker]

H3llB3nt

Please read posting rules. Links to the rules are in Red & blue text below.
Failure to follow the rules may see you message deleted.

There are several ways to do this

Please Login or Register  to view this content.

All the above lines of code test a cells contents. This is not the most efficient way to loop through cells.

For my view of Do & For type loops please see my last 2 replys at
http://www.excelforum.com/showthread...=591910&page=2

Without specifics of what you are trying to acheive I can only give general answers.

[H3llB3nt]

Sorry about missing the rules.

Below I have the code I came up with so far. On the "Summary" worksheet I have a button with this code behind it. what I'm trying to do is check cells on the "Administration" worksheet and see what's in certain cells. I then want to take that info and place it back on the "Summary" worksheet.

Please Login or Register  to view this content.

Thanks for any help you can give me!

[mudraker]

When testing the one cell for multiple answers it is more efficient to use Seelct Case that IF statements

Select Case tests the cells contents once
IF statements tests the cells contents for each IF and Elseif statement

Please Login or Register  to view this content.

[H3llB3nt]

Thanks for the reply Mudraker!

The code still seems to bomb on the first line though. I don't think that's the right syntax to reference another cell in a different worksheet. Any thoughts?

Thanks!

[mudraker]

I think i mis read & did not fully understand your problem

What is Administration!B & Administration!D supposed to refer to?

The correct way to use range is

Please Login or Register  to view this content.

Instead of range("a1") you could also use

Please Login or Register  to view this content.

[H3llB3nt]

That's it Thanks mudraker! I swear I tried that

Administration!D was refering to a cell in a different worksheet.

[mudraker]

Good to hear you have resolved your problem


one thing I did not list

to work with a named range or named cell on another sheet

use

Please Login or Register  to view this content.