I have an Excel workbook that contains a lot of macros in it (programmed with
Excel 97). When I try to excute some code with Excel 2007 the column's index
seems to start at 0 instead of 1.

example: Target.Cells(1,9)... refers to J1 instead of Target.Cells(1,10)

But if I go on the worksheet enter someting in A1 and excute the code again:

Target.Cells(1,9)... refers to I1 and now Target.Cells(1,10) refers to J1.


Any ideas????

Hi Clau -

It's likely not the column indexing that is causing the problem. It sounds
like some other code in your macros sets the value of Target to a range. The
property '.Cells(1,9)' refers to row1/col9 of that range and not to row1/col9
of the worksheet.

Initially, that range must start in column B resulting in Target.Cells(1,9)
referring to J1 (J is the 9th column in the range 'Target'). Furthermore,
the fact that simply entering a value in A1 changes Target.Cells(1,9) to
refer to I1 means that the variable 'Target' is set when a macro executes and
Target is dynamically set relative to entries on the worksheet somehow.

If you can find the statement(s) that set the variable 'Target', you could
monitor it's address under several scenarios with different worksheet
contents. Then you'll be in a better position to modify your code as needed.
Add the following statement after any statement that contains the variable
Target:

Msgbox Target.Address

If you're having trouble, post the code and we'll take a look.

---
Jay


"Clau" wrote:

I have an Excel workbook that contains a lot of macros in it (programmed with
Excel 97). When I try to excute some code with Excel 2007 the column's index
seems to start at 0 instead of 1.

example: Target.Cells(1,9)... refers to J1 instead of Target.Cells(1,10)

But if I go on the worksheet enter someting in A1 and excute the code again:

Target.Cells(1,9)... refers to I1 and now Target.Cells(1,10) refers to J1.


Any ideas????

Thank you very much for your help, you gave me a good tip.

I was setting Target using Sheet(...).UsedRange, and the used range was
starting on column B.


"Jay" wrote:

Hi Clau -

It's likely not the column indexing that is causing the problem. It sounds
like some other code in your macros sets the value of Target to a range. The
property '.Cells(1,9)' refers to row1/col9 of that range and not to row1/col9
of the worksheet.

Initially, that range must start in column B resulting in Target.Cells(1,9)
referring to J1 (J is the 9th column in the range 'Target'). Furthermore,
the fact that simply entering a value in A1 changes Target.Cells(1,9) to
refer to I1 means that the variable 'Target' is set when a macro executes and
Target is dynamically set relative to entries on the worksheet somehow.

If you can find the statement(s) that set the variable 'Target', you could
monitor it's address under several scenarios with different worksheet
contents. Then you'll be in a better position to modify your code as needed.
Add the following statement after any statement that contains the variable
Target:

Msgbox Target.Address

If you're having trouble, post the code and we'll take a look.

---
Jay


"Clau" wrote:

I have an Excel workbook that contains a lot of macros in it (programmed with
Excel 97). When I try to excute some code with Excel 2007 the column's index
seems to start at 0 instead of 1.

example: Target.Cells(1,9)... refers to J1 instead of Target.Cells(1,10)

But if I go on the worksheet enter someting in A1 and excute the code again:

Target.Cells(1,9)... refers to I1 and now Target.Cells(1,10) refers to J1.


Any ideas????