Conditional Formatting in a Macro

jennyyoung · #1 05-11-2018, 06:58 AM

Each morning at work I run a Purchase order register report in Global Shop (the software we use) and I export a CSV file. When I open it in Excel, there are 4 columns that I isolate and reformat so I can sort the purchases according to plant location. Then I sum the totals. I created a Macro to do the formatting part for me. (text to columns, etc)

Now the part that has me stumped is...I am trying figure out conditional formatting or a formula to insert in the macro that will enter the plant name according to the account number.

So my file starts out with account numbers that look like this: 73500-B002

The letter B tells me the location. In my macro I used text to columns to separate the two parts of the account number so I have 73500 in one cell and B002 in the cell next to it, so I can sort.

Some account numbers do not have a letter and look like this: 73500-002, I can tell by the value in the last three digits. So it is the second column that tells me the location.

I started my macro at the beginning with unformatted CSV and I went through all the steps to isolate my data and then I stop recording.

So bottom line is, what can I do now, that I can add to my macro, a formula or rule or condition, to automatically fill in the plant location?

Like for example some formula that says: If the value starts with B, then return this value or if the value is between 004-037 then return this value?

I know that is the IF function, but how does it work when there are many variations?

I am very new to formulas and macros, so forgive me if I am not presenting this clearly. I am attaching the document.

p45cal · #2 05-13-2018, 05:09 AM

Well, you're going to need a lookup table of some sort to translate the first letter/3 digits into the plant locations as you want to see them. Could you attach such a table or at least go through how the information in column B is used to determine location.

jennyyoung · #3 05-14-2018, 08:13 AM

I sorta wonder if the irregularities of the many variables would make it too difficult. But attached is the list. The orange highlighted column is the data that I manually input, the plant location. To the right I put a legend, the info that I am looking for that tells which is which. Also, a few of the characters have red font, those are just indicating which character in that cell is the defining one.

p45cal · #4 05-14-2018, 10:32 AM

037 lnks both to HOLMES and EAST; which is it?

jennyyoung · #5 05-14-2018, 02:06 PM

Opps!
37 Holmes
38-100 east

p45cal · #6 05-14-2018, 02:48 PM

In the attached there are two solutions on Sheet1. A simpler one (column F) that gives the same results as you expect from the data you provided, but on a test sheet (Sheet1 (2)) gives different results (see cell F407).
This is the of formula:
=INDEX($N$2:$N$13,MATCH($B2,$M$2:$M$13))

The more complex formula (column E) uses a different lookup table:
=IF(LEFT($B2,1)="B","BRYAN",IF(LEFT($B2,1)="U","AL MEDA",INDEX($K$2:$K$11,MATCH(VALUE(RIGHT($B2,3)),$ J$2:$J$11))))
gives more consistent results (see cell E407).

On Sheet1 there are comments in cells J2 and M2 regarding setting up the lookup tables.

It should be enough to get you started.

jennyyoung · #7 05-15-2018, 06:16 AM

thank you so much!!

jennyyoung · #8 05-15-2018, 06:58 AM

oh.... wait I have one more question if you dont mind.... Sooooo If I already had a complete list that had all possible accounts listed and their respective plants, that would make it more consistent? Could you please show me one more time? On the attached file, the first list is the report without plants, on the 2nd tab is the complete department list with all possible entries.

p45cal · #9 05-15-2018, 07:26 AM

Quote:

Originally Posted by jennyyoung

Sooooo If I already had a complete list that had all possible accounts listed and their respective plants, that would make it more consistent?

Yes, the full list of all possible corresponding codes and plants will be the most consistent, and if there's a mistake in any code on Sheet2 you will be alerted to that fact by an error in the plant cell - which could be useful; it could make you ask the question do we have a new dept code? or has someone made a mistake?

It's a balancing act; do I have a long list (more difficult to maintain when there are department changes) or a shorter list where we know a given plant will be associated with a range of numbers (this is often the case with cost centre names)?
If you are going to use a full list then the 3rd argument of the MATCH function has to be 0 (it looks only for an exact match) and the dept numbers can then be in any order. Also it's easier to write the formula if you use VLOOKUP with the last argument being FALSE (also an exact match).

The two formulae are in columns D and E of Sheet2 of the attached.

If you want a friendlier thing to show than an error code, then you can enclose the formula in the IFERROR function, see cells F42 and F43 of Sheet2.

jennyyoung · #10 05-15-2018, 07:38 AM

I used VLOOKUP and i think I got it to work.

My last task to add to the macro is to give me total for each plant. I hate to ask again, but it would be my last question I promise!

whats the best way to do that? thanks again for everything!

p45cal · #11 05-15-2018, 10:00 AM

Your LOOKUP formula is not necessarily a good choice, it's a legacy function and needs the lookup table to be in the right order. I see it's not in Macro3, however that formula isn't quite right vis-a-vis the absolute/relative references. The attached contains the right formula in Macro3.
Macro3 has been streamlined. It also calls the AddPivot macro at the end.
For a REPORT sheet that's already got the data that your original Macro3 left there, there's a button to add a pivot table. It's a very, very basic pivot table.

Code:

Code:

Option Explicit
Sub Macro3()
Dim lr As Long
Range("A:X,Z:Z,AC:AI").Delete
Range("B:B").ClearContents
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
      :="-", FieldInfo:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True
Range("A:A").Delete
Rows(1).Insert
Range("A1:C1").Value = Array("DEPT", "AMT", "PLANT")
Range("A:C").HorizontalAlignment = xlCenter
lr = Application.Max(2, Cells(Rows.Count, "A").End(xlUp).Row)
With Range("C2:C" & lr)
  .FormulaR1C1 = "=VLOOKUP(RC1,TABLE!R1C1:R133C2,2,FALSE)"
  '.Value = .Value 'include this if you want to convert formulae results into plain values.
End With
AddPivot    'adds pivot table.
End Sub


Sub AddPivot()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      Sheets("REPORT").Cells(1).CurrentRegion.Resize(, 3)).CreatePivotTable(TableDestination:= _
      Sheets("REPORT").Range("E1"))
  .RowAxisLayout xlTabularRow
  .PivotFields("PLANT").Orientation = xlRowField
  .AddDataField .PivotFields("AMT"), "Sum of AMT", xlSum
End With
End Sub

jennyyoung · #12 05-15-2018, 10:29 AM

thank you very much!

jennyyoung · #13 05-16-2018, 06:24 AM

Everything is working great! Learning macros a little has opened up a whole new realm of possibility. "What else can I automate??"

Last question...I assigned a short cut key to my macro, if I emailed the file to someone, will that shortcut key work? or only for me?

p45cal · #14 05-16-2018, 09:37 AM

I don't know. Try it. If it doesn't carry, come back and I'll make sure it will.

jennyyoung · #15 05-17-2018, 07:16 AM

Actually I have one last question.

when I showed my boss, he pointed out something I forgot. So I revised the table to include the entire account number and I recreated the macro. But i have a bug somewhere when I tried to include the pivot table at the end. Can you see what I did wrong?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
conditional formatting macro	ewso	Excel Programming	2	10-05-2017 12:10 AM
Conditional formatting	bbutl027	Excel	15	12-05-2016 08:56 PM
Conditional Formatting Macro	grexcelman	Excel Programming	1	01-10-2015 04:45 PM
Conditional formatting that ignores other formatting rules	info_guy2	Excel	1	07-03-2014 10:07 AM
Conditional Formatting and/or Macro for Character Insertion?	MonOverArchIng	Word	2	01-11-2013 02:48 AM