Backwards compatibility issue between VB Excel 2010 -> 2003

Hi All,
Wondering if I can get some expert advice from someone on my issue.
I've written some code for a script for slicing and dicing data which works perfectly in Excel 2010, but 2003 doesn't like it unfortunately.
Probably an easy one for an expert set of eyes, but my lack of real experience in scripting is hurting me here is I do not understand why what I have written fails in excel 2003.

The idea behind my code is that I'm searching a data set for parts in a given location; the output tells me if I have a part in a particular location based on the state and the part number.

Here is my code (or part of) that I get the error on:

Please Login or Register  to view this content.

The error I receive is a 1004 object defined error

Here is the rest

Please Login or Register  to view this content.

The reason for using variables in the VLOOKUP is because data is regularly added to the "All_Parts" tab so I figured doing it this way would ensure that the VLOOKUP searches all new data.

I've tried removing the $ signs from the line and reversing the "FirstRow" and "FirstCol"
I can confirm that this line ".Range("H2:H" & pRow).Formula = "=VLOOKUP(A2,All_Parts!$A$1:$B$1528,2,FALSE)"" works when I test with it so I know its something to do with the variables or how I've written it.
I've also considered my data sets, but they are all under 65535 lines so that shouldnt be an issue

Any feedback would be appreciated and I thank you in advance !!

All the best
Steve

Hi Steve

Please add Code Tags around your Code (must do)

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.

Hello Steve,

I can't see how it can work in XL 2010.

It fails probably because you're not specifying any column letters, just the row refs.

But why not simplify things by defining a dynamic range name in the Excel App. which automatically adjusts to the amount of data in the AllParts tab, then use the range name in your formula rather than worrying about which are the first and last rows.

Steve

Are you sure this is a version issue?

I can't see anything in the code that is version specific.

I think the problem might be with FirstCol/FirstRow/LastCol/LastRow.

FirstCol and LastCol should be column letters.

Also, in a cell reference the column comes first then the row.

Perhaps you could use Cells?

Please Login or Register  to view this content.

Originally Posted by Norie

Steve

Are you sure this is a version issue?

I can't see anything in the code that is version specific.

I think the problem might be with FirstCol/FirstRow/LastCol/LastRow.

FirstCol and LastCol should be column letters.

Also, in a cell reference the column comes first then the row.

Perhaps you could use Cells?

Please Login or Register  to view this content.

That did it !! - fixed my issue Norie - thanks so much for this information!!

I'm still going to investigate further on the dynamic range also. I'm up for anything that will help me to simplify my code!
Thankyou to all who provided feedback.
Steve