Flora Edwards
asked on
VBA modification help needed to revise the formula
I have this best code. also in the attached workbook.
if you select the column A and then run this macro. it will add a dynamic named range =OFFSET(Sheet1!$A$1,0,0,CO UNTA(Sheet 1!$A:$A),1 )
i need help with modification of VBA code that instead of creating =OFFSET(Sheet1!$A$1,0,0,CO UNTA(Sheet 1!$A:$A),1 ) I need the VBA to Create this =OFFSET(Sheet1!$A$2,0,0,SU MPRODUCT(M AX((Sheet1 !$A:$A<>"" )*ROW(Shee t1!$A:$A)) )-1,1)
how can this code below be modified?
if you select the column A and then run this macro. it will add a dynamic named range =OFFSET(Sheet1!$A$1,0,0,CO
i need help with modification of VBA code that instead of creating =OFFSET(Sheet1!$A$1,0,0,CO
how can this code below be modified?
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 = 1 To rng.Columns.Count
If Cells(1, c).Value <> "" Then
strShName = Replace(sht.Name, " ", "_", 1)
strHdrName = Replace(Cells(1, c).Value, " ", "_", 1)
strAddr = Split(Cells(1, c).Address, "$")
strCol = "$" & strAddr(1) & ":$" & strAddr(1)
Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))
wbk.Names.Add Name:=strShName & strHdrName, _
RefersTo:="=OFFSET('" & sht.Name & "'!" & Cells(1, c).Address & ",0,0,COUNTA('" & _
sht.Name & "'!" & strCol & "),1)"
End If
Next
End Sub
EE.xlsb
ASKER
Thanks Rgonzo.
your code creates the same thing which is =OFFSET(Sheet1!$A$1,0,0,CO UNTA(Sheet 1!$A:$A),1 )
i needed the code to create =OFFSET(Sheet1!$A$2,0,0,SU MPRODUCT(M AX((Sheet1 !$A:$A<>"" )*ROW(Shee t1!$A:$A)) )-1,1) instead
your code creates the same thing which is =OFFSET(Sheet1!$A$1,0,0,CO
i needed the code to create =OFFSET(Sheet1!$A$2,0,0,SU
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks A Million! very much appreciated Rgonzo1971
ASKER
i posted a question. no one answered https://www.experts-exchange.com/questions/29068100/modification-of-formula-as-i-get-another-problem.html
pls try
Open in new window
EDIT corrected codeRegards