I can't access a range created by VBA code

[TotallyConfused]

The number of columns in the following data file shouldn’t change, however, there will be a new row of data added at the bottom every day. For this reason, I can’t manually create a range name that can be used with some Excel formulas such as ‘DSUM’ and perhaps others.

The code I have works great creating a range which I’ve called ‘Rng’ and places the address in a cell I’ve called ‘RngLoc’. In this case, RngLoc shows $A$1:$E$9. My problem is that when I go to Excel and try to enter the formula of =DSUM(RngLoc,5,J13:K14) all I get is the error message of ‘#VALUE!’ with a comment of ‘A value used in the formula is of the wrong data type’. I know my formula, my criteria and the data are okay, because if I enter the formula =DSUM(A1:E9,5,J13:K14) everything works great, so I’m assuming the problem is with the RngLoc.

I want to offer my THANKS in advance for any assistance you may be able to offer. I’m hoping to pick the brain of some of you experts to gain an answer to my problem.

	A	B	C	D	E
1	CODE	DNUM	MNUM	YNUM	P1
2	42736	1	1	2017	$0.00
3	5	6	7	1254	$999.00
4	42736	1	1	2017	$0.00
5	42736	1	3	2017	$0.00
6	42736	1	1	2017	$2.00
7	42736	1	2	2017	$3.00
8	42736	1	1	2017	$0.00
9	5	6	7	2017	$999.00

<tbody>
</tbody>

Public Sub CreateRangeName()     'Create and names a range as 'Rng'
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim Rng As Range                        'Name of range created by this subroutine
Dim RngLoc As Range                   ‘Cell location of range
With ws1
   .Cells(1, 1).Select                       'Cursor at cell A1 and make it the ActiveCell
                'The following line of code creates the range and names it 'Rng'
   Set Rng = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row_
   ,ActiveCell.End(xlToRight).Column))
   .Range("RngLoc") = Rng.Address   ‘Transfer address to Excel variable
End With                                           ‘End With ws1
End Sub

 

[footoo]

=DSUM(INDIRECT(RngLoc),5,J13:K14)

 

[ukmikeb]

Hi

It won't work because you are naming the range within vba and not the workbook.
And @footoo's solution won't work for the same reason.

You need to replace :-

.Range("Rngloc")=.....

with

ActiveWorkbook.Names.aDD Name:="Rngloc", RefersTo:="=" & Rng.Address

Then you can refer to Rngloc in the DSUM formula without the INDIRECT.

hth

 

[footoo]

Hi

It won't work because you are naming the range within vba and not the workbook.
And @footoo's solution won't work for the same reason.

The OP states "places the address in a cell I’ve called ‘RngLoc’. In this case, RngLoc shows $A$1:$E$9"
Based on this and the code posted (.Range("RngLoc") = Rng.Address ), is it not reasonable to assume there is a cell that has been named RngLoc ?

 

[TotallyConfused]

Hi Footoo and Ukmikeb.

I want to THANK both of you for the time and thought you put into working on my problem. You have no idea how long I’ve struggled with this and how many different ideas I’ve tried, even ones I was sure wouldn’t work, I tried anyway just in case they did. I bet I know 50 different ways it won’t work. Sorry I didn’t get back here earlier but family problems have kept me busy.

Ukmikeb: Your solution worked like a charm. THANK YOU! After experimenting with your solution, I’ve discovered I don’t need the variable of RngLoc, but can simply keep the original one created by ‘Set Rng = …’ and pass the Rng to Excel. The only reason I was using the RngLoc was that I was afraid if I used Rng in both VBA and Excel, it might create some kind of conflict. You have not only provided a solution to my immediate problem, but also shown me how to pass a range from VBA to Excel and have it also show up in FILE > NAME MANAGER. I knew my method wasn’t doing that, but I couldn’t figure out how to make it happen.

Footoo: I think you and I were both thinking along the same line. As you mentioned, if I could get the address to show up in Excel, then DSUM should be able to access that because that would be similar to me actually typing the address into DSUM. It seems the problem was that nothing showed up in NAME MANAGER, so DSUM couldn’t find it. I’m assuming that was the reason. I even tried naming Cell ‘A1’ as RNG and RngLoc thinking VBA would take that as a starting point and expand the range to the required number of rows and columns.

THANKS again to the both of you. I see there is a new feature added where we can click on ‘Thank you for posting this’ and ‘I like this post’. I’m going to check both of these. I hope this helps you.

 

[Akuini]

The number of columns in the following data file shouldn’t change, however, there will be a new row of data added at the bottom every day. For this reason, I can’t manually create a range name that can be used with some Excel formulas such as ‘DSUM’ and perhaps others.

If you’re talking about dynamic named range actually it can be done.
Check this link:
How to create dynamic named range in Excel?

 

[footoo]

@TotallyConfused

Just for info, if you want to have a macro to put a range address into a named worksheet cell and use that address in worksheet formulas (like you were originally trying to do) :

- Assign a Name to a worksheet cell (let's say, cell A1 named RngLoc
- In a macro : Range("RngLoc")=RangeVariable.Address
- Worksheet formula, for example) : =SUM(INDIRECT(RngLoc))

 

[TotallyConfused]

If you’re talking about dynamic named range actually it can be done.
Check this link:
How to create dynamic named range in Excel?

Hello Akuini
THANK YOU for posting the link to that site. I’ve only had a chance to look at part of it yet, but there seems to be a tremendous amount of wonderful information there. As time allows, I’m going back there and see what else I can learn.

 

[TotallyConfused]

@TotallyConfused

Just for info, if you want to have a macro to put a range address into a named worksheet cell and use that address in worksheet formulas (like you were originally trying to do) :

- Assign a Name to a worksheet cell (let's say, cell A1 named RngLoc
- In a macro : Range("RngLoc")=RangeVariable.Address
- Worksheet formula, for example) : =SUM(INDIRECT(RngLoc))

Hello Footoo
I want to THANK YOU again for your effort to solve my problem and continued interest in this situation. It seems very reasonable that there should be some way to make this work as you described since I had transferred the address. I couldn’t get the =Dsum(Indirect(RngLoc)) to work, however, I had made so many changes to what I had in Excel and also to my macro, there is no telling what I had at that moment. When I get a little time to play with this, I’m going to set up a new sheet and macro and see what I can make happen. Hey, if nothing else, I’ll learn a few more ways it won’t work. I seem to have a talent for finding those methods.

The thought that now occurs to me, if we can create a range in Excel using VBA and transfer data to that range, is it possible to use VBA to delete the range from Excel when we are finished using it?