Consulting

Results 1 to 3 of 3

Thread: If values equal zero command.

  1. #1

    If values equal zero command.

    Hi guys,

    I am almost done with my first set of code.

    What I am trying to do is get a report formatted for email sending.

    I have a list of data, on a single table, and I am trying to filter them onto different tables.

    What I normally do is filter data>create header cell>copy filtered data>hide copied data and clear filters>repeat.

    What got me stuck was, I realized that I won't always have data for a certain column, and I will have to remove that blank table if it gets created.

    I learned about the code "If Range("A1").Value = 0 Then", and I was trying to incorporate it into my table, but I got completely lost in how the flow of the code should go. I understand that to make this particular code go smoothly, I need to create multiple subs in a single macro to make this work.

    Once I figure out the appropriate way to use the if value code, I can then continue with the rest of the code.

    Here is what I have so far:

    Sub Ecom_Aux_Dumptest()
    
    '
    
    
    Dim aux As Worksheet
    Dim dump As Worksheet
    
    
    Set aux = Sheets("AUX")
    Set dump = Sheets("Aux Macro Dump")
    
    
    Application.ScreenUpdating = False
    ' Hide columns
        aux.Columns("F:F").EntireColumn.Hidden = True
        
    ' Filter according to color
        aux.Range("$A$1:$O$311").AutoFilter Field:=8, Criteria1:=RGB(255, _
            199, 206), Operator:=xlFilterCellColor
            
    ' Adding Break text, with formatting
        With dump.Range("A1")
            .Value = "BREAK"
            .Name = "Tahoma"
            .Font.Size = 10
            .Font.Bold = True
            .Interior.Color = RGB(255, 255, 0)
        End With
    
    
    ' Copying and pasting the data
        aux.Columns("A:H").Copy
        dump.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
    ' Clear Filters and Hide Cell
        aux.ShowAllData
        aux.Columns("H:H").EntireColumn.Hidden = True
        
    ' REPEAT Filter according to color
        aux.Range("$A$1:$O$311").AutoFilter Field:=9, Criteria1:=RGB(255, _
            199, 206), Operator:=xlFilterCellColor
            
            
    
    
    
    
    Application.ScreenUpdating = True
    
    
    End Sub



    I attached a table for reference for what I am trying to achieve. It has been filtered to protect sensitive data.

    Attempt.xlsx

    Please help!

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Glad you attached an example workbook.

    Next time, it'd be helpful if the troublesome macro were actually in the example, and the sheet names matched the ones used in the macro

    Also, if there's any instruction steps that should be followed, it's good to include them also

    Otherwise, we're just guessing
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Hi Paul

    Thanks for pointing it out. My concern is I do not know how to set up this code.

    If Range("A1").Value = 0 Then
    I am trying to set it up so that i can add that code to affect the section copy and paste data.

    Based on the research I did, would happen would be that I would stop copying and pasting the data only.

    What is supposed to happen is that if the values are zero, It should also stop adding text to the cell. I do not know how to group in a way that the If code will impact both sets of rules.
    Last edited by headsniper; 11-10-2018 at 09:19 AM.

Posting Permissions

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