VBA modification help needed from earlier solution by Rgonzo1971 Dynamic Range

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

Open in new window


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
LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try 
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 & "'!$A$2,0,MATCH(" & Cells(1, c).Value & ",'" & sht.Name & "'!$1:$1,0)-1,COUNTA(OFFSET(Sheet1!$A:$A,0,MATCH(" & Cells(1, c).Value & ",'" & sht.Name & "'!$1:$1,0)-1))-1,1)"
       End If
    Next
End Sub

Open in new window

Regards
0
FloraAuthor Commented:
thank you very much.

sorry if i confused you with my poor explanation.  i am trying to avoid use of COUNTA  instead i want to use, the one with sumproduct.

SUMPRODUCT(MAX((OFFSET(Sheet1!$A:$A,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1)<>"")*ROW(OFFSET(Sheet1!$A:$A,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1))))-
0
FloraAuthor Commented:
sorry the comlete formula should be =OFFSET(Sheet1!$A$2,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1,SUMPRODUCT(MAX((OFFSET(Sheet1!$A:$A,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1)<>"")*ROW(OFFSET(Sheet1!$A:$A,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1))))-1,1)   which will be generated from  VBA

also the "ORDER ID" inside Match function should take from column header name
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
Rgonzo1971Commented:
then try
 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 & "!$A$2,0,MATCH(" & Cells(1, c) & "," & sht.Name & "!$1:$1,0)-1,SUMPRODUCT(MAX((OFFSET(" & sht.Name & "!$A:$A,0,MATCH(" & Cells(1, c) & "," & sht.Name & "!$1:$1,0)-1)<>"""")*ROW(OFFSET(" & sht.Name & "!$A:$A,0,MATCH(" & Cells(1, c) & "," & sht.Name & "!$1:$1,0)-1))))-1,1)"
                
       End If
    Next
End Sub

Open in new window

1
FloraAuthor Commented:
thanks very much.

it works. but it puts the formula as this .  so the header name inside the Match is not within qoutation marks " "  that is why it is not recognized.

=OFFSET(Sheet1!$A$2,0,MATCH(Product Name,Sheet1!$1:$1,0)-1,SUMPRODUCT(MAX((OFFSET(Sheet1!$A:$A,0,MATCH(Product Name,Sheet1!$1:$1,0)-1)<>"")*ROW(OFFSET(Sheet1!$A:$A,0,MATCH(Product Name,Sheet1!$1:$1,0)-1))))-1,1)
0
FloraAuthor Commented:
so if it generates like this =OFFSET(Sheet1!$A$2,0,MATCH("Product Name",Sheet1!$1:$1,0)-1,SUMPRODUCT(MAX((OFFSET(Sheet1!$A:$A,0,MATCH("Product Name",Sheet1!$1:$1,0)-1)<>"")*ROW(OFFSET(Sheet1!$A:$A,0,MATCH("Product Name",Sheet1!$1:$1,0)-1))))-1,1)

then it works. i do not know how to put the " " around the Cells(1, c)
0
Rgonzo1971Commented:
then try
 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 & "!$A$2,0,MATCH(""" & Cells(1, c) & """," & sht.Name & "!$1:$1,0)-1,SUMPRODUCT(MAX((OFFSET(" & sht.Name & "!$A:$A,0,MATCH(""" & Cells(1, c) & """," & sht.Name & "!$1:$1,0)-1)<>"""")*ROW(OFFSET(" & sht.Name & "!$A:$A,0,MATCH(""" & Cells(1, c) & """," & sht.Name & "!$1:$1,0)-1))))-1,1)"
                
       End If
    Next
End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FloraAuthor Commented:
This is by far one of the greatest solutions received from you.  Thanks a Billion!  very much appreciated.
0
FloraAuthor Commented:
you are amazing! Rgonzo1971
0
FloraAuthor Commented:
Thank you so much. Thanks very very much.
0
FloraAuthor Commented:
dear Rgonzo1971

you have written this line of code  Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))  

but i have not seen where did you use it, in the following lines of code.   can u please tell me why you created this rng2?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.