Dynamic Ranges [Excel VBA 2010]

[lloydgodin]

I have about 15+ spreadsheets that calculate/lookup different things. The data changes often which affects my ranges (for which there are a vast many) and it causes issues when I update the data and forget to update the ranges in VBA. I was reading about dynamic ranges and I have spent the last 1/2 hour trying to get the code here to use a dynamic range without success.

Old Code:

Please Login or Register  to view this content.

Tried the following but nothing is working

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any help would be appreciated. I'd like to fix the code in the calcs so I don't have to remember to update it constantly.

[protonLeah]

Please Login or Register  to view this content.

[lloydgodin]

Getting the Run-time error '1004': Method 'Range' of object '_Worksheet' failed error.
In debug I placed a watch on rng, the data on the table is contained in an 'array' within the value2 expression/values.

[protonLeah]

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 Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
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 button and wait until the file has uploaded.
10. Close the window and then click Submit.

[lloydgodin]

Figured it out. It had to do with my Vlookup() syntax after setting the rng value.

Please Login or Register  to view this content.