Posts by kmobile9

    Re: Renaming Excel Tab with Year to Date Range


    I had a funny feeling that might be the case. I'm not sure how to code the macro to truncate the date before adding it to the tab. Ideally, the macro would take the date range, yank the year out of it and name the tab, "YYYY YTD", which is ultimately what I have to do manually right now. I'm including the full macro code (which I'm in the process of rewriting to the newer versions of Excel since it was originally written in 2005) so that you can see the full process of the code. Also attached an example of what the end result should look like for reference.


    The macros is decidedly antiquated, hence the need to update the code, but if I can get this tab to work, I'll at least get one bugaboo sorted.


    Hi All,


    I have a macro that formats my daily sales and works great - for the most part. My issue comes up when I need to format the tab for month to date sales. I have a line of code that tends to fail if the date range gets too long. For example, if my Year to date sales range is January 1 through September 30, the macro fails at the code line to rename the excel tab to the date range. The code I am using is:


    Code
    ActiveSheet.Name = Format(Range("A3").Value,"[$-510] d-mmm;@")


    To provide background, the data in my spreadsheet is a year to date Sales report exported from QuickBooks to excel for additional manipulation and analysis. The macro runs just fine when the character length of the month at the end of the date range is shorter, but for the longer names, it fails unless I change the word "through" to "to". Is there better code I can use to have the macro rename the tab based on the date in cell A3?


    Thanks for all help and suggestions.

    Re: Macro search for text and highlight row


    The reason for highlighting the rows is so that I can find them to delete them. The spreadsheet that I receive is from my external sales team, so the report is often very long and, frankly, I just don't feel like scanning a report for rows that I need to delete when I can have the macro, which is already running to find fields that I need to edit to correct misspellings, etc. also highlight rows that are going to create duplicate records. The spreadsheet ultimately gets imported into QuickBooks and the wizards at Intuit are aware that QuickBooks has no duplicate record filter for excel imports, nor do they plan on creating one, so this is the work around I've had to develop.

    Hi all,


    I'm refining the code on a macro I posted a couple of weeks ago, and one of the issues I'm encountering is how to have my macro highlight a row in red for the text phrase "Winery Direct", when the cell contains more than just these two words. Unfortunately, I'm at the mercy of the sales team who sends me the data file, so sometimes they will send the file and the columns containing only text, but at other times they'll send it with text and dates mixed in this column. The purpose for highlighting this row is so that I can capture it and delete it before importing into my financial software since a Winery Direct has already been created and so this line would create a duplicate record. Make sense?


    I know that when the cell contains only "Winery Direct", the highlighting works, but when it contains mixed data, it doesn't so I'm certain I need to add something like a string for contains, but I'm not advanced enough in VBA to know how to code for that. Any advice or pointers is most welcome.


    Re: Conditional Macro to pull No Match Customer Names


    So I've made the corrections that I missed on Friday and this is probably because it's been so long since I had to rewrite a macro, but I'm still getting a VBA error 424: object required at the same line. Have I written my range declaration incorrectly? The idea is for the macro to search in column f for customer names then match them to the QB customer list and, as the comment notes, flag them without a match in a separate tab. I have my original notes from when the original macro was written in 2009 so I'm assuming the code may have changed somewhat in the following 7 years, possibly making me a newbie again.


    What am I missing? I've edited the line as follows:


    vDataOne = .Range("f1:f" & .Range("f1500").End(xlUp).Row)


    Any inputs/corrections would be most appreciated!

    Hi All,


    I had a macro decide to stop working today, so I've spent the day rewriting it and am in the testing phase. For the life of me (probably because I've been staring at it all day), I can't figure out why my macro is hanging at the following point in underlined red (see code pasted below). The goal of this macro is to compare a text file that I receive from my sales broker each morning with customer account sales in it and compare to my customer name spreadsheet BEFORE I import it into my accounting software, then identify those customer names that don't match my customer names in my accounting software. My accounting software is the master file for my customer name spreadsheet and is updated monthly. The macro is embedded in the customer name spreadsheet. A fresh set of eyes to look at this macro and tell me what I'm missing would be welcome because I can't get this blinkety-blink-blink thing to work.


    Thanks


    Karen