Using refedit in named range

[impius]

Hello...I am trying to use the refedit control in a named range. The code below keeps giving me a type mismatch error. Any help would be appreciated!




Dim x As Range
Set x = Range(RefEdit1)

ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
"=OFFSET(" & x & ",COUNTA(Sheet1!C1)-10,0,10)"

[p45cal]

x is a range (you defined it as such) yet you're using it in a string. Either use
x.address instead (you may have to use x.Address(ReferenceStyle:=xlR1C1) as you've used RefersToR1C1)
or
don't Dim x as a range, just Dim x and use x=RefEdit1, and hope that the user has entered a R1C1-type reference in the RefEdit control... which brings up more questions: is the refedit control an activex control on a sheet or on a userform? Is the worksheet set up showing as A1 style or R1C1 style? Is the code in a worksheet's code module or a standard module or a userform's code module?

FYI, I couldn't get the refedit control to allow the user to select cells with the mouse when it was on a worksheet, I didn't find a 'Forms' version of the refedit control, but you might consider using the InputBox (be careful, there are two types: Excel and VBA, and I can't remember whiich is the best to use in these circumstances!))

I was able to create valid names by using both solutions above, but some more information about where things are would be helpful to nail this one down.

[impius]

Thanks p45cal, unfortunately I am still unable to get it to work...

Dim x
x = RefEdit1

ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
"=OFFSET(" & x.Address & ",COUNTA(Sheet1!C1)-10,0,10)"

I am getting an object required error on the last line of the code above. To answer your questions:

1. The refedit control is on a userform
2. I am not sure what you mean set up as R1C1 style?
3. The code is in a userform button


Thanks again for your help!

Joe

[mdmackillop]

ActiveWorkbook.Names.Add Name:="Date", RefersTo:= _
"=OFFSET(" & RefEdit1 & ",COUNTA(Sheet1!A:A)-10,0,10)"

[impius]

Thanks all,

This solved one problem, but now I see another...It is now putting quotes (" ") around the range that is selected when defining the name. So:

ActiveWorkbook.Names.Add Name:="Date", RefersTo:= _
RefEdit1

gives the named range "Date" whatever was selected in the RefEdit control but within quotes. For example, as a test, I selected column A in my refedit box and it put "sheet1!A:A" as the named range range rather than sheet1!A:A. Anyone know how to avoind this?

[p45cal]

I suggested two options, you've tried 'em both at the same time! It was either:
Dim x
x = RefEdit1
ActiveWorkbook.Names.Add Name:="Date", RefersTo:= _
"=OFFSET(" & x & ",COUNTA(Sheet1!$A:$A)-10,0,10)"
OR:
Dim x As Range
Set x = Range(RefEdit1)
ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:= _
"=OFFSET(" & x.Address(ReferenceStyle:=xlR1C1) & ",COUNTA(Sheet1!C1)-10,0,10)"
R1C1 style is a way of referring to ranges on the sheet, as opposed to A1 notation. R1C1 uses numbers as column headers instead of letters. Your code had RefersToR1C1:= in it implying you were using R1C1 style/notation, so I stuck with it. You can see what a sheet looks like when setup in R1C1 style by going to the drop down menus in Excel and choosing:
Tools|Options..., then going to the General tab and checking/unchecking the R1C1 reference style checkbox.

Sticking with the more usual, and default, A1 notation, the second snippet of code above could have been written:
Dim x As Range
Set x = Range(RefEdit1)
ActiveWorkbook.Names.Add Name:="Date", RefersTo:= _
"=OFFSET(" & x.Address & ",COUNTA(Sheet1!$A:$A)-10,0,10)"Note how instead of Sheet1!C1 to refer to the whole of column 1 it uses Sheet1!$A:$A to do so; note also how RefersToR1C1:= changes to RefersTo:=.
Had we used RefersTo:= with Sheet1!C1, it would be referring to cell C1 rather than Column1, so they shouldn't be mixed up!

As an aside, it might be an idea to name the range something other than Date as Date returns the current date in vba, eg. rngDate or some such.

[impius]

Ohhh....Thanks P45Cal, it works and I gained some knowledge!

Thanks again!