Set Range to Existing Range Name based on criteria

[MFrancis]

General Background:
I am working on a Construction budget that has data input for multiple cost codes on multiple sheets. I need to extract this data into a single sheet, but still in specific locations (form required by the Owner).
I have assigned names to the ranges of both the existing data and the locations that will be populated by the data. The name format I used is "VExt##.##.###Tb" and "VIN##.##.###Tb" where ##.##.### is the cost code.
For example, the cost code for direct building rough cleaning is 05.01.742; The range containing the existing direct building rough cleaning costs is named "VIN05.01.742Tb" and the name of the range the data is to go into is "VExt05.01.742Tb". (FYI, I use the Z to keep them all at the bottom of the names manager, the IN denotes "where data was input", Ext denotes "where data is extracted to", and Tb denotes table which is a std protocol of mine when generating names to distinguish cell references from tables.)

My approach:
1) Loop through the workbook names, find the ones that start with "ZEXT"
2) Find the corresponding ZIn names (start with "ZIN" AND have the same cost code as the current name ZEXT... name).
3) Extract data from the ZIn to the ZExt ranges.

The Problem:
In #2 above, I can get a variable to match the corresponding ZIn range name, but I cannot find a way to use that variable to select the actual VIn range.
Here is what I have (one of the versions):16782
In case you can't see the attached screenshot, below is a copy of the problem section of my code. The problem occurs just after the 'troubleshoot lines. I have spent 2 days trying to set the VIn and VExt ranges (and/or names). I have tried a myriad of combinations of variable types, methods, formats, etc. I think if I can get this part to work, the code that follows should work.......

I apologize in advance for my novice, I am self taught with just enough ability to be dangerous. I also thank whoever can help me with this VERY MUCH in advance This is my first time using a forum for help. I won't mind any input on how to better use the forum for questions like this either. As an FYI, I did spend hours searching this forum and a bunch of others for my answer before posting this thread.


Sub FillData3()
'
' FillData Macro
' Fills Current Details in the Deliverable Report Tab


'Define Input Range Variables
Dim RIn As Range
Dim RInRows As Integer
Dim RInRow As Integer
Dim RInCol As Integer 'may not need
'Define Output Range Variables
Dim RExt As Range
Dim RExtRows As Integer
Dim RExtRow As Integer
Dim RExtCol As Integer 'may not need
'Define Data Value Variable
Dim d, i As Long, n As Long
'Define Row Delta Variable
Dim rowdelta As Integer

'Cycle through Ranges (Extra.Cost Codes)(Set RExt & RIn)
For Each nm In ThisWorkbook.Names
If Left(nm.Name, 4) = "ZExt" Then
'
'Start troubleshoot
Dim RInv As String
RInv = "ZIn" & Mid(nm.Name, 5)
MsgBox "nm is " & nm & vbNewLine & "nm.Name is " & nm.Name & vbNewLine & "RInv is " & RInv
'End troubleshoot
'
RExt.Name = nm.Name
MsgBox "RExt is " & RExt
RIn.Name = "ZIn" & Mid(nm.Name, 5)
MsgBox "RExt is " & RExt & vbNewLine & "RIn is " & RIn
End If
Next

[SamT]

Dim RInv As String
RInv = "ZIn" & Mid(nm.Name, 5)
MsgBox Range(RInv).Value


Range("SalesTax") = Range("InvoiceTotal") * Range("LocalTax")
Range("SalesTotal") = Range("InvoiceTotal") + Range("SalesTax")
MsgBox "The total cost of this sale is " & Range("SalesTotal")

[MFrancis]

Thanks Sam,
That little section of troubleshoot code, however, was just to get the range name I need. It returns, for example, ZIn05.01.742Tb which is the range name that correlates to (same cost code) the current ZExt range name in the loop. The problem is, I cant figure out how to turn that value into a range. It's still just a string. I need to convert the string to range, or otherwise get the same value in the form of a range. It would be good if I could also convert the current nm.Name into a variable of Range type.
It's hard to explain in writing. I'm probably not explaining very well. I do appreciate the help.

[MFrancis]

I added the troubleshoot section with the idea that I would delete it later. It's the code that follows that I can't make work. I need to set the RIn variable (as range) to the range name that the RInv is spitting out in the troubleshoot section. I also planned on having the RExt variable (as range) be the current nm in the loop.

I.e.
The workbook contains the names ZIn05.01.742Tb and ZExt05.01.742Tb (and a whole bunch of other "pairs" for all of the cost codes in the budget). I need to be able to identify the 2 correlating names, set them as usable variables, and use those variables in the subsequent code to move data, add/delete rows, etc. for those 2 ranges. Then do the same with the next pair.

[SamT]

I get a "Invalid Attachment" message when I try to download from your first post. Can you upload the workbook again?

[MFrancis]

What I attached previously was just a screenshot. The actual workbook is big. A bunch of tabs etc. Would take a bit to figure out and too big to upload. I extracted 2 of the applicable tabs to the attached workbook (along with the macro I am working on). I deleted a bunch of lines to get it down to an allowable upload size. The "Deliverable Budget" tab is where the data is going. The "Direct Building" tab is one of the tabs the data is coming from (there are several others).

[SamT]

I spent some time analyzing your workbook. Note that I have over 20 years experience in Construction in areas as disparate as Custom Closets to Geothermal Power Plants and have developed several Estimating systems and several report systems.

Man, I don't know where to start. I guess I'll start with some raw data

497 =Number of Named Formulas (Ranges)
Some names refer to Numbers, some to Strings
351 refer to Blocks of cells
348 will need to be Dynamic. "Woe, woe, woe," says the coder.
About 70 refer to another Workbook, of least one of which is a block range

Inserting Rows into a named Range does not change the Name reference. It still refers to the original count of Rows

At least one column's formulas refer to Uniits. The Range name is Units. Ctrl+H can fix that.

The workbook appears to have been designed for manual data entry and to be formula driven. Then, you thought add VBA to it and created all those horrible Named Ranges.

In Rows 1 to 36 on Deliverable Budget, you have a bunch of validation? lists and some formulas. Move them to Sheets "Validation Lists" and "Calculations". Move all constants to sheet "Constants." Name those Ranges, hide the sheets when you put the Project into production.

[SamT]

I am going to parse and analyze this code

For c = 1 To "RowDelta" 'the Quotes around RowDelta are an error
Range("RExt").Select
ActiveCell.Offset(0, 13).Select
Selection.Resize("RExtRows", 1).Select
CurrentSelection.End(xlUp).Offset(1, 0).Select 'There is no word CurrentSelection
EntireRow.Select 'Probably a no object error
Selection.Copy 'Never pasted anywhere
Selection.Insert Shift:=xlDown
RExtRows = RExtRows + 1
Next
End If



Range("RExt").Select
ActiveCell.Offset(0, 13).Select
Same as Range("RExt").Cells(1).Offset(, 13).Select

Range("RExt").Cells(1).Offset(, 13).Select
Selection.Resize("RExtRows", 1).Select
Selection.End(xlUp).Offset(1, 0).Select
Same As Range("RExt").Cells(1).Offset(, 13).End(xlUp).Offset(1).Select

Range("RExt").Cells(1).Offset(, 13).End(xlUp).Offset(1).Select
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
Same As Range("RExt").Cells(1).Offset(, 13).End(xlUp).Offset(1).EntireRow.Insert

All that boils down to

For c = 1 To RowDelta
Range("RExt").Cells(1).Offset(, 13).End(xlUp).Offset(1).EntireRow.Insert
Next
RExtRows = RowDelta
And I would do it

rw = Range("RExt").Cells(1).Offset(, 13).End(xlUp).Offset(1).Row
For c = 1 To RowDelta
rows(rw).Insert
Next

[SamT]

I can't suggest strongly enough that you put "Option Explicit" at the tops of all your code pages.

Menu Item Debug >> Compile is your best friend. Use it often and frequently, every time you think you have a small bit of code finished.

The reason I only parsed a small bit of your code is because Debug >> Compile kept pointing out errors every couple of lines.


If you are interested in starting over, I think that with very minor alterations in the Worksheet layout of Deliverable Budget we can get your Project to working with a couple of User Defined enumerations, a couple of Finds, and a few Offsets, and most importantly, without 348 Static Named Ranges.

But, before we get to that point. . . Do you intend that this be used manually or ar you going to create a Data Entry UserForm?

[MFrancis]

THANK YOU very much Sam,
You don't know the half of it. Note, The Company just moved me into my current position a couple of weeks ago. The history of this workbook and number of people that have done stuff to it is as complex as that of China or Israel. If it were up to me, I would just start from scratch ..... but that is not going to happen. In fact, I have to be careful about how much I change the look or function of it at all from the users perspective. You are right about it starting as a data entry workbook (actually 2 workbooks). When I got here a couple of weeks ago, they were not macro enabled. The team here was copying and pasting all the information from one workbook (with all the tabs I am calling data input i.e. ZIn ranges) into another (the deliverable tab that I am calling extracted data i.e. ZExt ranges) in order to deliver it to the client. The first time I helped 2 other people do this, it took us a full day. Total waste of time, and huge risk of error. That is when I took it upon myself to do this project. BTW, I have also been in Construction for 30 yrs., most of which as a Project Manager.

I have no doubt my code is horrible. Over the years I have managed to make VBA do what I need it to, but a combination of using the macro writer then editing its code and finding code on the internet has surely left me a programmers nightmare. All of the "Z" names in the workbook were added by me. I thought I would have to have them in order to match the input data to the output data in my code. Which brings me back to my original problem. I am unable to set the range variables (RExt & RIn) to the range names. All of your comments above are super helpful, and I will definitely make changes based on them, but I am still stuck if I can't set the ranges. That is to say, I can't use Range ("RIn").anything if I can't tell it what RIn is in the first place. Does that make any sense? Anyway, I will keep plugging away at it. I really appreciate your help.

[MFrancis]

Oh, the next part of your reply hadn't come through yet when I wrote my last. I will put "Option Explicit" at the top of all my code pages...... I will also research out what it means/does.
I was unable to debug what I was writing (and still am) because it all depends on the 2 range variables (RIn and RExt) which I cannot set.
I have no problem with starting over. The only thing you need to understand is that I am dealing with some politics. Different people have done things they don't want changed, and some are reluctant to allow any changes at all. My intent is to make this work without making it look like I have made any significant changes. I even learned how to hide all of the Z names I created today, which I have done in the workbook I am working in.

My intent was to have a button on the deliverable page that would run the macro bringing in all the data. The macro would also then copy everything and paste it as value so the whole sheet becomes static, hard data. This workbook has so many calculations going on that if you don't have a descent computer, it is barely usable. I don't want to make it worse that way. Also, It will be good to have a fixed "Picture" of everything at whatever time the macro was ran.

[MFrancis]

Ps. I will send the whole workbook if there is a way. I am also happy to give you my phone number and/or call you if that helps.

[SamT]

My intent is to make this work without making it look like I have made any significant changes.

The only two changes I think are needed to the worksheet's structure are hidden Rows above the blue Phase header rows (57 et al) on Deliverable Budget, and inserting a column in front of C on Direct building, then formatting it so it 'disappears' into Column B.

As I see it att, the first production run of the Project can continue to use manual Data Entry. A dirty trick to get everybody on the same page is to volunteer to do the data entry, then return in five minutes with a completed workbook. :D

Even that very inelegant use of the top 35 rows on Deliverable Budget can stay. If you twist my arm hard enough. :bat2:

[MFrancis]

That sounds good. FYI, I am meeting with my boss on Wed. to review the revised template. Hopefully I can get it reasonable by then. He wants to use it on our next budget deliverable - which we are actually starting now, and is due to the client as a preview on the 16th, and final on the 23rd.
It's funny you should mention those 35 top rows. There used to be about 70. I was told to leave it alone, but I just couldn't stand all the extra blank rows so I deleted them. Figure I will take the slap on the wrist if somebody objects.
Bear in mind, what I sent you is only 2 tabs of a workbook that has 19 tabs. I have convinced my boss to let me get rid of a bunch of them though.
It's hard going from owner of my own GC company and/or PM where I call the shots and can make my systems however I want to .... this.

[MFrancis]

BTW, if the other workbook some of the ranges are referring to is "Cpt Est Template 080416" or similar with another date, that is the actual workbook I copied the 2 tabs from so I could post them here.

[SamT]

Two weeks? LOL. Cheap, good, or fast. Pick two.

Lessee if we can ballpark this baby. 19 tabs, if these two are anywhere near common, thats >100 categories per tab. Ask the boss how long it would take two men to frame and finish 10 different bathrooms, assuming he had two men qualified on all steps involved.

This is more akin to taking an old knob and tube house with 50' era fixtures and plumbing and turning it into a modern smart home controllable from an I-phone. Starting from design and engineering. And face it, herein you are just a semi skilled laborer. :(

OTOH, these two are actually pretty well designed. If this is the standard level of design, this wouldn't be too hard.

I would say 160 hours, but I know my guesstimating habits, so 320 hrs to turnkey and CO. Just don't ask how many hours a week I put into this, my hobby.

The main difference in the way you have been looking at this workbook and the way I see it is, I just see patterns of Blocks of cells. You see 348 different Ranges and I see one pattern repeated many times.

The first step for you is to put that darn book on a diet.

Invisible used ranges?
Reduce Excel File Size (http://www.vbaexpress.com/kb/getarticle.php?kb_id=83)
Better?
Reduce Excel file size (http://www.vbaexpress.com/kb/getarticle.php?kb_id=1091)

too many Styles?
http://excel.tips.net/T002135_Deleting_Unwanted_Styles.html


hidden Left over Code bits?
http://www.spreadsheet1.com/vba-project-code-cleaner-for-access-excel-powerpoint-word.html

and

http://www.appspro.com/Utilities/CodeCleaner.htm

[MFrancis]

All true, but I'm up to the challenge. I am a fast learner, and I work 18+ hrs. a day, including many weekends (like this one). I've done a wide range of projects from old home to smart home restoration (only mine was a 1929 home) to the 8 & 9 $figure multi-family projects I am working on now. The one thing I have learned is "Can't Do" is rarely true. It will still have to be a work in progress, but I will find a way to get it at least functional for this next budget proposal. I know you can't spend all of your time helping me. I am amazed at, and very grateful for, how much time you have already put in. Point me in the right direction, and I will do the heavy lifting. If you live in the San Jose area, I'm taking you to lunch!

[MFrancis]

Also, when I first got here, the workbook was so cluttered with styles, it kept saying there weren't enough resources to do anything. I used (and occasionally run) the following code to correct the problem. Let me know if you see any issues or improvements I should employ. I can't take credit for the code, I found it on the internet....:

Sub ResetStyles()
Dim styT As Style
Dim intRet As Integer
On Error Resume Next
For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then
If styT.Name <> "1" Then styT.Delete
End If
Next styT
End Sub

[SamT]

If you live in the San Jose area, I'm taking you to lunch! I visit family in Farmington occasionally.

I standard offer is one brand new dollar bill and a certificate of appreciation or a letter of appreciation on company letterhead, suitable for framing.

Check your PMs. "Notifications" at the top of this page.

[SamT]

If you live in the San Jose area, I'm taking you to lunch! I visit family in Farmington occasionally.

I standard offer is one brand new dollar bill and a certificate of appreciation or a letter of appreciation on company letterhead, suitable for framing.

Check your PMs. "Notifications" at the top of this page.

[MFrancis]

I can do all of the above.
Let me know when you are in the area, and lunch will be on me.
I will send you a PM today sometime with a link to the whole file.
I will also PM you a raving letter of appreciation/recommendation, but it will have to wait until I get through my current deadline.
I will happily either have a crisp brand new dollar bill for you when we go to lunch, or send it to you!

In the meantime, you have given me a lot more things to do, but I am still stuck on my original problem. Can you help me set the range variables?
I.e. RExt and RIn
RExt needs to be set to the current nm in the loop.
Rin needs to be set to whatever RExt is, except with ZIn at the start of the name instead of ZExt.

If you know a better way than using names to correlate the data with each other, I'm all ears. Le me know ASAP, because I still haven't named all the ranges in the other tabs....

Thanks again for everything. REALLY!

[SamT]

I will also PM you a raving letter of appreciation/recommendation, Howm i suppost to frame a
pm?

[SamT]

Can you help me set the range variables?
I.e. RExt and RIn
RExt needs to be set to the current nm in the loop.
Rin needs to be set to whatever RExt is, except with ZIn at the start of the name instead of ZExt.

Names have a RefersTo Property and a RefersToRange Property
IIRC, RefersTo looks like "=A1". study one of the defined Names in the Workbook's Insert>> Name dialog
RefersToRange :dunno:

So I think one of them can be used like
Set Rext = Range(Mid(Nm.RefersTo, 2))
Or like
Set Rext = nm.RefersToRange

for RIn, something like
St RIn = Range(Mid(Names("ZIn" & Mid(nm, 5)).RefersTo, 2)) GAHHH! To many ((())))((()))()()
Set Rin = Names("ZIn" & Mid(nm, 5)).RefersToRange

[MFrancis]

I can do all of the above.
The next time you are in the area, let me know and lunch will be on me.
I will have a crisp brand new dollar bill when we go to lunch, or I am happy to send one wherever!
I will post a PM sometime today with a link to the workbook.
I will also send a PM with a raving appreciation/recommendation letter. That will probably have to wait until after my current deadline. Let me know what you might use it for and I will word it to that advantage.

In the meantime, you have given me a lot of new stuff to do, but I am still stuck on my original problem. Can you help me set the 2 ranges?
I.e. RExt & Rin
Rext will be the current nm in the loop. Name is something like ZExt05.01.742Tb (numbers in the middle change with each loop).
RIn will be the range with the same name as above, except with Zin instead of ZExt at the beginning. If the above example was the current nm in the loop, the name of the RIn range would be ZIN05.01.742Tb.

[MFrancis]

Sorry, I refreshed the page and the post I wrote didn't show up. I thought I hadn't sent it. Then when I sent it again, it showed up along with your responses.
I think that is it!!!!!!!!!! I actually thought refers to might be the problem. I ran across that in some of the searches I made online. I'll give it a shot.

THANKS AGAIN

[MFrancis]

Duh! I didn't notice we are now on to pg 2 in this thread....
Did I interpret your acronym "PM" correctly as meaning "Personal Message"?
If you "PM" me your address, I will take care of the frame problem for you....

[SamT]

So far this attachment has codes to retrieve a particular value, Insert any number of Rows into any Phase, and insert all Phase totals on the Direct sheet.

It still needs a Module "DirectBuilding_Globals", but that would take another hour?.

At this time, all codes are in Modules. Those are safer from Users. Otherwise, there is no major reason not to put some Functions in the Sheets. That is a code engineering decision.

Carefully note Column C on the Direct sheet and the Row numbers above the blue shaded Rows on the Deliverable sheet. Those are the STructural changes I was talking about

Errata:
In Example 2 of the Code Module, this line
Const dbuiPhaseNames As String = "B" 'Belongs in Module "DirectBuilding_Globals"

Should read
Const dbuiPhaseNames As String = "C" 'Belongs in Module "DirectBuilding_Globals"

On further consideration the Prefixes dbud and dbui, should be del and dir respectively. It depends on all the other tabs in the book. It might make sense to change the sheets' Code Names to make it easier to determine appropriate Variable prefixes.

[SamT]

If you are going to do much coding like this, I used UltraEdit (http://www.ultraedit.com/) to create the Globals module in about an hour. It costs only $80, but it's worth it, I've been a user for about 14 years now. You can try it for 30 days free.

Disclaimer: I am not now and never have been associated with the company in any way but as a user.

[SamT]

Sheet Range Names contains a bit of analysis results. The procedures I used to perform the work are in Module SamTs.

I managed to get rid of about 90% of the #NAME errors in the Workbook Cells by adding ErrChecking to, IIRC, just 3 formulas.

To add simple error checking to a formula, use this process

Find the last Precedent in any formula that results in an error.
In that precedent cell change the formula thusly
Copy the Calculation (all after the = sign.)
Before the calculations, insert "IF(ISERR(
After the Calculations, add ",,"
after the ",," paste the calculations you copied
After the pasted calculations, add "))"

That will work in most cases. In one formula that referenced two Names multiple times, I used
IF(OR(ISERR(NAME1),ISERR(NAME2)),,Calculations))

I'm pretty sure I bordered all changed formula cells with a big red border.

IN Sheet Direct Building I introduce a much simpler and elegant way to use formulas against a column of differently related cells. See the new Column F in the attached.

"Warning, Will Robinson! Danger Danger! Danger!"
Do not insert new or delete existing columns or rows into a sheet with formulas! Doing so will destroy all formulas and Names to the right of and below the insertion!

This is very important:
"Warning, Will Robinson! Danger Danger! Danger!"
Do not insert new or delete existing columns or row into a sheet with formulas! Doing so will destroy all formulas and Names to the right of and below the insertion!

"But, Sam. You just said that's what you did. What's the story?"
I'm glad you asked :)

You can safely Cut existing Columns and Rows, then "Insert Cut Cells" (Columns or Rows) all over the place. Just give Excel a moment to catch up each time while it updates everything.

I scrolled way over to the right to an unused column outside the Used Range, "Cut" it, then did an "Insert Cut Cells" before the existing F column. I Named the new column "PhaseNames and used the name in the replacement formulas in (the new) column G. You can hide F without hurting anything.

If you don't like the new Column F where it is, Cut it and Insert it where you like. I notice that a lot of sheets already have Column A hidden.

About the way I Named PhaseNames. Open the Name dialog and scroll down to it. You will notice that to the Right of the Name in the list is the Sheet Name. This means the This particular Name is specific to that sheet. This is handy because many sheets can have identical Sheet specific Names referring to different Ranges.

Formulas on the specific sheet use Names like usual. here is the Formula in 4G form the attached =SUMIF(PhaseNames,B4,G:G). Remember This sheet's PhaseNames refers to column F. You can put a PhaseNames column in another sheet in a different column by Naming that Name
'Another Sheet'!PhaseNames


How do you "automate" this? Say you want to Name all the Phase Totals (G4:G19 in the attached)

Cut G and insert it before C
Select the Phase Name cells(B4:B19) and run this Generic Sub

Sub MakeSheetSpecificNames()
Dim SheetName As String
Dim Cel As Range
With ActiveSheet
SheetName = .Name

For Each Cel In Selection
.Names.Add _
Name:="'" & SheetName & "'!" & Cel.Text, _
RefersTo:="='" & SheetName & "'!" & _
Cel.Offset(, 1).Address(1, 1)
Next
End With
End Sub

Cut C and Insert it before H. All done.

Edit Follows
The OP asked me to remove the attachment