Results 1 to 15 of 15

Thread: Simplifying VBA

  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Simplifying VBA

    Hi Guys,

    I am trying to come up with ways to simplify my code and I am sort of stuck, is there a better way to do the bold lines?

    Code:
    '...........................................................................
    Case 10, 11, 12, 13, 14, 17, 18, 19, 20, 21                                 'change detected in Cost or Return Item Price
    '...........................................................................
        
        Select Case zCol
        '.......................................................................
        Case [f1].Column, [m1].Column, [t1].Column, [aa1].Column                'price entry
        '.......................................................................
        Application.EnableEvents = False                                        'turn events OFF during changes
        If Target.Offset(0, -2) <> "" Then                                      'check for blank in Qty cell..
            If Target <> "" Then                                                'check for blank in Price cell..
            Target.Offset(0, 1) = Target.Offset(0, -2).Value * Target.Value     'calculate total cost..
            End If
        End If
       
     '.......................................................................'when Cost > Price is updated, update Total Cost, Profit and Profit Margin..
        If zRow = 10 Then
            LID = Target.Offset(-8, -4)
            updateTrade
        End If
        If zRow = 11 Then
            LID = Target.Offset(-9, -4)
            updateTrade
        End If
        If zRow = 12 Then
            LID = Target.Offset(-10, -4)
            updateTrade
        End If
        If zRow = 13 Then
            LID = Target.Offset(-11, -4)
            updateTrade
        End If
        If zRow = 14 Then
            LID = Target.Offset(-12, -4)
            updateTrade
        End If
        If zRow = 17 Then
            LID = Target.Offset(-15, -4)
            updateTrade
        End If
        If zRow = 18 Then
            LID = Target.Offset(-16, -4)
            updateTrade
        End If
        If zRow = 19 Then
            LID = Target.Offset(-17, -4)
            updateTrade
        End If
        If zRow = 20 Then
            LID = Target.Offset(-18, -4)
            updateTrade
        End If
        If zRow = 21 Then
            LID = Target.Offset(-19, -4)
            updateTrade
        End If

    Thanks for looking...
    Ferenc
    Last edited by Ferenc Nagy; 2015-07-19 at 12:05.

  2. #2
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Figured it out...

    '................................................. ..........................
    Case 10, 11, 12, 13, 14, 17, 18, 19, 20, 21 'change detected in Cost or Return Item Price
    '................................................. ..........................

    Select Case zCol
    '................................................. ......................
    Case [f1].Column, [m1].Column, [t1].Column, [aa1].Column 'price entry
    '................................................. ......................
    Application.EnableEvents = False 'turn events OFF during changes
    If Target.Offset(0, -2) <> "" Then 'check for blank in Qty cell..
    If Target <> "" Then 'check for blank in Price cell..
    Target.Offset(0, 1) = Target.Offset(0, -2).Value * Target.Value 'calculate total cost..
    End If
    End If

    '................................................. ......................'when Cost > Price is updated, update Total Cost, Profit and Profit Margin..
    r = zRow - 2
    LID = Target.Offset(-r, -4)
    updateTrade

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    Unless you need the value r for some other purpose simply LID = Target.Offset(-(zRow-2),-4)

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Ferenc Nagy (2015-07-19)

  5. #4
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Ferenc,

    Unless you need the value r for some other purpose simply LID = Target.Offset(-(zRow-2),-4)

    HTH

    You are right...

  6. #5
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    I have one that I could not figure out...

    Code:
        If zRow = 4 Then
            LID = Target.Offset(-11, 4)
        End If
        If zRow = 5 Then
            LID = Target.Offset(-10, 4)
        End If
        If zRow = 6 Then
            LID = Target.Offset(-9, 4)
        End If
        If zRow = 7 Then
            LID = Target.Offset(-8, 4)
        End If
        If zRow = 8 Then
            LID = Target.Offset(-7, 4)
        End If
    Any ideas for an easier VBA here?
    Last edited by Ferenc Nagy; 2015-07-20 at 00:43.

  7. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Figured it out again

    If zRow = 4 Then
    LID = Target.Offset(-11, 4)
    End If
    If zRow = 5 Then
    LID = Target.Offset(-10, 4)
    End If
    If zRow = 6 Then
    LID = Target.Offset(-9, 4)
    End If
    If zRow = 7 Then
    LID = Target.Offset(-8, 4)
    End If
    If zRow = 8 Then
    LID = Target.Offset(-7, 4)
    End If

    Code:
    LID = Target.Offset(-(15-zRow), 4)

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Ferenc

    ..you are making great progress with your coding!
    It's not too important to eliminate all steps, if it makes it easier to follow.
    So, although RG is correct in post#3, your post#2 would perhaps be easier to document.
    Your post#6 shows you have learnt from RG's advice.
    Top marks for reporting back - it is sure to help others too.

    zeddy
    •Pickled Banana Historian

  9. #8
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thank you for the kind words

    The code was just an example, I actually had it wrong and in the original r = target.Row so it makes sense to simplify it, as it is my notes next to the code that makes it easy to follow...

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Ferenc Nagy View Post
    as it is my notes next to the code that makes it easy to follow...
    Ferenc,

    That is the greatest coding advance you could possibly make IMHO! Code all looks great when it is first coded six months later w/o comments it is mostly gibberish even to the coder, at my age it doesn't even take that long!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #10
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Back again, but this time I have a wild one

    I am able to identify the location ID of the item and I know the column.
    I want to figure out the row matching the location ID without searching all the trades...
    Any thoughts would be welcome...

    Code:
      |   B |   I  |   P  |  W
    ---------------------------------
     2|  1  |   2  |   3  |   4
    26|  5  |   6  |   7  |   8
    50|  9  |  10  |  11  |  12
    74|  13 |  14  |  15  |  16
    98|  17 |  18  |  19  |  20
    etc
    Thanks for looking...
    Last edited by Ferenc Nagy; 2015-07-20 at 10:32.

  12. #11
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Worked it out again

    Code:
    x = Location ID
    y = Target.Row
    
    Case B
    y = x * 6 - 4
    
    Case I
    y = (x - 1)* 6 - 4
    
    Case P
    y = (x - 2) * 6 - 4
    
    Case W
    y = (x - 3) * 6 - 4
    Last edited by Ferenc Nagy; 2015-07-20 at 11:00.

  13. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    Here's a little test code you might find interesting. Both Subs will return the same results.
    Code:
    Option Explicit
    
    Sub Test1()
    
    'x = Location ID
    'y = Target.Row
    
     Dim x      As Long
     Dim y      As Long
     
     x = 10
     
     Select Case Chr(ActiveCell.Column + 64)
    
          Case "B"
              y = x * 6 - 4
    
          Case "I"
              y = x * 6 - 10
    
          Case "P"
              y = x * 6 - 16
    
          Case "W"
              y = x * 6 - 22
     End Select
     
     Debug.Print Chr(ActiveCell.Column + 64), y
     
    End Sub 'Test1
    
    Sub Test2()
    
    'x = Location ID
    'y = Target.Row
    
     Dim x      As Long
     Dim y      As Long
     Dim z      As Long
     
     x = 10
     z = InStr("BIPW", Chr(ActiveCell.Column + 64)) - 1 'Sub 1 so if Col B = 0
     y = x * 6 - (4 + (z * 6))
     
     Debug.Print Chr(ActiveCell.Column + 64), y
     
    End Sub  'Test2
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #13
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    In the Worksheet_Change sub this is the part that would call in the function or subroutine to refresh the cost total, return total, total profit & profit margin...


    Code:
        'Cost total..
        LID = Cells(8 - r, zCol)
        J = zCol
        TotalUpdate
    but I am now sure which one this should be...

    Code:
    If J = "b" Then
    I = LID * 6 - 4
    ElseIf J = "i" Then
    I = LID * 6 - 10
    ElseIf J = "p" Then
    I = LID * 6 - 16
    ElseIf J = "w" Then
    I = LID * 6 - 22
    End If
    
    
    With shtTrades
        .Cells(I + 13, J + 5) = WorksheetFunction.Sum(Range(.Cells(I + 8, J + 5), .Cells(I + 12, J + 5)))   'Cost Total
        .Cells(I + 20, J + 5) = WorksheetFunction.Sum(Range(.Cells(I + 15, J + 5), .Cells(I + 19, J + 5)))  'Return Total
        .Cells(I + 22, J + 5) = .Cells(I + 20, J + 5) - .Cells(I + 13, J + 5)                               'Total Profit
        .Cells(I + 22, J) = .Cells(I + 22, J + 5) / .Cells(I + 13, J + 5)                                   'Profit Margin
    End With
    Still not familiar enough with the basics...
    Last edited by Ferenc Nagy; 2015-07-20 at 11:53.

  15. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ferenc,

    You can replace this:
    Code:
    If J = "b" Then
    I = LID * 6 - 4
    Else If J = "i" Then
    I = LID * 6 - 10
    Else If J = "p" Then
    I = LID * 6 - 16
    Else If J = "w" Then
    I = LID * 6 - 22
    End If
    With this:
    Code:
     I = LID * 6 - (4 +  ( (InStr("BIPW", UCase(J) ) - 1) * 6))
    Here's what's going on:
    1. Ucase makes sure you're comparing Uppercase to uppercase.
    2. Instr finds the location of J in the string "BIPW" so returns 1-4.
    3. We subtract 1 to get 0-3 so the multiplication part works as 4 is the one that is not up by 6 from the last.
    4. Now it's just a formula to do the math (remember 0 times anything is 0!)


    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #15
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Ferenc,

    You can replace this:
    Code:
    If J = "b" Then
    I = LID * 6 - 4
    Else If J = "i" Then
    I = LID * 6 - 10
    Else If J = "p" Then
    I = LID * 6 - 16
    Else If J = "w" Then
    I = LID * 6 - 22
    End If
    With this:
    Code:
     I = LID * 6 - (4 +  ( (InStr("BIPW", UCase(J) ) - 1) * 6))
    Here's what's going on:
    1. Ucase makes sure you're comparing Uppercase to uppercase.
    2. Instr finds the location of J in the string "BIPW" so returns 1-4.
    3. We subtract 1 to get 0-3 so the multiplication part works as 4 is the one that is not up by 6 from the last.
    4. Now it's just a formula to do the math (remember 0 times anything is 0!)


    HTH

    While I do understand what is happening thanks to your description, I rather keep the other one because I would not remember it 6 months down the line

Posting Permissions

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