Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record - Playback (2003)

    Please can anyone explain why I cannot get this macro to play back what I record. It works fine when I do it manually, but refuses to play back??

    Thanks

    Nath
    Attached Files Attached Files

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

    Re: Record - Playback (2003)

    Double-clicking the lower right corner of the cell as a shortcut for filling down to the last filled cell in the neighbouring column isn't recorded correctly by Excel. It is translated to Selection.FillDown, but FillDown expects that the range to be filled is selected.

    Here is an alternative version that doesn't use the filter:

    Sub Divide_BC06902_by_10()
    Dim r As Long
    Dim m As Long
    m = Cells(Rows.Count, 2).End(xlUp).Row
    For r = 2 To m
    If Cells(r, 1) = "BC069-02" Then
    Cells(r, 2) = Cells(r, 2) / 10
    End If
    Next r
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record - Playback (2003)

    Thanks Hans,

    I changed it to:

    Sheets("Rejections 1").Select
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=IF"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-9]="""","""",IF(RC[-9]=""BC069-02"",RC[-1]/10,RC[-1]))"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J2000"), Type:=xlFillDefault
    Range("J2:J2000").Select
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("I2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.ClearContents

    This works fine, but yours looks more efficienst so will give it a go.

    Cheers,
    Nath

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record - Playback (2003)

    Sorry Hans,my data is actually in columns A&I.what do I need to change?

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

    Re: Record - Playback (2003)

    Cells(r, 1) represents the value that you want to compare to BC069-02. In your sample workbook it was in column A, i.e. column # 1.
    Cells(r, 2) represents the value that you want to divide by 10. In your sample workbook it was in column B, i.e. column # 2.
    Cells(Rows.Count, 2) is also in column B.
    Change 1 and 2 to the appropriate column numbers.

  6. #6

Posting Permissions

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