Dynamic range created vrom VBA

[white_flag]

Hello

I have two dynamic ranges defined via VBA. But like this it is not going.

Option Explicit
Public Sub list_name()
    ActiveWorkbook.Names.Add Name:="name1", RefersTo:="=OFFSET(data!$B$3,0,0,COUNTA(data!$B3:$B20),1)"
    ActiveWorkbook.Names.Add Name:="name2", RefersTo:="=OFFSET(data!$B$26,0,0,COUNTA(data!$B26:$B38),1)"
       With Sheets("Calculation").Range("C6").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=name1"
            .IgnoreBlank = True
            .InCellDropdown = True

       End With
       With Sheets("Calculation").Range("C7").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=name2"
            .IgnoreBlank = True
            .InCellDropdown = True

       End With

End Sub

Then I put just one range "name1" and in excel the dynamic range become:
=OFFSET(data!$B$3,0,0,COUNTA(data!$B28:$B45),1) insted
=OFFSET(data!$B$3,0,0,COUNTA(data!$B3:$B20),1)

Option Explicit
Public Sub list_name()
    ActiveWorkbook.Names.Add Name:="name1", RefersTo:="=OFFSET(data!$B$3,0,0,COUNTA(data!$B3:$B20),1)"
       With Sheets("Calculation").Range("C6").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=name1"
            .IgnoreBlank = True
            .InCellDropdown = True

       End With

End Sub

so what it is wrong?

 

[GlennUK]

Your definition has relative references in it ... so the definition depends on what is the active cell when the definition is created. Either select an appropriate cell or change the definition.

 

[white_flag]

I add this line on VBA but it is not going:

    Worksheets("data").Activate
    ActiveWorkbook.Names.Add Name:="name1", RefersTo:="=OFFSET(data!$B$3,0,0,COUNTA(data!$B3:$B20),1)"
    ActiveWorkbook.Names.Add Name:="name2", RefersTo:="=OFFSET(data!$C$26,0,0,COUNTA(data!$C26:$C38),1)"

 

[GlennUK]

You have not done either of my suggestions ... and you seem surprised that it is still not working. What would you have me suggest instead?????

 

[white_flag]

sorry. but my level of VBA is not so fantastic.

set active cell:

Worksheets("data").Range("B1").Activate

then will follow the definition ...

the offset are created correct now. but the second part it is with error

Public Sub list_name()
    Worksheets("data").Range("B1").Activate
    ActiveWorkbook.Names.Add Name:="name1", RefersTo:="=OFFSET(data!$B$3,0,0,COUNTA(data!$B3:$B20),1)"
    ActiveWorkbook.Names.Add Name:="name2", RefersTo:="=OFFSET(data!$C$26,0,0,COUNTA(data!$C26:$C38),1)"
       With Sheets("Calculation").Range("C6").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=name1"
            .IgnoreBlank = True
            .InCellDropdown = True
       End With
'       With Sheets("Calculation").Range("C7").Validation
'            .Delete
'            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
'                xlBetween, Formula1:="=name2"
'            .IgnoreBlank = True
'            .InCellDropdown = True
'       End With

End Sub

so I can not insert in C7 an list

 

[GlennUK]

You may have added the names correctly, but I still don't think that you realise what having relative references means. I recommend that you change your references to be absolute ( data!$B$3:$B$20 and data!$C$26:$C$38 ).

 

[white_flag]

ups ..my bad (I did not see). but for the second question:

'       With Sheets("Calculation").Range("C7").Validation
'            .Delete
'            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
'                xlBetween, Formula1:="=name2"
'            .IgnoreBlank = True
'            .InCellDropdown = True
'       End With

I recive an error 1004

 

[GlennUK]

Try activating the Calculation sheet before that block of code.

 

[white_flag]

nice done..thank you