0

The below shortened code section fails to work out. When I attempt to define the range the name "tablename" refers to, I obviously commit some kind of mistake in this line of the code:

Names.Add Name:=FullRangeName, RefersTo:=wsNum.Range(Cells(3, 1), Cells(lentgth, width))

The cells of the reference range are located in different sheets. Can that be the root cause?

The code stops running at the above line. (The subsequent message box is substituting further code content.)

The script looks like:

Sub ggfgfd()
    Dim wsCtrl As Worksheet
    Dim wsNum As Worksheet

    Set wsCtrl = Workbooks("trez.xlsm").Sheets("ltto")
    Set wsNum = Workbooks("trez.xlsm").Sheets("figures")

    If wsCtrl Is Nothing Then
        MsgBox " blabla"
        Exit Sub
    End If
    wsNum.Activate
    wsCtrl.Activate

    Dim width As Byte
    width = wsCtrl.Cells(1, 3).Value

    Dim length As Byte
    length = wsCtrl.Cells(2, 3).Value

    Dim FullRangeName As String
    FullRangeName = "tablename"
    Names.Add Name:=FullRangeName, RefersTo:=wsNum.Range(Cells(3, 1), Cells(lentgth, width))
    MsgBox FullRangeName.Value
End Sub

I've tried to replace the cell references width, length with fix numbers, but that doesn't help either. Actually even this code has already worked out until I separated the code into two Excel sheets, therefore my guess was, that the source definition in RefersTo:= .... has an incorrect syntax.

Thanks for your efforts.

GSerg
  • 76,472
  • 17
  • 159
  • 346
A Pofta Tapofta
  • 5
  • 4
  • `The cells of the reference range are located in different sheets` - how can that be? – GSerg Mar 12 '23 at 17:55
  • in the faulty line quoted above the cell reference Cells(lentgh, width) originates from wsCtrl, whilst Cells(3,1) sits in wsNum. – A Pofta Tapofta Mar 12 '23 at 18:34
  • No, they both originate from `ActiveSheet` like the link above explains. In your case, `ActiveSheet` is `wsCtrl`. – GSerg Mar 12 '23 at 18:51
  • Taking the syntax from the link above I have reformulated my script as – A Pofta Tapofta Mar 12 '23 at 18:56
  • . Having taken the syntax as example from the link above I have reformulated my script as ..... Names.Add Name:=FullRangeName, RefersTo:=wsNum.Range(wsNum.Cells(3, 1), wsCtrl.Cells(lentgth, width)) ......... I still get the same error message though. – A Pofta Tapofta Mar 12 '23 at 19:07
  • Now you are explicitly asking for a `Range` that starts on one sheet and ends on another. How can a single `Range` be that? – GSerg Mar 12 '23 at 19:10
  • ... also I'm not sure what that means: " to qualify a range or cells" – A Pofta Tapofta Mar 12 '23 at 19:11
  • It means "to prepend the sheet designator", which you have done successfully by prepending the `wsNum.` and `wsCtrl.` to the two `Cells()` calls. Now you are explicitly asking for a Range that cannot be. You can ask for `wsNum.Range(wsNum.Cells(3, 1), wsNum.Cells(lentgth, width))`, you can ask for `wsCtrl.Range(wsCtrl.Cells(3, 1), wsCtrl.Cells(lentgth, width))`, but you cannot ask for a mix. – GSerg Mar 12 '23 at 19:17
  • Thanks GSerg, I admit that it is a hogwash to determine a range across diverse sheets. I now understand how and why I 've committed mistake on the one hand. I still have to work on this to find a working solution, as the post on the link does not seem to offer solution to me. – A Pofta Tapofta Mar 12 '23 at 19:22
  • If your problem is "to define a range across sheets", then there is no solution. – GSerg Mar 12 '23 at 19:23
  • Just realized, that my concept about ranges was wrong actually. Now it was already easy to rectify the fault. Your contribution is highly appreciated. – A Pofta Tapofta Mar 12 '23 at 19:43

0 Answers0