MACRO, VBA, IDW what it is, but ...

[Felix]

Guys, I swear that b4 I post a question, I try to do my research, and I swear that i really can't figure out what to or how to replace the data in those modules that I copy and paste. I have never used them, but now I need a code:

Here is the deal: the attached file is one of many that are saved in a folder C:\Users\NISG\Desktop\Felo\Docs\Excell\P & L, as you can see N1 and O1 show "cost" & "profit". I want to have a general workbook that will have the costs and profits of all these files addded as soon as I open it. How do I accomplish this?

 

[David Evans]

Well, that's a complex formula at the core of this workbook, being

=IF($B22="",0,IF($G$2=$N$1,INDEX($R$60:$R$273,MATCH(C22,$P$60:$P$273,0)),IF($G$2=$N$2,INDEX($S$60:$S$273,MATCH(C22,$P$60:$P$273,0)),IF($G$2=$N$3,INDEX($T$60:$T$273,MATCH(C22,$P$60:$P$273,0)),IF($G$2=$N$4,INDEX($U$60:$U$273,MATCH(C22,$P$60:$P$273,0)),IF($G$2=$N$5,INDEX($V$60:$V$273,MATCH(C22,$P$60:$P$273,0)),IF($G$2=$N$6,INDEX($W$60:$W$273,MATCH(C22,$P$60:$P$273,0)),IF($G$2=$N$7,INDEX($X$60:$X$273,MATCH(C22,$P$60:$P$273,0)),IF($G$2=$N$8,INDEX($Y$60:$Y273,MATCH(C22,$P$60:$P$273,0)),IF($G$2=$P$1,INDEX($R$277:$R$438,MATCH(C22,$P$277:$P$438,0)),IF($G$2=$P$2,INDEX($R$440:$R$616,MATCH(C22,$P$440:$P$616,0)),IF($G$2=$P$3,INDEX($R$618:$R$792,MATCH(C22,$P$618:$P$792,0)),IF($G$2=$P$4,INDEX($R$794:$R$1159,MATCH(C22,$P$794:$P$1159,0)),IF($G$2=$P$5,INDEX($R$1161:$R$1324,MATCH(C22,$P$1161:$P$1324,0)),0))))))))))))))

My initial suggestion is to reorganize your "lookup table" and employ a simpler formula to return the Unit Cost by Product code and Color. However, I'm not certain if that's what you were looking for ... Your request to "have a general workbook that will have the costs and profits of all these files added as soon as I open it" is confusing - Are you wanting to consolidate the data in several such workbooks?

Did you create this workbook or have you inherited it?

 

[Felix]

Thanks for your reply. I created this Workbook, and it's not that formula i want to focus on, now, but if there's a shortening for it, sure why not?

I already have the total of my costs added up in cell N1, and my proffits in cell O1. I will be using this SS for every single client and will be saving a copy in my P & L (profit and loss) folder. I know there is a macro that can add up all of the N1's and O1's of every worksheet in this P & L folder, but I have not been able to figure it out.

 

[David Evans]

OK - so let's set the formula stuff aside for a moment. Let's assume you have twenty files in your P&L folder. Where do you want the sum of all the N1s and O1 to be stored? Do they need to be list each separate "project" and then sum them? In other words are you looking for a file to summarize your jobs?


To return to the Worksheet formulas/structure;

1. you have a lot of merged cells - they will cause problems - especially if you want to use VBA to manipulate ranges of data, and there are other gotchas with them. Best avoided - I think others around here who view your worksheet will echo that one.
2. You use nested IFs to accommodate the color pricing. You'd be better served using a more conventional lookup technique.
3. You might want to consider Data Validation - List to limit the Colors by way of a dropdown. Unless you have a special need for the Code that accompanies a Color, you can rid that, and the mechanism you're currently employing to identify the color. I'd also use Data Validation List to populate the Item Codes.
4. You have "helper" cells that are outside your Printed Area, that are not helper cells - they are just duplicates and you point to them. It's very confusing and unnecessary. You hide them by formatting the font as white - if you truly need to hide cells, Use Excel's Hide function. it's a hell of a lot easier to identify where stuff is hidden.
5. The formulas in your Profit/Cost cells are not returning what one would expect. It's easily fixed.
6. I suggest you develop a Project Code format, and use that to create a standard filename - good naming conventions are very useful as you start to manipulate files with VBA. I'd also use Range Names to identify key areas of your worksheet, as VBA can accommodate them, even if for some reason you change the size of a worksheet.
7. If it were my project, I'd store every line item in each project, with their cost/Sales Price information so that you can report on them later. That would assist with understanding where exactly you're making or losing money. You especially need to know the latter quickly

Of course all these observations are more important if you're going to have others work on your worksheet - if you're the only driver of this particular Bus, it's only you that needs to understand it.

 

[p45cal]

as you can see N1 and O1 show "cost" & "profit".

I'm sure you mean N11 and O11. Are the values you want to sum ALWAYS in those two cells?

I want to have a general workbook that will have the costs and profits of all these files addded

We'll need code to run through all the files in that folder, either to create a formula (a long one) or do all the adding up in the code, or even create a list of values with file names and sheet names in your general workbook, but for this code to be reliable a few questions:
Do all the files contain only one sheet?
If not, is the relevant sheet always called CABINETS AND C-TOPS?

as soon as I open it.

…probably not instant! Especially if the number of files changes.

 

[Felix]

I'm sure you mean N11 and O11. Are the values you want to sum ALWAYS in those two cells?



We'll need code to run through all the files in that folder, either to create a formula (a long one) or do all the adding up in the code, or even create a list of values with file names and sheet names in your general workbook, but for this code to be reliable a few questions:
Do all the files contain only one sheet?
If not, is the relevant sheet always called CABINETS AND C-TOPS?



…probably not instant! Especially if the number of files changes.

1. All files contain only one sheet called CABINETS AND C_TOPS
2. I thought I could have a separate file in the same folder that when I opened it, it would give me the totals that I need without me having to add manually new sheets values to a long formula.
3. I think it's time for me to get into the VBA school, hehe
4. Yes, the values i want to sum are always in those cells

 

[Felix]

OK - so let's set the formula stuff aside for a moment. Let's assume you have twenty files in your P&L folder. Where do you want the sum of all the N1s and O1 to be stored? Do they need to be list each separate "project" and then sum them? In other words are you looking for a file to summarize your jobs?


To return to the Worksheet formulas/structure;

1. you have a lot of merged cells - they will cause problems - especially if you want to use VBA to manipulate ranges of data, and there are other gotchas with them. Best avoided - I think others around here who view your worksheet will echo that one.
2. You use nested IFs to accommodate the color pricing. You'd be better served using a more conventional lookup technique.
3. You might want to consider Data Validation - List to limit the Colors by way of a dropdown. Unless you have a special need for the Code that accompanies a Color, you can rid that, and the mechanism you're currently employing to identify the color. I'd also use Data Validation List to populate the Item Codes.
4. You have "helper" cells that are outside your Printed Area, that are not helper cells - they are just duplicates and you point to them. It's very confusing and unnecessary. You hide them by formatting the font as white - if you truly need to hide cells, Use Excel's Hide function. it's a hell of a lot easier to identify where stuff is hidden.
5. The formulas in your Profit/Cost cells are not returning what one would expect. It's easily fixed.
6. I suggest you develop a Project Code format, and use that to create a standard filename - good naming conventions are very useful as you start to manipulate files with VBA. I'd also use Range Names to identify key areas of your worksheet, as VBA can accommodate them, even if for some reason you change the size of a worksheet.
7. If it were my project, I'd store every line item in each project, with their cost/Sales Price information so that you can report on them later. That would assist with understanding where exactly you're making or losing money. You especially need to know the latter quickly

Of course all these observations are more important if you're going to have others work on your worksheet - if you're the only driver of this particular Bus, it's only you that needs to understand it.

.


1.I just one to have 2 cells added up in each sheet, and they are not merged. Would the rest merged cells affect that code?
2.I can try, but even the IFs were difficult for me to figure out. I'm sure there could be something shorter, but mine works beautiful
3.I dont know about Data Validation List. The codes that accompany the colors are entered by the user to select which color the order is about. these codes are later on found on boxes identifying, making it easier for "dispatching"
4.I wasn't aware I had duplicate cells. I have the rest in white because its easy for me to see them, but nobody else works on the background but me. The fiel that is being used is pasword protected and all helper cells are hiden. users are allowed to edit certain cells only. data entry only. not even the date.
5.Not sure here ?? my costs are added up, and substraced from the total sales, that gives me my proffit or loss
6.that is what i need to learn
7. line items are grouped by vendors, and each vendor gives a different discount from the listed retail price, thus we give our own discount depending on the "season", so to say

There are others working on this SS, but only entering data and saving the file. I request two files to be saved: 1 in the customers folder, and another one in this folder that i named P & L in which I would want to have one file named P & L that will be gathering a resumed info of all customers. I wanted to start with the cost and proffit and then start adding more things, but I think I really need to get into the coding party hehe.

THANKS A TON!!! For your reply

 

[David Evans]

VBA is not difficult, but it helps if you have a good grounding in Excel, as essentially what yo're doing is automating the process. If you have a wobbly process and you make it go faster, you tend to arrive at disaster faster

Take a look at this if you want a good primer in VBA for Excel
https://www.mooc-list.com/course/introduction-vbaexcel-programming-csupomona

It was free, and I thought it was good.

BTW, search for Data Validation and understand what it's about. It's crucial when your entering data and there's the possibility for data entry errors.

I'll post you a revision of your file that I was playing with that will illustrate how it works.

 

[David Evans]

Take a look at the attached file - I've used a lookup formula that dramatically simplifies the formula and uses dropdowns to select the cabinet code and color - these are then used in selecting the price.

My mantra is simplify, simplify, simplify. Yes your formulas worked, but they're overly-complicated and will cause problems when you add, amend your data table.

Let me know if you understand the data validation.

BTW, this is by no means the finished article. There are other ares we can look at, but one step at a time ...

 

[Felix]

Take a look at the attached file - I've used a lookup formula that dramatically simplifies the formula and uses dropdowns to select the cabinet code and color - these are then used in selecting the price.

My mantra is simplify, simplify, simplify. Yes your formulas worked, but they're overly-complicated and will cause problems when you add, amend your data table.

Let me know if you understand the data validation.

BTW, this is by no means the finished article. There are other ares we can look at, but one step at a time ...

Ill take a look at it right away. Thanks

 

[Felix]

Take a look at the attached file - I've used a lookup formula that dramatically simplifies the formula and uses dropdowns to select the cabinet code and color - these are then used in selecting the price.

My mantra is simplify, simplify, simplify. Yes your formulas worked, but they're overly-complicated and will cause problems when you add, amend your data table.

Let me know if you understand the data validation.

BTW, this is by no means the finished article. There are other ares we can look at, but one step at a time ...

I kind of get your concept now, but I would have to re-arrange the whole document. This is a "pre-contract/invoice/estimate" where rows from 15 to 21 will never been applied any type of discount, however rows from 22 to 54 would receive any where from 30% to 45. That is why I had a subtotal hiden in column A which would receive the discount typed in J57, and its result would be substracted from the Total (J56) giving the Due Balance (C58)

The part that I dont understand is how would a VBA be affected with the long formulas if I just wanted to have added the same two cells (always) of an x amount of files kept in a unique folder?

 

[David Evans]

I kind of get your concept now, but I would have to re-arrange the whole document. This is a "pre-contract/invoice/estimate" where rows from 15 to 21 will never been applied any type of discount, however rows from 22 to 54 would receive any where from 30% to 45. That is why I had a subtotal hiden in column A which would receive the discount typed in J57, and its result would be substracted from the Total (J56) giving the Due Balance (C58)

The part that I dont understand is how would a VBA be affected with the long formulas if I just wanted to have added the same two cells (always) of an x amount of files kept in a unique folder?

Let me try and answer the two highlighted points for you.

The document needs a thorough review, if you want someone else to fix it for you - it's akin to me starting to build a kitchen, stopping halfway through with bits everywhere and asking you to finish it, but not change the work I've done. If you don't want to clean it up, it will be harder to get it repaired - not impossible, but harder. Nobody wants to be liable for the logic in the IF statement for starters - they're tedious to test and check. And there's more ... but i think you get the point

I can't tell you for certain what the effect of VBA will be on the file; i suspect not a lot while they are still functioning, but let's see if someone takes up the challenge of writing the code. However, they are going to need a lot more information about your file locations, file names (which you won't know in advance, unless you've developed a solid naming convention) where you want to store the summed amounts, do you just want the total of the two cells through X worksheets, or do you want/need a summary of each two cells and then a Total Line etc. ....You've addressed none of these, which probably explains why there aren't any takers for writing the code - there are far too many unknowns

They'll want to be sure that you can maintain the code, as it will need maintenance on occasion ....

 

[Nebu]

Hi:

You had asked this question in another thread. I had given you a solution there or at least an avenue to explore. The problem with your formula is you are using only row match. You have to use both row and column match and remove all if conditions. There is no need for such big and confusing formula to populate values in your column M. I have used normal index match to get the results in column O. find the attached.

Thanks

 

[Felix]

Hi:

You had asked this question in another thread. I had given you a solution there or at least an avenue to explore. The problem with your formula is you are using only row match. You have to use both row and column match and remove all if conditions. There is no need for such big and confusing formula to populate values in your column M. I have used normal index match to get the results in column O. find the attached.

Thanks

Thank you. I will learn this formula by heart. While it seems very easy for you ninjas, it's not for me. I will have to use it more frequently.

 

[Felix]

Let me try and answer the two highlighted points for you.

The document needs a thorough review, if you want someone else to fix it for you - it's akin to me starting to build a kitchen, stopping halfway through with bits everywhere and asking you to finish it, but not change the work I've done. If you don't want to clean it up, it will be harder to get it repaired - not impossible, but harder. Nobody wants to be liable for the logic in the IF statement for starters - they're tedious to test and check. And there's more ... but i think you get the point

I can't tell you for certain what the effect of VBA will be on the file; i suspect not a lot while they are still functioning, but let's see if someone takes up the challenge of writing the code. However, they are going to need a lot more information about your file locations, file names (which you won't know in advance, unless you've developed a solid naming convention) where you want to store the summed amounts, do you just want the total of the two cells through X worksheets, or do you want/need a summary of each two cells and then a Total Line etc. ....You've addressed none of these, which probably explains why there aren't any takers for writing the code - there are far too many unknowns

They'll want to be sure that you can maintain the code, as it will need maintenance on occasion ....

Thanks for your reply again. I am sorry I have caused so much confussion. I do have a specific folder to save my files into.
The original SS that you have been helping me with is named "ORDER", once a sales rep fills up all of the fields, he saves as (with the customer's name) into that specific folder (C:\Users\NISG\Desktop\Felo\Docs\Excell\P & L)
NOTE: The sales rep will not be able to mess with any of the formulas at all.

...and what I'm trying to accomplish is that, at any given day I open this folder, then execute a SS by the name of "P & L" that has the ability of adding in cell B3 all of the values of "N11" in the customers' SS, as well as adding in cell C3 all of the values of "O11" in the customers' SS.

I have the following, but I don't know how/where substitute with my info's

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
  
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
  
    'Change First Worksheet's Background Fill Blue
      wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)
  
    'Save and Close Workbook
      wb.Close SaveChanges:=True
    
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[NARAYANK991]

Hi ,

The code which is executed each time a file is opened is :

    'Change First Worksheet's Background Fill Blue
    wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)

    'Save and Close Workbook
    wb.Close SaveChanges:=True

This is the part you will have to replace with your own code.

Suppose we declare two variables cellB3 and cellC3 as follows :

Dim cellB3 as Double , cellC3 as Double

The above line of code can be at the beginning of your procedure.

The code that will replace the two lines mentioned earlier will be something like :

cellB3 = cellB3 + wb.Worksheets(1).Range("N11").Value

cellC3 = cellC3 + wb.Worksheets(1).Range("O11").Value

Then , immediately after the Loop statement , insert the following lines of code :

With ThisWorkbook.Worksheets("Sheet1")
          .Range("B3").Value = cellB3

          .Range("C3").Value = cellC3
End With

Narayan

 

[Felix]

Thank you Narayan. I will try that, and give you the outcome. I'm already enjoying the VBA. I dont know what took me so long to get into it.