Line of code not translating from 2003 to 2007

[simjambra]

Hi there, I am trying to run a macro created in excel 2003 on my 2007 machine.

I don't know vba unfortunately and when I try to run a macro, I get the debugger. There is no issue with the Macro using xl2003.

The line of code it highlights, is the following.

LastRow = Master_Wksht.Range("N" & Rows.Count).End(xlUp).Row

Is there any immediate reason why this line might fail?

Thank you for your time.

[DonkeyOte]

There is nothing wrong with that line as it stands I'm afraid.

How is LastRow declared ?

In VBE - go to Debug -> Compile Project... do you get any errors ?

If so check Tools -> References and see if you have any libraries marked as Missing (eg Calendar Control etc...)

[simjambra]

Hi, thanks for the reply.

Compile Project didn't do anything. I tried the Tools -> References and it lists the available ones but I notice Calendar control is not there, is this one that should be?

[DonkeyOte]

I notice Calendar control is not there, is this one that should be?

No, don't fret

Sometimes pre 2007 files debug when run in 2007 because previously available libraries / controls are no longer available... the Calendar Control is a classic case in point (and often a culprit) given it does not exist in 2007 by default.

In this case the above is not an issue (nothing denoted as missing).


You didn't stipulate what the Debug was ... ie the error message itself ... can you ?

My next question would be that mentioned in the Italics previously - ie how is LastRow declared ?

If LastRow is declared as Integer and the last used cell in Col N appears after row 32767 you would generate a Debug (overflow) ... best to use Long rather than Integer (in 32 bit VBA Integers are converted to Longs "under the hood" so no gain in using Integer)

[simjambra]

Aha it was indeed an integer, changed it to a Long and all seems well.

Thanks for your help.