locked
Issue to point to other sheet RRS feed

  • Question

  • Hi,
    Any advice to issue like

    owing to last line below? Should we put exact workbook name to avoid issue?
        With ActiveSheet
        'With Worksheets("B")
            With Range(Str0).Borders(xlEdgeLeft)



    Many Thanks & Best Regards, Jackson Chen

    Wednesday, December 2, 2020 9:53 AM

Answers

  •  "C-3:C-3" is not a valid range address.
    Manually select the cell/s, row/s or column/s you want the range to refer to, and in the VBE's Immediate window copy & paste
       ?Selection.Address
    and press enter to see the address.
    You can remove any absolute $ symbols

    • Marked as answer by Jackson_1990 Friday, December 4, 2020 8:00 AM
    Thursday, December 3, 2020 2:15 PM

All replies

  • Code in a worksheet module will always refer to its own sheet unless qualified to a different sheet and, if possibly the parent workbook if not active. Otherwise the Range address will refer to the activesheet unless qualified to a different sheet.

    The 'With' can only refer to an object, but not include any methods such as you have. Try simply-
    Range(Str0).Borders(xlEdgeLeft)

    Wednesday, December 2, 2020 10:51 AM
  • Would it be better to put exact Workbook name to avoid current issue?

    Many Thanks & Best Regards, Jackson Chen

    Wednesday, December 2, 2020 12:06 PM
  • You could include a reference to the workbook but if you do you will also need to include the worksheet, for example:
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng as Range

    Set wb = Workbooks("myBook.xlsm")
    Set ws = wb.Worksheets("Sheet1")
    Set rng = ws.Range("A1")

    If the worksheet belongs to the workbook your code is in you could do this:
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Wednesday, December 2, 2020 6:49 PM
  • Sorry to that I still get

    due to last line below

    Sub Fill_Border(Str0 As String)
        With ThisWorkbook.ActiveSheet
        'With Worksheets("B")
            With Range(Str0).Borders(xlEdgeLeft)



    Many Thanks & Best Regards, Jackson Chen

    Thursday, December 3, 2020 4:24 AM
  • What is the value of Str0?
    If Str0 refers to a valid cell address is the sheet protected and are any cells in the Range Locked?

    If you want the Range to refer to cells in refer to the With ActiveSheet above, prefix Range with a dot, for example
    With ThisWorkbook.ActiveSheet
    With .Range("B2").Borders(xlEdgeLeft)

    Thursday, December 3, 2020 9:46 AM
  • Str0 is having value "C-3:C-3".

    I got error

    due to last line below
        With ThisWorkbook.ActiveSheet
        'With Worksheets("B")
            With .Range(Str0).Borders(xlEdgeLeft)



    Many Thanks & Best Regards, Jackson Chen


    • Edited by Jackson_1990 Thursday, December 3, 2020 10:17 AM
    Thursday, December 3, 2020 10:13 AM
  •  "C-3:C-3" is not a valid range address.
    Manually select the cell/s, row/s or column/s you want the range to refer to, and in the VBE's Immediate window copy & paste
       ?Selection.Address
    and press enter to see the address.
    You can remove any absolute $ symbols

    • Marked as answer by Jackson_1990 Friday, December 4, 2020 8:00 AM
    Thursday, December 3, 2020 2:15 PM