troubleshooting Question

VBA modification help needed from earlier solution by Rgonzo1971 Dynamic Range

Avatar of Flora Edwards
Flora EdwardsFlag for Sweden asked on
Microsoft OfficeMicrosoft ExcelVBA
11 Comments1 Solution220 ViewsLast Modified:
I had this question after viewing VBA modification help needed from earlier solution by Rgonzo1971.


Rgonzo1971 was so kind helping with this great piece of code.

Sub Create_RangeNames1()
     'Creates dynamic named ranges based on header row information only Selection
    Dim wbk As Workbook
    Dim sht As Worksheet
    Dim rng, rng2 As Range
    Dim cl As Range 'Object
    Dim c As Long
    Dim strAddr As Variant
    Dim strShName, strHdrName, strCol As String
    
    Set wbk = ActiveWorkbook
    Set sht = ActiveSheet
    Set rng = Selection
    For c = rng.Column To rng.Column + rng.Columns.Count - 1
        If Cells(1, c).Value <> "" Then
            strShName = Replace(sht.Name, " ", "_", 1)
            strHdrName = Replace(Cells(1, c).Value, " ", "_", 1)
            strCol = Cells(2, c).EntireColumn.Address
            Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))
             wbk.Names.Add Name:=strShName & strHdrName, _
                RefersTo:="=OFFSET('" & sht.Name & "'!" & Cells(2, c).Address & ",0,0,SUMPRODUCT(MAX(('" & sht.Name & "'!" & strCol & "<>"""")*ROW('" & sht.Name & "'!" & strCol & ")))-1,1)"
       End If
    Next
End Sub

lets say from the attached file .  i selected column E and run the above code.  it created named range with this formua.
=OFFSET(Sheet1!$E$2,0,0,SUMPRODUCT(MAX((Sheet1!$E:$E<>"")*ROW(Sheet1!$E:$E)))-1,1)

i need help with modifying this, so that it creates the column dynamic formula  like this.  

so the "ORDER ID" inside Match function should take from column header name

=OFFSET(Sheet1!$A$2,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1,COUNTA(OFFSET(Sheet1!$A:$A,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1))-1,1)
EE.xlsb
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros