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

Thread: Having a Problem Using Autofill within a MACRO.

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hope I can explain this so someone understands it.

    I am trying to debug a MACRO created for a sales commission report.
    In COL O is the Comm Rate.
    NOTE: The Range in this Column varies from month to month. Last month when I initially recorded the macro this col had 178 rows. This month it's 228. With that in mind here's my dilemma:

    The Comm Rate is listed as x.xxx.
    A 1% rate is listed as 1.000.
    A 1/2% rate is listed as 0.500.
    I need to get it to read 0.010, .005, etc.
    so I recorded the macro in P2 to read: O2*.01
    Leaving the result as .010.
    In my initial Macro I sent to the corner of P2 cell and dbl click to autofill to the end of the active spreadsheet.
    Then I COPY the entire Col and Paste Values into COL "O" to correct all the data to the correct %. I then delete COL "P".

    My problem. The Initial MACRO still wants to use O2:O178 as the AUTOFILL instead of O2:O228. Why?? How can I sneak around this problem?

    Is there a way to reformat COL"O" so the data changes from 1.000 to .010? Tried everything I could think of. I thought the Autofill would change from month to month.

    Desperately need someones help.
    If you wish to call me use 800-536-3900 x154
    ask for Mike.
    If you need more information please ask.

    Zac





  2. #2
    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

    Rewrite your code. Delete all the adjustments made to the worksheet on the "P" column. Make all the adjustments to the "O" column directly.

    Try something like:
    ----------------
    Sub test()
    Dim UsedRng As Range, UsedCell As Range

    With ActiveSheet
    Set UsedRng = Intersect(.UsedRange, .Range("O2:O65536"))
    For Each UsedCell In UsedRng
    UsedCell = UsedCell * 0.01
    Next UsedCell
    End With

    End Sub
    ---------------

    If this doesn't work, please post your code.

    Bye,
    Jay

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jay. I have never used the
    DIM coding so I hope I get it right.

    Zac

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay Petrulis
    Need your help.

    Copied over some of the data from COL "O
    to R25:R35.

    Then I recorded a short macro to see if it works (as follows):
    'Application.Goto Reference:="R25C17"
    With ActiveSheet
    Set UsedRng = Intersect(.UsedRange, .Range("R25:R65536"))
    For Each UsedCell In UsedRng
    UsedCell = UsedCell * 0.01
    Next UsedCell
    End With

    End Sub



    Getting a DEBUG msg at "SET USEDRNG......"


  5. #5
    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

    Is there anything in column R? I cannot replicate your problem when there is data in the used range.

    Sub test()
    Dim UsedRng As Range, UsedCell As Range

    Application.Goto Reference:="R25C17"
    With ActiveSheet
    Set UsedRng = Intersect(.UsedRange, .Range("R25:R65536"))
    If Not UsedRng Is Nothing Then
    For Each UsedCell In UsedRng
    UsedCell = UsedCell * 0.01
    Next UsedCell
    End If
    End With

    End Sub

    This will stop the error if there are no entries in col R, but that doesn't really solve your problem.

    Please post your code.

    Bye,
    Jay

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay,

    I'm pretty sure I've got it figured out.
    I will let you know

    Thanks for your help.
    Zac

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
  •