Excel VBA create a Named range from formula in wor

Question

Hello I have a problem with some of my VBA code.

I am making a multi dynamic table that helps me retain control of my program. So when I adjust something in the table I can just use this code to update all my code.

In the table I use this code to locate my needed data, and so far it worked great.

Source_1_Criteria = "Factuur"
Source_1 = Range("MDM_MDM_Tool_List").Find(what:=Source_1_Criteria).Offset(0, 1).Value

Me.ListBox1.RowSource = Source_1

And now I wanted to use the same code with a bit extra to create a named range. This works when I use a cell with just some text, but when I fill the cell with a formula VBA throws me a error.

Source_1_Criteria = "Factuur"
Source_1_Name = Range("MDM_MDM_Tool_List").Find(what:=Source_1_Criteria).Offset(0, 2).Value
Source_1_Area = Range("MDM_MDM_Tool_List").Find(what:=Source_1_Criteria).Offset(0, 4).Value

ActiveWorkbook.Names.Add Name:=Source_1_Name, RefersTo:=Source_1_Area

The contents of the range Source_1_Area is

=VERSCHUIVING(archief!$A$2;0;0;1;AANTALARG(archief!$A$3:$Y$3))

The Code i use does work when i place =archief!$A$2 in the contents

Why dus my bigger formula not work?

Answer 1

This will use the formula you gave in the named range

Sub Test()

    AllocateNamedRange ThisWorkbook, "SomeName", "=VERSCHUIVING(archief!$A$2;0;0;1;AANTALARG(archief!$A$3:$Y$3))", "A1"

    'English version:
    'AllocateNamedRange ThisWorkbook, "SomeName", "=OFFSET(archief!$A$2,0,0,1,COUNTA(archief!$A$3:$Y$3))", "A1"

End Sub

Sub Test2()

    'Using some of your code to find the name.
    Dim rFoundRange As Range

    With ThisWorkbook.Worksheets("Sheet1").Range("MDM_MDM_Tool_List")
        Set rFoundRange = .Find(what:=Source_1_Criteria)

        'If Source_1_Criteria isn't found it will throw an error so need to check if it's found first.
        If Not rFoundRange Is Nothing Then
            AllocateNamedRange ThisWorkbook, rFoundRange.Offset(0, 2).Value, _
                "=VERSCHUIVING(archief!$A$2;0;0;1;AANTALARG(archief!$A$3:$Y$3))", "A1"
        End If

    End With

End Sub

'---------------------------------------------------------------------------------------
' Procedure : AllocateNamedRange
' Purpose   : Deletes the named range if it already exists and then recreates it.
'---------------------------------------------------------------------------------------
Public Sub AllocateNamedRange(Book As Workbook, sName As String, sRefersTo As String, Optional ReferType = "R1C1")
    With Book
        If NamedRangeExists(Book, sName) Then .Names(sName).Delete
            If ReferType = "R1C1" Then
                .Names.Add Name:=sName, RefersToR1C1:=sRefersTo
        ElseIf ReferType = "A1" Then
                .Names.Add Name:=sName, RefersTo:=sRefersTo
        End If
    End With
End Sub

'---------------------------------------------------------------------------------------
' Procedure : NamedRangeExists
' Purpose   : Checks if a named range exists.  Returns TRUE or FALSE.
'---------------------------------------------------------------------------------------
Public Function NamedRangeExists(Book As Workbook, sName As String) As Boolean
    On Error Resume Next
        NamedRangeExists = Book.Names(sName).Index <> (Err.Number = 0)
    On Error GoTo 0
End Function

You could just hardcode the formula into a named range and it will automatically adjust as you add new data (basically what my code does).