Hi,

So I have a document where I want the user to be able to create a new named range simply by Typing in the column letter into a userform. Problem I'm having is the refersto part of defining the name.


Private Sub CommandButton1_Click()

Dim sel As String
Dim nrCol As String
Dim nrHead As String
Dim nrHeadval As String

sel = TextBox1.Value

nrCol = sel & ":" & sel
nrHead = sel & "1"
nrHeadval = Range(nrHead).Value

Columns(nrCol).Select
    ActiveWorkbook.Names.Add Name:=nrHeadval, RefersToR1C1:= _
        "=OFFSET(Named_Ranges!R2C1,0,0,COUNTA(Named_Ranges!C1,1)-1)" ' On this line I'm not sure how to work out the Column Number and change from C1 to whatever the selection C# is.
    ActiveWorkbook.Names(nrHeadval).Comment = ""
    Range(nrHead).Select
    
Unload UserForm3

End Sub
So this will work if A was selected in the userform, but if B for example was chosen then I need the C1 part to change to C2.

Any help is greatly appreciated.

Thanks

Dave