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
Bookmarks