PDA

View Full Version : [SOLVED] If values equal zero command.



headsniper
11-10-2018, 05:52 AM
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.

23181

Please help!

Paul_Hossler
11-10-2018, 07:51 AM
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

headsniper
11-10-2018, 08:18 AM
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.