Defining Name refering to dynamic range VBA?

[magicbob007]

OK VBA gurus, I have what I believe to be a simple problem here. I am designing a userform that creates dynamic named ranges for Data Validation. This takes the guess work out of it for my end users who are not savvy enough to name a dynamic range on their own.

My problem is that when I get to line 53 of the code and go to assign the "refersto:=" I always wind up with quotation marks in my final refers to formula in the name manager. Consequently my equations are referenced right, but wont work because of the inclusion of extra quotes.

the code as is produces:

="offset(Data!$C$27,0,0,countA(Data!$C$27:$C$1000)-0,1)"

instead of:

=offset(Data!$C$27,0,0,countA(Data!$C$27:$C$1000)-0,1)

This has been frustrating the pants off me for the whole day and everything I have tried doesn't seem to fix the issue. Can you guys tell me how to get this right??

Sub cmdGenoAdd_Click()

Dim NewGeno As String
Dim NewGenoChk As Variant
Dim NewRng  As Variant
Dim CurRow As Long
Dim CurColInt As String
Dim CurCol As String
Dim TopRef As String

    'Promps user for genotype to add
        NewGeno = Application.InputBox( _
                    Prompt:="What is the Name of the genotype you would like to add?", _
                    Title:="Add Genotype", _
                    Left:=(Application.UsableWidth / 2) - (Me.Width / 2), _
                    Top:=(Application.UsableHeight / 2) - (Me.Height / 2), _
                    Type:=2)
                    
        newgenocheck = Left(NewGeno, 1)
        If IsNumeric(newgenocheck) Then
            MsgBox "You cant start your strain with a number...please try again."
        Else
                'Goes into data sheet and ammends the Possible Strains list
                Sheets("DATA").Visible = True
                Sheets("DATA").Select
                If IsEmpty(Range("PosStrains").Offset(1, 0)) Then
                    Range("PosStrains").Offset(1, 0).Select
                    ActiveCell.Value = NewGeno
                Else
                    Range("PosStrains").End(xlDown).Offset(1, 0).Select
                    ActiveCell.Value = NewGeno
                End If
                
                'Starts the Strain's Allele List
                If IsEmpty(Range("PosStrains").Offset(0, 1)) Then
                    Range("PosStrains").Offset(0, 1).Select
                    ActiveCell.Value = NewGeno
                Else
                    Range("PosStrains").End(xlToRight).Offset(0, 1).Select
                    ActiveCell.Value = NewGeno
                End If
                
                'Determines the active row and sets it to CurRow variable
                CurRow = ActiveCell.Row
                'Determines the active col and sets it to CurCol variable
                CurCol = Mid(ActiveCell.Address, InStr(ActiveCell.Address, "$") + 1, InStr(2, ActiveCell.Address, "$") - 2)
                'Sets TopRef as current col and current row+1
                TopRef = "$" & CurCol & "$" & CurRow + 1
                
                CurColInt = CStr(CurCol)
                NewRng = "offset(Data!" & TopRef & ",0,0,countA(Data!" & TopRef & ":$" & CurColInt & "$1000)-0,1)"
                ActiveWorkbook.Names.Add Name:=NewGeno & "Genotypes", RefersTo:=NewRng
        End If
                     
End Sub

[Richard Buttrey]

Hi,

You're missing the =

i.e.
NewRng = "=offset(Data!" & TopRef & ",0,0,countA(Data!" & TopRef & ":$" & CurColInt & "$1000)-0,1)"

[magicbob007]

I had it in there before, but it wasn't working. There must have been something else wrong also because with the = it works perfect now. Thanks. I knew it would be something simple I was just over looking.