Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Last Question for the day

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a formula in Column R Row 2 that I want to have scrolled down till the end of column B.

    Ex. Col.B ends at row 200, I want the macro to click and drag the formula till row 200.


    I can't figure it out?

    Any suggestions, Thanks

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You want r2:b200 filled, 'cause that is:

    Code:
    Sub cpier()
    [r2].Copy [b2:r200]
    End Sub
    Doesn't seem like that's what you would want though....

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-26 16:20 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not clear on what you need? Do you want
    the formula that resides in R2 copied from
    B2:B200?

    James

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If it's b2:b200 then:

    Code:
    Sub cpier()
    [r2].Copy [b2:b200]
    End Sub
    Must be off. Hope this helps.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, what I need is the formula in R2 to be clicked and dragged. But to stop at the same row column B ends. Thanks

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Range("R2:R200").FillDown

    ????????

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The thing is the range changes. Depending on the number of rows in column B. So it could be 5, 100 or 15000.

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    If column B is variable in length, then try,

    Sub test()
    Dim lastrow As Long

    lastrow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("R2").Copy Range("R3:R" & lastrow)

    End Sub

    Also, to prevent the formula in cell R2 from accidentally being deleted, load that at run time as well.

    Range("R2:R" & lastrow) = "=10*ROW()"
    for example.

    Bye,
    Jay


  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    YES!!!!! Now I can go and have a beer. Jay if you were around I would get you one. Thanks, that did the job and I am done for the week.

    Parra

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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