vba

[coquinn]

Hi I am trying to link a loop in VBA to a referenced worksheet. Specfically I am trying to let values in two sheets equal each other i.e.
For i = i_start To 17
For j = j_start1 To 14
Worksheets("Analysis").Cells(i, j + 55).Value = Worksheets("Analysis FR").Cells(i + 164, j + 54).Value
Next j
Next i
However the loop gets violated whenever I move a column. Does anyone know what the bext way to achiev this would be? Many thanks

 

[coquinn]

Btw I mean referenced to a labelled worksheet cell.

 

[sweater_vests_rock]

coquinn.

i guess i'm confused a bit by what you mean when you say "violated." if you mean that your loop no longer works with the right columns, then it seems like you just need to set your i_start and j_start to the appropriate starting columns. i think this would most easily be accomplished via an inputbox or perhaps a userform.

another option would be to use a named range for your cells, and then reference the named range in your program. that way, as long as the named range moves when you insert columns, your program will access the right cells.

am i missing something?
ben.

 

[coquinn]

No thats perfect...How exactly would you incorporate the referenced cell into the loop?

 

[sweater_vests_rock]

coquinn.

you would want to define a name for the cell Cell(i_start, j_start). you can do this in your workbook with the shortcut key ctrl+F3

once you have this name defined, you can refer to it in your code as

Range("MyNamedRange")

since you have defined MyNamedRange to be the cell with at row i_start and column j_start, all you need to do to get these values in VBA is

i_start = Range("MyNamedRange").Row
j_start = Range("MyNamedRange").Column

if you insert columns, your named range will move correspondingly, which will ensure that i_start and j_start are correct.

let me know if this makes sense...
ben.