Hi,
Tried to a macro recording of assigning a range name one cell lower then its current location. This is the statement I got:
[Code]
ActiveWorkbook.Names("SendIdentifier").RefersToR1C1 = "=Send!R2C1"
[\code]
Would like a statement to just set the range one cell lower than the current range location.
Thx.
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Does not work . . . following these instructions, just sets the range as a label with an address below the second row.
What am I missing?
[code]
Sub Send()
Dim rNextRange As Range
Sheets("Send").Select
Range("A" & (ActiveCell.Row)).Copy
Range("SendIdentifier").PasteSpecial xlPasteAll
Set rNextRange = Range("SendIdentifier").Offset(1)
ActiveWorkbook.Names.Add Name:="SendIdentifier", RefersTo:=rNextRange.Address
End Sub
[\code]
Please use Code Tags when posting code excerpts. Thanks.
Sorry, should have been something like:
Please Login or Register to view this content.
Thought it was expecting a string for the refers to address. But no
What exactly are you trying to do?
Regards, TMS
Thanks. I am just trying to bring a range down every time i copy some information into the range, to get ready to copy more later. I am basically creating records within a list.
Is it working for you now?
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
No it is not working. I am simply trying to drop the range down one cell to get it ready to copy again, not name the range a label which refers to the cell dropped down.
My entire code:
Please Login or Register to view this content.
What do you mean by 'label'?
If posting code please use code tags, see here.
This avoids the use of the .Add method to change the range that a name refers to.
Please Login or Register to view this content.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
In all honesty, I have no idea why you are doing this. I've never seen this approach before.
Why do you need to create individual named ranges for cells and then advance them a row at a time?
Would it not be a lot easier to determine the last/next row each time, copy the active row and paste the values? Be a lot neater and quicker.
Maybe it'd be an idea to post a sample workbook and explain what you are trying to achieve rather than how you are trying to achieve it.
Regards, TMS
An example:
Please Login or Register to view this content.
Regards, TMS
Ok, i have
Last edited by pglufkin; 07-12-2014 at 03:09 PM.
Ok, i have records of a very large list that i need to send to several different people who have different requirements and different 'need to know' priorities.
When i am on a row in that list, i will invoke this macro or a modification of this macro to send selected field records of that list to ranges in a blank worksheet. I then have a macro that sends that single worksheet to a specific email address.
Select data differs between individuals, so i have 20 variations of my code, 20 sub-routines all very similar, and 20 separate sheets they go to . . . . with each slightly modified sub assembling select records to select lists for select people.
The reason I am doing it this way is that the code is easy to change for each separate person's needs . . . and people's information needs change over time.
There may be a much easier way to do this, but this is the best this VBA third grade equivalent can muster. All for any better ways to do this. Code must be easy to change for the different people's needs.
Thank you for your help! Changing ActiveWorkbook.Names.Add Name:="SendYear", RefersTo:=rNextRange.Address to ActiveWorkbook.Names.Add Name:="SendYear", RefersTo:=rNextRange did the trick! And I figured it out 'ahh by mysef'. I guess I should give credit to TMS, cuz this person is the one that got me the farthest. Thx TMS!
On a side note, I yearn for the days of Lotus 123 old macros. Back 'in the day' i could have done this whole thing in about 10 minutes. This project is now logging 3 hours. This is progress? This old CEO doesn't think so. Developing dystopia at its finest IMO.
Last edited by pglufkin; 07-12-2014 at 03:18 PM.
Thanks and you're welcome. And thanks for the rep.
If it were me, I'd have a configuration worksheet. On that sheet, I'd have two rows per individual, or type of individual. The first row would list the ID for the individual, the email for that individual, and the source columns (on the active row); and the second row would hold the target/output sheet, and list the target cells on the output sheet.
Then I'd use the individual's ID to locate his/her records (MATCH). I'd loop though the source columns (on the active row), copying them to the corresponding target cells. Then use the stored email address to send the email. Probably read both the source and target lists into arrays for speed.
Even easier if it's always a range of columns, like A to L. No need for 20 subroutines. Depending on the format/structure of the output sheet, you could maybe have a template (rather than 20 "similar" worksheet and build the "variable" parts from scratch.
Obviously, the devil's in the detail but that would be the principle.
Whatever, if it works, it works
Regards, TMS
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Ok, thx. Not entirely sure what you are saying as far as X's and O's but I see your concept. Again, too complicated for me to figure out at this point. Because of the nature of what I am doing, cannot bring a programmer into this as much as I would like. May be killing a fly with a large stick . . . but its my stick and I know how to wield it. Thx again. Now to figure out how to mark a case solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks