Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Debug a macro (Excel 2002)

    Hi,
    Could someone assist me in debugging the macro. I've been at it for a while, and Excel is still hassled with something.
    The data in the Data sheet must be copied to the right column in the SAP sheet.
    THANX
    Regards,
    Rudi

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

    Re: Debug a macro (Excel 2002)

    In your code, you set

    ListTitle = " OT2.0"

    in one of the cases (a Select Case statement would be more efficient than all those ElseIfs, but that is beside the point here). You then search for this value in row 12, but there is no cell containing " OT2.0" (note the space before the O). You should probably use

    ListTitle = "OT1.5"
    and
    ListTitle = "OT2.0"

    (without the space before the O)

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Debug a macro (Excel 2002)

    I spotted that just before I read your reply. Thanx for pointing it out anyways.
    I would like to streamline the code further. Hans could you help me with that Case Select that you mentioned! I would like to get to grips with this code structure too! Use the new attachment
    Is there any other suggestions or improvements I can make to generalize the code?
    Thanx
    Regards,
    Rudi

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Debug a macro (Excel 2002)

    Hi Rudi,
    Your Select Case statement would look something like:
    <pre>Select Case ListTitle
    Case Is = "PPH"
    ListTitle = "Paid Public Holiday"
    Case Is = "Combined Normal Hours"
    ListTitle = "Normal Hours"
    Case Is = "Combined OT1.5"
    ListTitle = "OT1.5"
    Case Is = "Combined OT2.0"
    ListTitle = "OT2.0"
    Case Else
    End Select
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Debug a macro (Excel 2002)

    Thanks Rory.
    Hans mensioned that it would be more efficient that the ElseIf's. In what way is that? It is more or less the same amount of code, and in both cases there are conditions to evaluate.
    Never the less, I will change the macro if the case select is more efficient!
    PS, I see you ended with Case Else. Is this the way to end a Case Select statement??
    Regards,
    Rudi

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Debug a macro (Excel 2002)

    Rudi,
    With a Select Case statement, your ListRange is only evaluated once. That result is then compared to each of the case statements until it finds one that is true. With an if..elseif.. I believe the ListRange gets reevaluated every time. In reality, I would imagine that any speed benefits would be negligible unless this were in a loop of millions of passes. I do, however, find Select Case statements much easier to read than ElseIf so, as long as you're evaluating the same thing each time, I always use Select Case. For example:
    <pre>Select Case intSomeNumber
    Case 1
    Do something
    Case 2 To 5
    Do something else
    Case 6, 7
    Do thing three
    Case > 7
    Do a little dance
    Case Else
    Oh my God, it's zero or negative
    End Select
    </pre>

    looks nicer to me than:
    <pre>If intsomenumber = 1 Then
    Do something
    ElseIf intsomenumber >= 2 And intsomenumber <= 5 Then
    Do something else
    ElseIf intsomenumber = 6 Or intsomenumber = 7 Then
    Do thing three
    ElseIf intsomenumber > 7 Then
    Do a little dance
    Else
    Oh my God, it's zero or negative
    End If
    </pre>

    Also, I generally include a Case Else statement as a failsafe for those unforeseen moments! It is optional though.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Debug a macro (Excel 2002)

    Thanx for clarifying that. Even though (as you say) the speed is not going to apply, it is better to have the condition only once evaluated. It makes sense. The less the work the better! Even if we talk about a PC's processor <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Regards,
    Rudi

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

    Re: Debug a macro (Excel 2002)

    You should also add a line of code after the Case Else to set ListTitle to what you want it to be if none of the cases tested match.
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Debug a macro (Excel 2002)

    Yes...this is true. That is why i initially thaought it odd that it just hangs there. I had a look at the VBA help, and it explained the concept nicely there too. Strange how I only pick this up now...this Select Case Block structure. It is certainly useful, and will be using it more in the future!
    Regards,
    Rudi

Posting Permissions

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