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:

Dim R As Range
Dim C As Range
For Each RngCell In DataRange
    For i = 1 To ActiveRange
    RngCell.Activate
    Set R = ActiveCell.Row
    Set C = ActiveCell.Column
    Wks"DPML".Activate
    Set DPMLCell = Range("R:C")

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:

For each rngcell in datarange
r=rngcell.row
s=rngcell.column
worksheets(index).cells(r,c).value=rngcell.value ' or whatever you exactly wanted to copy/move from data range to the worksheet specified by index
next rngcell

This is what I have but its not quite working:

    For i = 1 To ActiveRange
    R = i.Row    'GETS CAUGHT UP RIGHT HERE
    C = i.Column
    DPML = ECTWorkbook.Worksheets("DPML").Cells(R, C)
    Set DPMLCell = DPML.Offset(i, 4)

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

    Set DPML = ECTWorkbook.Worksheets("DPML").Cells(R, C)

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?