none
Select worksheet based on the value of a cell RRS feed

  • Question

  • Hello.  I am trying to select a worksheet based on the value of a cell using r1c1.  Without using r1c1, I have been able to do it as such:

    Sheet(Range("A2").Value).Select

    But the following has not worked:

    Sheet(Range(RC[-3]).Value).Select

    This results in an "Expected: list separator or)" error.

    I am sure I am probably missing something simple, but I just can't seem to get it.

    Any help would be greatly appreciated.

    Thanks!

    Sunday, March 17, 2013 7:37 PM

Answers

  • Dim sFla As String
    Dim sShtName As String
    
    sFla = "=WORKDAY(RC[-1],RC[-2],'[Holidays.xlsx]XnameX'!RC1:RC1)"
    sShtName = ActiveCell.Offset(0, -3).Value
    sFla = Replace(sFla, "XnameX", sShtName)

    Whilst the above is one way to do what you are asking (there are other ways), unless you have a specific reason the approach of making relative formulas based on the acitvecell is at best convoluted and at worst likely to lead to errors.

    Peter Thornton

    Sunday, March 17, 2013 10:07 PM
    Moderator

All replies

  • Range only accepts cell addresses in A1 format, not in R1C1 format. If you want to refer to the cell 3 columns to the left of the active cell, you can use

    ActiveCell.Offset(0, -3)


    Regards, Hans Vogelaar

    Sunday, March 17, 2013 8:20 PM

  • Even if the Range object could accept a R1C1 address Excel would need to be configured to use R1C1 references.

    However your RC[-3]) is a relative address, ie relative to the activecell. The cell you appear to be trying to reference is 3 cells to the left of the active cell in the same row, which of course assumes you know what the active cell is, which must be in at least column D.

    Your code appears to be trying to do the following, though I wonder if that's what you really want to be doing!

    Worksheets(ActiveCell.Offset(0, -3).Value).Activate

    Peter Thornton

    Sunday, March 17, 2013 9:04 PM
    Moderator
  • Thanks for the help.

    I want to use the WORKDAY formula to find a list of holidays from another workbook.  The value of the cell I am selecting with "ActiveCell.Offset(0, -3).Value" is the name of the sheet in the other workbook.  How would I put that reference together?  I have:

    ActiveCell.FormulaR1C1 = _
            "=WORKDAY(RC[-1],RC[-2],'[Holidays.xlsx]Sheet1'!RC1:RC1)"

    Where "Sheet1" needs to be the sheet named the value of the cell selected in the other workbook.

    So for each row in the initial workbook, there is a value that corresponds to a sheet with a list of holidays in the other workbook that should be used in the WORKDAY calculation. 

    Sunday, March 17, 2013 9:51 PM
  • Dim sFla As String
    Dim sShtName As String
    
    sFla = "=WORKDAY(RC[-1],RC[-2],'[Holidays.xlsx]XnameX'!RC1:RC1)"
    sShtName = ActiveCell.Offset(0, -3).Value
    sFla = Replace(sFla, "XnameX", sShtName)

    Whilst the above is one way to do what you are asking (there are other ways), unless you have a specific reason the approach of making relative formulas based on the acitvecell is at best convoluted and at worst likely to lead to errors.

    Peter Thornton

    Sunday, March 17, 2013 10:07 PM
    Moderator
  • Apologies for an extremely delayed reply on this.  When I used the code you provided on its own, it worked perfectly, however, when I added the code into my longer macro, it is returning a run-time error - 1004 Application-defined or object-defined error - on the ActiveCell.FormulaR1C1 = sFla line:

    Range("V2").Select
          sFla = "=WORKDAY(R[-1]C[1],1,'[PTO.xlsx]XnameX'!RC1:RC1))"
                        sShtName = ActiveCell.Offset(0, -6).Value
                        sFla = Replace(sFla, "XnameX", sShtName)
                        ActiveCell.FormulaR1C1 = sFla

    Can you see something wrong with the code?

    Thanks,

    Alex

    Thursday, March 28, 2013 10:48 PM
  • Compared with my original formula yours has two closing brackets vs mine with only one, and yours has
    WORKDAY(R[-1]C[1]
    vs mine
    WORKDAY(RC[-1],RC[-2]

    Peter Thornton

    Friday, March 29, 2013 12:08 PM
    Moderator
  • I did miss the extra closing bracket, but unfortunately, the error is still occurring (application defined or object defined error).

    Range("V2").Select
                        sFla = "=WORKDAY(R[-1]C[-2],1,'[PTO.xlsx]XnameX'!RC1:RC1)"
                        sShtName = ActiveCell.Offset(0, -6).Value
                        sFla = Replace(sFla, "XnameX", sShtName)
                        ActiveCell.FormulaR1C1 = sFla

    Any ideas?  Thanks again for all the help.

    Friday, March 29, 2013 2:04 PM
  • It looks like you didn't correct the second differences I mentioned, refer to my previous post and this bit

    WORKDAY(R[-1]C[1]
    vs
    WORKDAY(RC[-1],RC[-2]

    Peter Thornton

    Saturday, March 30, 2013 3:01 PM
    Moderator
  • Apologies - the number of days is in Column T and the cell I need the formula in is V so I think the WORKDAY(RC[-1],RC[-2] part is correct.

    The error appears to occur when trying to set the cell to the formula.  I added a text box to show the value of sFla to ensure it is correct and it is, but when the

    ActiveCell.FormulaR1C1 = sFla 

    line tries to execute, I get the error.

    Saturday, March 30, 2013 7:16 PM
  • I figured it out and I feel terrible about wasting your time because it should have been a clearly obvious error - the name of the sheet it was trying to find did not exist in the other workbook it was referencing.  A thousand apologies and thanks again for the help with this!
    Saturday, March 30, 2013 7:28 PM
  • No problem and thanks for letting us know.

    Peter Thornton

    Tuesday, April 2, 2013 8:41 AM
    Moderator