Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-11-2018, 06:58 AM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default Conditional Formatting in a Macro

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.
Attached Files
File Type: xlsm 050918.xlsm (16.5 KB, 2 views)
Reply With Quote
  #2  
Old 05-13-2018, 05:09 AM
p45cal p45cal is offline Windows 10 Office 2010 32bit
Competent Performer
 
Join Date: Apr 2014
Posts: 170
p45cal is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 05-14-2018, 08:13 AM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

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.
Attached Files
File Type: xlsx Table Example.xlsx (10.6 KB, 3 views)
Reply With Quote
  #4  
Old 05-14-2018, 10:32 AM
p45cal p45cal is offline Windows 10 Office 2010 32bit
Competent Performer
 
Join Date: Apr 2014
Posts: 170
p45cal is on a distinguished road
Default

037 lnks both to HOLMES and EAST; which is it?
Reply With Quote
  #5  
Old 05-14-2018, 02:06 PM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

Opps!
37 Holmes
38-100 east
Reply With Quote
  #6  
Old 05-14-2018, 02:48 PM
p45cal p45cal is offline Windows 10 Office 2010 32bit
Competent Performer
 
Join Date: Apr 2014
Posts: 170
p45cal is on a distinguished road
Default

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.
Attached Files
File Type: xlsx msOfficeForums39050Table Example.xlsx (30.2 KB, 3 views)
Reply With Quote
  #7  
Old 05-15-2018, 06:16 AM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

thank you so much!!
Reply With Quote
  #8  
Old 05-15-2018, 06:58 AM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

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.
Attached Files
File Type: xlsx List.xlsx (28.1 KB, 1 views)
Reply With Quote
  #9  
Old 05-15-2018, 07:26 AM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 170
p45cal is on a distinguished road
Default

Quote:
Originally Posted by jennyyoung View Post
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.
Attached Files
File Type: xlsx MSOfficeForums39050List.xlsx (30.2 KB, 1 views)
Reply With Quote
  #10  
Old 05-15-2018, 07:38 AM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

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!
Attached Files
File Type: xlsm list2.xlsm (37.5 KB, 1 views)
Reply With Quote
  #11  
Old 05-15-2018, 10:00 AM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 170
p45cal is on a distinguished road
Default

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
Attached Files
File Type: xlsm msOfficeForums39050list2.xlsm (43.7 KB, 1 views)
Reply With Quote
  #12  
Old 05-15-2018, 10:29 AM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

thank you very much!
Reply With Quote
  #13  
Old 05-16-2018, 06:24 AM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

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?
Reply With Quote
  #14  
Old 05-16-2018, 09:37 AM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 170
p45cal is on a distinguished road
Default

I don't know. Try it. If it doesn't carry, come back and I'll make sure it will.
Reply With Quote
  #15  
Old 05-17-2018, 07:16 AM
jennyyoung jennyyoung is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Apr 2018
Posts: 15
jennyyoung is on a distinguished road
Default

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?
Attached Files
File Type: xlsm MACRO.xlsm (56.1 KB, 1 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 08:57 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft