Call Same Cell in Athr Wks during Loop

Hi,

I am running a loop through a range in one worksheet but need to copy a cell from a different worksheet. Therefore I cannot just call up the other worksheet and copy the cell because it changes in the loop and I don't want it hardcoded. Is there a way to copy the cell row and column from the loop I am running on one worksheet and then use it in the second worksheet. Something like the following:

Please Login or Register  to view this content.

I did not follow your code 100%, but I have an idea. First suggestion I would make would be to abandon the "activate" method. It is rarely if ever needed. Unless I have severely misundeerstood, this would make your code look more like:

Please Login or Register  to view this content.

This is what I have but its not quite working:

Please Login or Register  to view this content.

How are your variables dimensioned? Are R and C still dimensioned as Range objects? If so, you will get a typemismatch error, because the row and column properties of a range object return a long data type.

What is i demensioned as? For the For syntax, I'm guessing it would be dimensioned (or assumed by VBA) to be an integer data type. An integer data type doesn't have a row or column property. Along these same lines, is activerange now an integer data type as well? I guess this bit here all boild down to do you want to use a For Each loop as originally proposed, or do you want to use a counter variable (For i=1 to activerange.count) to loop through the range. Either approach will work, but you have to decide how you want to do it and dimension/use your variables accordingly.

R and C are dimensioned as Long Objects. They are now registering but something is wrong with the code

Please Login or Register  to view this content.

I'm sure its just something dumb i'm doing in the .cells part of the code.

p.s. thanks for the help...i really appreciate it.

Can you be more specific RE: "something is wrong with the [line of] code?" With a few assumptions about the context of the code, the line given is syntactically correct. This line of code takes the variable DPML, if it is undimensioned or dimensioned as a variant it is assigned the "range" object data type, and DPML is then assigned the necessary information to refer to the given cell on the given worksheet (assuming R and C are integers/longs).

Are you getting an error? Is the statement not executing? Is it executing, but doing something it shouldn't? What is this statement supposed to be doing? As before, what data type is each variable supposed to have?

I'm getting a "object required" error

Please Login or Register  to view this content.

DPMLRow is dimensioned as a numeric variable (Long).
The .Row property returns a number (Long)
The problem you are having is putting this within a Set statement. The Set statement is only used when assigning an object reference to an appropriate object variable. The same error is going to occur on the following statement as well.

It appears that what you really want is a Let statement

Let DPMLRow = AdjustCell.Row

Let is used to assign numbers to numeric variables. Set is used to assign an object reference to an object variable.

Most programming languages have made the Let keyword optional in a Let statement. So:

Let mylong=5
and
mylong=5

are equivalent statements. Most programmers leave the Let keyword out, but, if it helps you keep Let and Set statements straight, it's not incorrect to include the Let keyword. The Set keyword is not optional.