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

    Moving formula from sheet to VBA...

    Hi guys,

    New day, new problems

    I made significant advancements in my excel file, but hit a couple of road bumps. The following is what I have problems with:

    "Trade Tab"
    - Conditional formatting [Seller] name to [Bold] when all items sold in a trade... (Don't even have a clue how to start)
    Code:
    '.............................................................
    Case 4, 5, 6, 7, 8  'change detected in other Item qty rows
    '.............................................................
        
        Select Case zCol
        '-----------------------------------
        Case [d1].Column, [k1].Column, [r1].Column, [y1].Column
        Application.EnableEvents = False                                       
        If Target.Offset(6) = "" Then                                           
            Target.Offset(6) = Target.Value                                   
            Target.Offset(6).Offset(0, 1) = "x"                               
            Target.Offset(13) = Target.Value                                 
            Target.Offset(13).Offset(0, 1) = "x"                             
        End If
        '-----------------------------------
        'If SUM(D4:D8) = 0 Then
        'C2 is Bold
        'End If
    - Clearing all content related to first cell if content is deleted from it... (Had an idea but could not tell why it is not working)
    Code:
        '-----------------------------------
        Case [b1].Column, [i1].Column, [p1].Column, [w1].Column
        Application.EnableEvents = False                                      
        
        If Target = "" Then                                                     
            temp = Target.Offset(0, 1) & r & ":Target.Offset(0,3)" & r
            Range(temp).ClearContents                                          
            
            temp = Target.Offset(6, 1) & r + 6 & ":Target.Offset(6,3)" & r + 6
            Range(temp).ClearContents                                          
            
            temp = Target.Offset(13, 1) & r + 6 & ":Target.Offset(13,3)" & r + 6
            Range(temp).ClearContents                                          
    
    
        End If
        
        Target.Offset(6) = Target.Value                                         
        Target.Offset(13) = Target.Value                                       
        '-----------------------------------
        Case Else                                                               
        'do nothing
        End Select
    - I would like to move the formula in "G15", "G22", "B24" & "G24" as well... (Obviously if someone can show me one of them, I should be able to do the rest)

    - Finally, if you happen to see something in the file, that could be made easier, I am eager to learn

    Thanks for taking the time to even look at this...

    Ferenc

    Trades 3.0.xlsm
    Last edited by Ferenc Nagy; 2015-06-26 at 11:07.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Question 1
    Conditional formatting [Seller] name to [Bold] when all items sold in a trade
    Your conditional formula in cell C2 would be:

    =SUM($D$4:$E$8)=0

    FN1.png

    Using VBA method 1:
    Code:
        Range("C2").FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=SUM($D$4:$E$8)=0"
        Range("C2").FormatConditions(1).Font.Bold = True
    VBA method 2:
    Code:
    If WorksheetFunction.Sum(Range("D4:D8")) = 0 Then Range("C2").Font.Bold = True
    Of course, you would have to unbold the cells first to see the effect of the conditional formatting.

    Could you explain in words what you are trying to achieve and what cells you are referring to with your second code. Very difficult to understand picking up in the middle of a non-working code segment with variables not defined within the amount of code provided and no clear explanation given of its purpose. Could this be what you are trying to achieve: set temp to the range of the 3 adjacent cells next to it? For example, if target is cell B4 then temp is the range D4:F4
    Code:
    Application.EnableEvents = False
    Dim temp As Range
    Row = Target.Row
    col = Target.Column
    Set temp = Range(Cells(Row, col + 2), Cells(Row, col + 4))
    Application.EnableEvents = True
    The line Set temp = Range(Cells(Row, col + 2), Cells(Row, col + 4)) gets hairy because of your merged cells

    HTH,
    Maud
    Last edited by Maudibe; 2015-06-26 at 20:27.

  3. #3
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi Maudibe,

    I just did some experimenting and managed to figure out what is wrong. The reason my code is not working has to do with merged cells. Unfortunately that is about as far as I can go...

    Here is the code I want to use but the problem is, that cell "B" is merged with"C" and that someone breaks the code. When I delete the content from cell B, the code won't recognize cell B as empty. Is there a work around for that?

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    If Target.Cells.Count > 1 Then Exit Sub                                     
    r = Target.Row                                                              
    
    
    If Target.Column = [b1].Column Then                         
        
        '-----------------------------------
        If Cells(r, "B").Value = "" Then                           
            Application.EnableEvents = False                    
            Cells(r, "G") = ""                                 
            
           End If
       
    End If
    Regarding the 2nd part, I now believe if the merged cells issue can be resolved then I will be able to do it on my own since I got it to work before except it was with cells that were not merged.
    (Btw the originaly attached file has the code and sheets as well if you want to check it out)

    Thanks
    Ferenc

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Ferenc,

    Might I ask why B & C are merged?
    Are they merged for the entire worksheet?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Because of the merged cells you have to remove the count>1 then Exit Sub and add the line to re-enable events

    Code:
    r = Target.Row
    If Target.Column = [b1].Column Then
    '-----------------------------------
        If Cells(r, "B").Value = "" Then
         Application.EnableEvents = False
         Cells(r, "G") = ""
         Application.EnableEvents = True
        End 
    Enf If
    HTH,
    Maud

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

    Might I ask why B & C are merged?
    Are they merged for the entire worksheet?
    I think it is deeply routed in my OCD

  7. #7
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Because of the merged cells you have to remove the count>1 then Exit Sub and add the line to re-enable events

    Code:
    r = Target.Row
    If Target.Column = [b1].Column Then
    '-----------------------------------
        If Cells(r, "B").Value = "" Then
         Application.EnableEvents = False
         Cells(r, "G") = ""
         Application.EnableEvents = True
        End 
    Enf If
    HTH,
    Maud

    I honestly can't believe the solution was this simple...

    Curious, anything to look out for with the removal of "count >1"? (Still a noob, so better to ask )

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Ferenc,

    Another approach:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
       Dim lRow As Long
       
       If Target.Cells.Count <> 2 Then Exit Sub  '*** Fix for Merged Cells ***
       If Intersect(Target, Range("$B:$B")) Is Nothing Then Exit Sub
       
       lRow = Target.Row
    
    If Target.Column = [b1].Column Then
        
        '-----------------------------------
        If Cells(lRow, "B").Value = "" Then
            Application.EnableEvents = False
            Cells(lRow, "G") = ""
            Application.EnableEvents = True
           End If
       
    End If
    
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Another approach:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
       Dim lRow As Long
       
       If Target.Cells.Count <> 2 Then Exit Sub  '*** Fix for Merged Cells ***
       If Intersect(Target, Range("$B:$B")) Is Nothing Then Exit Sub
       
       lRow = Target.Row
    
    If Target.Column = [b1].Column Then
        
        '-----------------------------------
        If Cells(lRow, "B").Value = "" Then
            Application.EnableEvents = False
            Cells(lRow, "G") = ""
            Application.EnableEvents = True
           End If
       
    End If
    
    End Sub

    Thank you for that. Quick follow up:

    When I have multiple If statements in a Privat sub, is it enough to write "Application.EnableEvents = True" only once at the end, just before "End Sub" or should I be doing it at the end of every If statement?

    Thanks
    Ferenc

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Ferenc,

    That depends! If there are any Exits (like Exit Sub) before the end of the routine but after the EnableEvents = False you need to turn Events back ON before that Exit just in case it is taken. You don't want to exit your routine w/o re-enabling events. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Ferenc Nagy (2015-07-02)

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    In Addition, I find it best to turn the events back on as soon as possible and not wait for the end of the code for several reasons. if your code halts for any reason (errors, code interrupts) prior to the end of the code, you code will not longer run if restarted. Your code may branch to another routine where other events are captured. Also in testing, frequently code is ended and restarted at various points and if the code was stopped prior to the line Application.EnableEvents=True your code will again no longer run. So minimize the number of lines between the time you turn it off to the time you turn it back on. It is also good to write a quick routine below the one you are working on in case you need it.

    Code:
    Public Sub Re_enable()
        Application.EnableEvents = True
    End Sub
    Removing the code that counts the number of target cells has the effect that selecting more than one cell will no longer halt the code. In that respect, target will be a range of more than one cell which may produce undesirable affects to what your code is supposed to do

    HTH,
    Maud
    Last edited by Maudibe; 2015-06-27 at 14:14.

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    Ferenc Nagy (2015-07-02)

  14. #12
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thank you guys for the help, with your help I managed to move significantly forward.
    Of course I reached another scenario I never done before and failing like a trooper at it

    - I am trying to move the "SUM(N10:N14)" into my VBA and keep it generic so it would work for all cases. The row with the 3 stars was my attempt at doing just that but I get the 'Type mismatch' error. Also I think using 15 to identify the row of the cell might not work either. Any ideas would be greatly appreciated...

    Code:
    '...........................................................................
    Case 10, 11, 12, 13, 14, 17, 18, 19, 20, 21                              
    '...........................................................................
        
        Select Case zCol
        '-----------------------------------
        Case [f1].Column, [m1].Column, [t1].Column, [aa1].Column  
        Application.EnableEvents = False                                       
        If Target.Offset(0, -2) <> "" Then                                      
            If Target <> "" Then                                                
            Target.Offset(0, 1) = Target.Offset(0, -2).Value * Target.Value    
    ***   Cells(15, zCol + 1) = Target.Offset(-5) + Target.Offset(-4) + Target.Offset(-3) + Target.Offset(-2) + Target.Offset(-1)
            End If
        End If
        
        '-----------------------------------
        Case Else
        'do nothing
        End Select
        '-----------------------------------
        Application.EnableEvents = True
    Edit: Nevermind, figured it out!
    Last edited by Ferenc Nagy; 2015-07-02 at 09:36.

Posting Permissions

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