VBA code line OK in earlier versions but not in XL 2007. Why?

**Richard Buttrey** · #1 08-08-2008, 02:32 PM

Hi,

In an application I developed for someone, I have a line of code

Code:

Range("M2") = "=MATCH(L2,'" & wbOld.Name & "'!$E:$E,FALSE)"

wbOld is a declared variable for an open workbook.

The application works fine on the Excel 2000 version on which it was developed, but not with the client's 2007 version.

Has anyone come across any documentation which might explain why this won't work in 2007. (I've not yet acquired 2007)

Thought I'd post it here first. If there's no feedback I'll move it over to the Excel 2007 forum.

Usual TIA

Richard B.

**royUK** · #2 08-08-2008, 02:44 PM

hi Richard

It seems fine in 2007. For testing I changed it slightly, this places the formula in a1 either in this workbook, or in another open workbook that is active.

Code:

Option Explicit

Sub test()

Dim wbold As Workbook
Set wbold = ThisWorkbook
Range("a1") = "=MATCH(L2,'" & wbold.Name & "'!$E:$E,FALSE)"
End Sub

**Richard Buttrey** · #3 08-08-2008, 02:53 PM

Hi Roy,

Thanks for the prompt reply. That's intriguing.

Would you mind looking at the actual application if I upload it. It's fairly small with a workbook and a csv file supporting it, and check to see if it falls over on your system.

Regards,

Richard

**royUK** · #4 08-08-2008, 02:58 PM

No problem Richard

**Richard Buttrey** · #5 08-08-2008, 03:52 PM

Thanks Roy,

Three zipped files attached.

The 'Price Updater Template' is the one containing the macros.
Click the button and a menu opens up, prompting you to load the 'Prices' file, then the 'Products' File, and when those two have been loaded, click the Update Data File button.

It's at this stage that the user reports that it's falling over at the line:

Code:

Range("M2") = "=MATCH(L2,'" & wbOld.Name & "'!$E:$E,FALSE)"

I'm interested to discover if this really is a version problem or something else. Let me know if you get an error.

Regards,

Richard

**royUK** · #6 08-09-2008, 04:27 AM

Hi Richard

I go the eror using yor uploaded files. however, when I converted the files to 2007 format (*.xlsm and *.xlsx) the code ran without errors.

let me know if you want the converted books.

**Richard Buttrey** · #7 08-09-2008, 04:59 AM

That's good news Roy, thanks for your assistance. I'll let the user know and ask him to do the same. Yes please email the converted template file. Can you briefly describe the process for converting the files. Is it just a question of opening the file and resaving with a .xlsm extension? Incidentally, what's the difference between .xlsm & .xlsx ? Sorry for such a basic question and hopefully it won't be too long before I upgrade to 2007 myself and become fully acquainted.

In the meantime I've been googling around and came across the following comment from Jon Peltier, which perhaps sheds a bit more light. I'd started to add the complete book.sheet reference to the cell but your solution should obviate the need for this. Presumably MS have built this translation into the conversion process.

Many thanks once again.

Richard

Quote:

Re: Excel 2007 VBA - Run-time error 1004
From: Jon Peltier
Date Posted: 1/25/2008 1:23:00 PM

This will break:

Range("A4", Range("A4").End(xlDown))

It should be

Range(Range("A4"), Range("A4").End(xlDown))

Even better to reference a sheet:

With Worksheets("Some Sheet")
.Range(.Range("A4"), .Range("A4").End(xlDown))
End With

Sometimes 2007 is a bit more finicky about these references.

Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com

**royUK** · #8 08-09-2008, 05:32 AM

Hi Richard

The extension *.xlsm is for workbooks containing macros, *.xlsx is for ordinary workbooks. Addins are saved in *.xlam format. When saving a *.xls file in 2007 you can choose which type to save as from the Office Button

I'll mail the converted workbooks.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)