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

Thread: Jay Petrulis or anyone else.

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

    Default

    My saga continues.

    Still trying to clean up the mcaro I recorded.

    In "COL D", I want to enter TEXT as "PAI",
    then have it copied down to where the data ends in the adjacent Col "C".

    How can I accomplish this?

    Finally;
    "R" lists my salesmans COMM Rate.
    "O" lists the Sales for an item for the month.
    From P2: where-ever the data ends in "O",
    I need to start by taking R2*O2, and conyinue on down the line as far as the data entends in "O".

    Can I do something like this:

    Dim lastrow As Long

    With ActiveSheet
    .UsedRange
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    .Range(("P2:P" & lastrow)= "RC[-1]*[RC+2]"
    End With

    End Sub

    Have a Great Day,
    Zac

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

    Default

    Used the following and it worked:

    Dim lastrow As Long

    With ActiveSheet
    .UsedRange
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    .Range("P2:P" & lastrow) = "=RC[-1]*RC[+2]"
    End With

    End Sub

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

    Nice job. Best way to learn is to figure it out for yourself.

    If I understand correctly, you recorded a macro and deciphered what resulted. Then, you saw the limitations of that and received an answer which worked, but you did not understand how. A bit of tinkering, and you now have a pretty good grasp of what is going on. It will only get better, too!

    Stick with the board and in a few months you will look back at your requests and say, "That was so easy..."

    Bye,
    Jay

  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,

    What I've been doing is running (stepping into) the original recorded MACRO line by line to see what results. I get to the last part I'm trying to clean up, and that's formatting Col "P".

    Then I stop just before the "crash" begins.
    I create a macro on the side and attach it to a button using the code below. It runs fine. I then copied it into the original MACRO, and it fails. !?!?!?!?!?!?!?

    The problem.
    I inserted the following into the MACRO I initially recorded, and I'm getting a Debug Msg on the "lastrow As Long".

    'This Code takes N2*M2, places it in O2, then copies the formula
    'all the way to the bottom of the ACTICE SHEET.
    Dim lastrow As Long
    With ActiveSheet
    .UsedRange
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    .Range("O2:O" & lastrow) = "=RC[-2]*RC[-1]"
    'End Formating "O".
    'The Following Code takes what is in (R2*.01) to the Bottom
    'of the Active Sheet.
    Application.Goto Reference:="R2C15"
    Dim UsedRng As Range, UsedCell As Range
    With ActiveSheet
    Set UsedRng = Intersect(.UsedRange, .Range("R2:R65536"))
    For Each UsedCell In UsedRng
    UsedCell = UsedCell * 0.01
    Next UsedCell
    'End Formating "R"
    'The following Formats Column "P" as required.
    Dim lastrow As Long
    With ActiveSheet
    .UsedRange
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    .Range("P2:P" & lastrow) = "=RC[-1]*RC[+2]"
    'End Formating "P".

    It doesn't fail on the previous lastrow stmnt.

    Any ideas?

    Zac

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

    Default

    Jay,

    I was getting a "Duplicate Stmnt" error so I
    placed a ( ' ) in front of the Dim lastrow As Long stmnt to "text" it.

    At the end of my MACRO I have

    End With

    End Sub

    When I start to step into the macro line by line the first error msg I get states you have a compiling error, that is looking for an "End With" stmnt. Which I have.
    So I "text" the End With, and the next error I get is that it's looking for an End Sub.
    Which I had.

    Now I'm stumped for sure.

  6. #6
    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,

    Don't know for sure, but I suspect that you've already dimensioned the variable and need to use the ReDim statement to redimension it.

    However, that is not needed in this case, as you already have found the last row on the worksheet. You can reuse it.

    A few points to note.

    1. Dim statements declare variable types and go at the top (for readability). We won't go into global variables here.

    2. Once you are working with the activesheet, you don't need to redo the setup if you want to work with another range.

    3. The 'lastrow' determination has been done twice in your code, but you haven't added or deleted any rows, so you gain nothing, as lastrow is the same at the top of the procedure and the bottom.

    Try the following:

    '-----------
    Sub test()
    Dim lastrow As Long
    Dim UsedRng As Range, UsedCell As Range

    With ActiveSheet
    .UsedRange 'Resets the range (so I'm told)

    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    .Range("O2:O" & lastrow) = "=RC[-2]*RC[-1]"
    .Range("P2:P" & lastrow) = "=RC[-1]*RC[+2]"

    Set UsedRng = .Range("R2:R" & lastrow)
    For Each UsedCell In UsedRng
    UsedCell = UsedCell * 0.01
    Next UsedCell

    End With

    End Sub
    '----------------

    I prefer to load all the formulas at runtime and at one shot. To me, this is much easier to read than what you have.

    Bye,
    Jay

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
  •