Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-05-2018, 07:32 AM   #1
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 590
Thanks: 9
Thanked 50 Times in 48 Posts
kevlray will become famous soon enough
Reset Named range.

I have an existing named range called TotalActions. But I want to use VBA code to change that range. But the code I have basically does nothing (i.e., the value for the named range TotalActions does not get modified). Everything I have found so far indicates that my code is correct.

With ActiveWorkbook.Names("TotalActions")
.RefersTo = "=AV1!$G5:$G8"
End With

Any suggestions will be appreciated.

kevlray is offline   Reply With Quote
Old 11-05-2018, 07:59 AM   #2
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 590
Thanks: 9
Thanked 50 Times in 48 Posts
kevlray will become famous soon enough
Re: Reset Named range.

Found out some further information. It appears my code sorta works. I tried a different range and it did change the range, but it is offsetting the rows by four rows. i.e., I set the range to A1:A5 and it shows the range as A5:A9. I have no idea why.
kevlray is offline   Reply With Quote
Old 11-05-2018, 08:36 AM   #3
kevlray
Newly Registered User
 
Join Date: Apr 2010
Location: Central California
Posts: 590
Thanks: 9
Thanked 50 Times in 48 Posts
kevlray will become famous soon enough
Re: Reset Named range.

Not sure, but it appears it was doing an offset from a active cell. When I set the active cell to A1, it appears to be working correctly now.

kevlray is offline   Reply With Quote
Old 11-07-2018, 05:10 AM   #4
Trevor G
Newly Registered User
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,329
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Reset Named range.

You can look to delete the named range before you recreate it, incase it is causing conflict. I would suggest record a macro to find the named range and delete it then create the new named range accordingly and you have the code.

__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2016
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Named Range VBA IanT Excel 1 06-22-2012 08:35 PM
Question Export to named range in excel with named ranges within the data merry_fay General 3 08-23-2011 04:16 PM
Cannot Expand named Range - But new file, no range, small result set lahuva Modules & VBA 6 02-08-2010 08:57 AM
TransferSpreadsheet with Named Range eschaef2 Modules & VBA 3 12-08-2008 02:24 AM
Cannot expand named range danny123 Excel 1 07-25-2006 05:16 AM




All times are GMT -8. The time now is 02:17 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World