Posts by bpdarrow

    Update: I've partially solved my problem but am stumped at the error I get now.

    I now get the RunTime Error 1004 application defined or object defined error when I get to this line:

    Code
    1. Range("NewChange[[#Totals],[Death as % of Total Cases]]").FormulaR1C1 = DeathCalc

    I am completely baffled, because it is the exact same syntax as I used in the two lines above it and those work 100% perfect. What am I doing wrong??


    I have a macro that adds a 2 new tables to a sheet, a new table for each day to the right of the previous day tables (the 2 daily tables are vertically stacked). There are total rows in the new tables, and I need to insert a formula that calculates the difference between one of the total columns for the new day and the previous day's total. I recorded a macro that I planned to edit to include variable references rather than fixed references. I'm now stuck at how to address the second part of the formula below:

    Code
    1. Cases051920[[#Totals],[Death as % of Total Cases]]

    The "Cases051920" table name is the variable. Each day, it will be a different date. Today's is "Cases052020", and tomorrow's new table will need to reference this table - and so on. How do I turn this into a variable so the macro can insert the formula so that it references the correct prior day's table???


    This is the recorded bit of code that I'm trying to adapt:

    Code
    1. Range("NewChange[[#Totals],[Death as % of Total Cases]]").Select
    2. Application.CutCopyMode = False
    3. ActiveCell.FormulaR1C1 = _
    4. "=NewTable[[#Totals],[Death as % of Total Cases]]-Cases051920[[#Totals],[Death as % of Total Cases]]"

    Ohh thank you so much. This is so close, I just need some help adapting it to my scenario. There are red cells in various columns, but I only want to sort color based on Column M and then ascending number in Column L next. And rather than loop through all sheets (there are 5 total) I only want this specific sheet. I think I've done that with requiring the ws.name to be the correct sheet name before applying sort. Here's what I have so far.


    I'm planning to call this private sub from my main macro rather than integrate the code into my existing macro. Is that a good idea?




    I've spent wayy too long searching for why my code isn't always working. The scenario is that I have a sheet (VGP) with Columns A:U. The number of rows is dynamic each time I use this. I need to sort the entire range by the cell color in Column M (RGB 255,0 ,0) so that they appear at the top of the sheet first. Then, I need Column L to sort the numerical values in ascending order. I have 2 bits of code in my macro that work with varying degrees of success. This first section works sometimes. Then doesn't. It will have Column L sorted in numerically ascending order, but Column M will be all over the place in whatever order Column L is in.


    So I turned to trying to sort by the cell color, because the cells that populate with "CG Trip Risk" in Column M based on the formulas in that column fill with a red background. I end up with a similar result, where it doesn't seem to be sorting by cell color first. The second section of code below is this attempt.


    The last section of code below contains the section above where the sort functions begin, where the formula is input that results in the "CG Trip Risk" string and the red background color. I've also included a sample file containing the sheet and data with how it looks after running the macro currently.


    I've searched all over the interwebs and tried dozens and dozens of different combinations with the same result, what am I missing?! Thank you!


    Files

    Success! Added that to my code, and also added if/else prior to the formatting for the A9 & OTJ sheets and now it all works again! Thanks for your help.



    Code
    1. If Range("A2") = "" Then '//Fills cell A2 with "NO DATA" and prevents formatting code from running if there is no data.
    2. Range("A2").Value = "NO DATA FOR DEALER CODE"
    3. Range("A2").Font.Bold = True
    4. Range("A2").Interior.ColorIndex = 27
    5. Range("A2").Borders.Color = vbBlack
    6. Range("A2").Borders.Weight = xlThick
    7. Cells.EntireColumn.AutoFit
    8. Rows("1:1").Font.Strikethrough = True
    9. Else
    10. '//Remaining code follows

    Where would be the correct place in my code to add this is?


    Here is the part of the code that you helped me put together originally. I have a source workbook with 5 sheets of data, that we filter based on a specific "dealer code", copy the data from all 5 sheets, and paste into a new workbook. Commonly, there is no filtered data under 2 of the tabs for the specified dealer code (A9 and OTJ). In those instances, all that gets copied is the header row which is then pasted into the new workbook. In the attached workbook "Error Test 3", the sheet "A9" is what it looks like when there is no data to copy/paste.


    I have also included an example of the source workbook.


    My thought is to somehow run through each sheet after filtering by dealer code, and if cell A2 is blank, then skip the copy/paste for that sheet.


    OR when formatting the new workbook sheets, first check if cell A2 on each sheet is blank and skip the section of formatting code for that sheet if it is blank.


    Some of the formatting can probably be done to the source workbook, I'll experiment with that. What I'm noticing after digging in further, is that the content error only comes up when there was no data available in the source workbook for that A9 sheet. The headers get copied & pasted to the new workbook, but there is no data. When there IS data there, the workbook opens with no error. Is there a better way to handle the occurrences where there is no data to paste into the new A9 sheet? Where it skips the formatting steps and moves to the next sheet?

    Ok, I'm narrowing in on the section of code that is triggering the issue. I deleted all my code and started fresh with the original code you sent me last month. Then I started adding sections of code back in one sheet at a time until the error triggered again. When I added this section of code in to format the "A9" sheet, this is when I start getting the error when I try to re-open the file. Any thoughts?


    Hoping this is posted in the correct location.


    I have a macro that creates a new workbook and pastes & formats data to 5 new sheets. It has been working flawlessly for a few weeks now. (I have attached a .txt file with the VBA script for the macro that generates the file - it is too long to enter with code tags). Until this particular file that I created today. For whatever reason, I get an error that there is a problem with some content. It only does this for this particular file, no matter how many times I delete and re-generate the file. I've tested dozens of others, and it still works.


    Here is a screenshot of the message that pops up when I first open the file:


    After repairing, a message confirming the repair with a link to the log file opens and this is the text within the log file:


    I've been searching for a while this morning trying to figure out what it's having trouble with. There is no VBA code stored in the file that is generated, and no xml objects (at least that I can find).

    The excel file that I attached brings up the error when I've tested it a few times.


    How do I go about finding the issue that excel is finding?

    I have a set of data that has a column of State abbreviations in it. Each state abbreviation has a corresponding numerical value. I am trying to use looping If/ElseIf to determine the state abbreviation in one column, and based on the value, return the assigned numerical value to the empty cell next to it. With the code below, I get a run-time error 13 Type mismatch with the first IF line highlighted. I've spent most of the day searching for an answer, and the closest that I get is that I have an issue with the text vs. number value and I'm not sure how to solve it.


    I have include a sample workbook with the data that I'm referencing.


    Files

    • Example.xlsx

      (40.83 kB, downloaded 14 times, last: )

    Yeah, it's not there. It's not in the source file to begin with. The 9 master files get exported to a shared network drive from QlikSense through nPrinting that turns them into what you see in the example file. Then based on a request I get the data for the necessary dealer code and create a report for that particular request. I've tried applying all the formatting to the master files but they contain so much data that it sometimes locks excel up.

    I played around with this some more and attempted to add my formatting macros to the script. Unbelievable - but it runs in about 10 seconds including the time to enter the file name in the save as dialog. I tried adding it with code tags, but it's too long. Here's a sample file with it embedded.


    One question - when the save as dialog box opens, it defaults to "All Files (*.*)" as the save-as type and I have to type .xlsx after my file name to get it to save as excel.. How do I get that to default to .xlsx?