Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cells(xx, xx).Select Error (Excel 2000 SR-1)

    I have picked up someones old code and I am also just picking up VBA as well.

    The old code is under "Sheet1(Key Metrics Report)" in the Project Explorer, linked to a button on the first page. I am trying to add this code:

    If row = 11 Then
    Dim myrow As Integer
    myrow = 25
    Dim mycolumn As Integer
    mycolumn = 2
    Worksheets("Data").Cells(23, column).Value = 0
    Worksheets("Data").Cells(25, column).FormulaR1C1 = "=R[-1]C*'Key Metrics Report'!R31C[-1]"
    Worksheets("Data").Cells(25, mycolumn).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.FillRight
    End If

    I know it needs some refinement, but it works, except for the "Worksheets("Data").Cells(25, mycolumn).Select" statement. I have changed the statement to "Cells(25, mycolumn).Select", and it works fine, on the wrong sheet of course.

    My thinking is that since it is on the "Sheet1(Key Metrics Report)" object, I can not select a cell not on the sheet. The error is "Run time error '1004': Select method of Range class failed.

    Thanks,
    Justin K.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells(xx, xx).Select Error (Excel 2000 SR-1)

    My guess is that the "Data" worksheet is not the active sheet, and you can't select a cell on a sheet that is not the active sheet.
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cells(xx, xx).Select Error (Excel 2000 SR-1)

    As Legare Coleman wrote, you can't select a cell that is not in the active worksheet.

    If you really want the range on the Data worksheet to be selected, activate the worksheet first:

    <font color=blue>Worksheets("Data").Activate</font color=blue>
    Worksheets("Data").Cells(25, mycolumn).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.FillRight

    Or if you just want to fill right a range on the Data worksheet, and don't need to see it, you can do it without selecting the range:

    Dim wsh As Worksheet
    Dim rng As Range
    Set wsh = Worksheets("Data")
    Set rng = wsh.Cells(25, mycolumn)
    wsh.Range(rng, rng.End(xlToRight)).FillRight

    HTH,
    Hans

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells(xx, xx).Select Error (Excel 2000 SR-1)

    Ok, now it all makes sense. Thanks a lot!

    Just one more minor problem. When I fill right, I need the second cell references in the formula to be absolute.
    The formula Worksheets("Data").Cells(25, (column + 1)).FormulaR1C1 = "=R[-1]C*'Key Metrics Report'!R31C[-1]" doesn't make anything absolute
    So how to I tell it to make R31C[-1] absolute?

    Thanks,
    Justin K.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells(xx, xx).Select Error (Excel 2000 SR-1)

    The [-1] in R31C[-1] is a relative reference to the cell one to the left of the cell the reference is in. You would need to change that [-1] to be the absolute column number of the column you want to reference. If that column is the column to the left of where the code is placing the formula, then this should do it:

    <pre> Worksheets("Data").Cells(25, (column + 1)).FormulaR1C1 = "=R[-1]C*'Key Metrics Report'!R31C" & column
    </pre>

    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cells(xx, xx).Select Error (Excel 2000 SR-1)

    Thank a lot, it worked great! I'm sure you knew that though. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Justin K.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •