Need help converting offset formula for dynamic range in vba

[michaelsmith559]

Here is the current macro:

Sub Insert_Dynamic_Range()
Dim myText As String, myRange As Range, lastRow As Long

myText = Application.InputBox("Enter a Name for Range", Type:=2)
Set myRange = Application.InputBox("Select Where To Put Name", Type:=8)
myRange = myText

With ActiveSheet
lastRow = Cells(Rows.Count, myRange.Column).End(xlUp).Row
    ActiveSheet.Names.Add Name:=myText, _
    RefersTo:="OFFSET(myRange.Offset(1,0).Address,0,0,MATCH(1E+306,myRange.Column:myRange.Column),1)"
End With

End Sub

Here is sheet 1:

Excel 2012
	A	B
1
2	5
3	6
4	7
5	3
6	2
7	1
8	8
9	9
Sheet1

Here is sheet 1 after I run macro:

Excel 2012
	A	B
1	Prices	#VALUE!
2	5
3	6
4	7
5	3
6	2
7	1
8	8
9	9
Sheet1

Cell Formulas
Range	Formula
B1	=SUM(Prices)

Named Ranges
Name	Refers To	Cells
Sheet1!Prices	="OFFSET(myRange.Offset(1,0).Address,0,0,MATCH(1E+306,myRange.Column:myRange.Column),1)"

I am trying to replace this formula:
Offset($A$2,0,0,Match(1E+306,$A:$A),1)

with something like this:
OFFSET(myRange.Offset(1,0).Address,0,0,MATCH(1E+306,myRange.Column:myRange.Column),1)

 

[Jeffrey Mahoney]

Let me help. First, myrange is set as a range from input. This: myRange = myText is only going to make the cells fill up with the text from mytext. myrange is only a temporary range you created in the sub. If you create a dynamic range, the Offset function needs either an actual named range or a cell address.

I don't know what you're trying to do with the 1E+306?

When I create dynamic named ranges, I use an anchor for the OFFSET function to work. Example:
=OFFSET(Top_hdr,1,0,COUNTA(OFFSET(Top_hdr,1,0,1000000,1)),1)
This uses the named range "Top_hdr" as the anchor. It counts the number of cells below "Top_hdr" that actual contain a non-blank cell to set the rows that are in the dynamic named range. I usually name my dynamic named range the same as my anchor so I can use the GOTO (F5) to get to it. In this case I would name the dynamic named range "Top_list".

Maybe you want to have the user (yourself most likely) select the range with the values, then have the macro create a header for it from "myText". So myText would be the name of the anchor and the text you place above the range. If myText contains "BarneyRubble", the header would be called "BarneyRubble_hdr" and the dynamic named range would be called "BarneyRubble_List" or "BarneyRubble_Tbl" or "BarneyRubble_rng".

Sub Insert_Dynamic_Range()
  Dim myText As String
  Dim myRange As Range
  Dim Cel As Range
  Dim hdrRange As Range
  Dim hdrText As String
  Dim dText As String
  
  myText = Application.InputBox("Enter a Name for Range", Type:=2)
  Set myRange = Application.InputBox("Select Where To Put Name", Type:=8)
  For Each Cel In myRange
    Set hdrRange = Cel.Offset(-1, 0)
    hdrRange = myText
    Exit For
  Next Cel
  hdrText = myText & "_hdr"
  dText = myText & "_List"
  
  With ActiveSheet
    ActiveSheet.Names.Add Name:=hdrText, RefersTo:="=" & hdrRange.Address
    
    ActiveSheet.Names.Add Name:=dText, _
    RefersTo:="=OFFSET(" & hdrText & ",1,0,COUNTA(OFFSET(" & hdrText & ",1,0,1000000,1)),1)"
End With

End Sub

 

[Jeffrey Mahoney]

If you explain why you're trying to match a number that is 1 followed by 306 zeros, I'll try to incorporate it into your dynamic range.

 

[michaelsmith559]

The line: ActiveSheet.Names.Add Name:=myText, is the name for the named range. The macro I posted works except for the RefersTo line. If the macro I posted is ran and you check under formulas, defined name you will see the name Prices and in the refers to line you will see my failed modified formula. In the formula: Offset($A$2,0,0,Match(1E+306,$A:$A),1) I am trying to replace the $A$2 with something like myRange.Offset(1, 0).Address; and the $A:$A with something like myRange.EntireColumn. The 1E+306 deals with blanks that may be in the column. When I select a cell to input the name, I am wanting the Offset formula to reference that column. Again, instead of me typing $a$2, I would like something more universal, same with $a:$a. Where you mentioned: the Offset function needs either an actual named range or a cell address, I am trying to reference a cell address. I have tried: =Offset(myRange.Offset(1, 0).Address,0,0,etc. but it fails. It will not recognize .Address. I will try your macro momentarily to see if it will work for my needs. Appreciate the help.

Mike

 

[michaelsmith559]

Just tried your macro, and it does not work. It errors on: Set hdrRange = Cel.Offset(-1, 0). Another way to look at what I am trying to do is instead of me typing Offset($A$2,etc.) I am trying to make a reference to $A$2 by using the myRange.Address and offsetting the address by 1 row. I have tried declaring a variable and setting it; for example: i = myRange.Offset(1, 0).Address. Holding the mouse over Address shows $A$2 like I want, but the Address is not stored in i. I have also tried: i = Address(myRange).Offset(1, 0). It fails as well.

 

[Jeffrey Mahoney]

I could be wrong on this statement, but I think the OFFSET function needs a single cell as reference. myRange.address nor myRange.offs(1,0).address will work because myRange is not a single cell.

I just noticed this: RefersTo:="OFFSET(myRange.Offset(1,0).Address,0,0,MATCH(1E+306,myRange.Column:myRange.Column),1)"
Needs to be this: RefersTo:="=OFFSET(myRange.Offset(1,0).Address,0,0,MATCH(1E+306,myRange.Column:myRange.Column),1)"


I see your use of 1E+306 now. MATCH defaults to search for values less than the value given. That works if the range is filled with numbers but not with text.

 

[Jeffrey Mahoney]

Reply from post #6. That part of the macro needs to have a blank cell above your selected range. If the range you select is starting at row 1 then it will error

 

[Jeffrey Mahoney]

OFFSET does work with a range that only includes one column but not more than one column. If provided a range such as =OFFSET(A8:A9,1,0) it would return the value in A10.

Did you see how I got the top right cell in the range?

For Each Cel In myRange
    Set hdrRange = Cel.Offset(-1, 0)
    hdrRange = myText
    Exit For
  Next Cel

You could use that instead of myRange in the RefersTo statement

 

[michaelsmith559]

I used part of your other code and some of your explanations to figure most of this out. I still need help removing the outer quotes so vba will accept it. Here is the macro so far, it is correct except for the outer quotes:

Sub Insert_Dynamic_Range()
Dim myText As String, myRange As Range, i As Variant, j As Variant

With ActiveSheet

myText = Application.InputBox("Enter a Name for Range", Type:=2)
Set myRange = Application.InputBox("Select Where To Put Name", Type:=8)

i = myRange.Offset(1, 0).Address
j = Split(i, "$")(1)

ActiveSheet.Names.Add Name:=myText, _
    RefersTo:="Offset(" & i & ", 0, 0, Match(1E+306, " & "$" & j & ":" & "$" & j & "), 1)"

End With

End Sub

 

[Jeffrey Mahoney]

When I run your code as I changed it below, select range B1:B6, I get this as the formula: =OFFSET(Sheet2!$B$2:$B$7, 0, 0, MATCH(1E+306,Sheet2!$B:$B ), 1)

If I add numbers to the set, It always selects a blank cell below the set.

Sub Insert_Dynamic_Range2()
  Dim myText As String, myRange As Range, i As Variant, j As Variant
  
 
  
  myText = Application.InputBox("Enter a Name for Range", Type:=2)
  Set myRange = Application.InputBox("Select Where To Put Name", Type:=8)
  If myRange Is Nothing Then Exit Sub
  
  i = myRange.Offset(1, 0).Address
  j = Split(i, "$")(1)
  
  ActiveSheet.Names.Add Name:=myText, _
      RefersTo:="=Offset(" & i & ", 0, 0, Match(1E+306, " & "$" & j & ":" & "$" & j & "), 1)"
  
 

End Sub