Posts by jronaldlee

    I've inherited some spreadsheets that are a formatting nightmare. I tried to script away the fills, fonts, colors, borders, and such, but sometimes there are so many different things on the sheet that Excel balks.


    Next I manually copied and pasted the data as values into a new workbook, I can reduce the size by 90%. The problem is there's a lot of data and it's not all continuous (multiple tables on each sheet, different size ranges on each version of the workbook, etc).


    I have a script that creates a new worksheet and brings EVERYTHING over like this:


    Code
    1. Sheets(WSn2).Range("a1:cz100000").Value2 = Sheets(WSn).Range("a1:cz100000").Value2


    But if I use that hammer to do it, the workbook is referencing all these cells as having values even though many of them are, in fact, blank. I'm not getting as much size reduction as I would like.


    Is there a better way?

    I have a large cache of legacy documents that were stored with someone using the insert date function of Word. Now, whenever one opens these documents, the field reverts to today's date. I understand the value of said functionality, but these documents should represent with accuracy the create date, not today's date. I know how to update the field to show this manually, but I'm wondering if there is a way to force the issue.


    In other words, If date field is present, change value from Today to Create Date.


    Is this possible? Automatic would be preferred, but a button tied to a macro stored in personal would work.

    I have a macro that generates TXT files, but the encoding is not useable by my downstream systems. This code generates documents which Notepad++ identifies as UCS-2. Only ANSI can be read by my downstream system.


    Here's what's giving me the wrong format:


    [VBA]Set fso = CreateObject("Scripting.FileSystemObject")
    Set FileOut = fso.CreateTextFile(FileName, True, True)
    FileOut.Write MetaData 'Metadata is a previously established text string variable
    FileOut.Close[/VBA]


    Thanks in advance...

    I can wrap my ahead around opening another document, grabbing data from it, and bringing it back, but I've only done that where I've been specific as to which sheet to get the information from. Now I need to open another workbook, and loop through every sheet, collect the data in A1:E30, and bring it back to the spreadsheet from which I'm running the data and add it to a table.


    • I don't quite have a handle on how to set up the loop
    • I also need to grab the sheet name and bring that over.


    The data I'm inheriting is spread across 100 sheets instead of one table, and the sheet name is part of the data - yuck. Any suggestions? Thanks in advance!

    Re: Mail Merge output to named individual files


    Figured this out. Here's the code I'm using, which is a little sloppy and inelegant in particular when it comes to the total count of records for the for/next loop. I'm manually entering that in the mail merge data file. If anyone has a suggestion on how to count the number of records and just use that, I'd love to incorporate it.


    I need to send emails and have the "Direct Replies To" field updated to a different address.


    This is my code. I've remarked what I tried, which is NOT working.


    I'm working on a macro that uses the contents of the clipboard. The text string has many fields in quotations, and Excel is stripping the first two quotes when I paste the data into the spreadsheet.


    For example, if the text string in the clipboard is:
    "Sample One" or "Sample Two" or "Sample Three"


    The user starts with this text string in their clipboard, then fires the macro. When the text string hits the spreadsheet (via ActiveSheet.Paste), the value changes to this:
    Sample One or "Sample Two" or "Sample Three" - the first two sets of quotes are stripped.


    My macro uses the quotes to convert this into a table of the three text strings in quotes. I'm wondering if there's a way to get at the contents of the clipboard without pasting it onto the spreadsheet? If so, I could put an apostrophe in front of the text string and make this thing work.


    Thanks in advance,


    James

    Re: Find value w/VB creates error, ignoring "On Error Resume Next"


    Your code is working flawlessly. Much cleaner, too - thanks very much. I learned something new today!


    (Re: two o's, I somehow deleted the hard return from one line to the next when I pasted the code. I edited the original post to display it as it was in my code)

    I have a bit of VBA that I've used on multiple occasions. I look for a row of data in a table, and if it's not there, I add it. If it is, I overwrite the data. Here's how I do it:



    I've used similar code before with great success, but for some reason I'm now getting "Run-time error '91': Object variable or With block variable not set" and it's the DataFound = Columns(1).Find... line that is causing the error. I expect it to error... the data isn't there, right? But that's why I have the line of code "On Error Resume Next" there. Very confused as to why that isn't happening.


    Any ideas?

    I use a series of formulas and user inputs to generate a series of text entries which will then comprise the body of an email. This is the code that is working for me at present:



    In the for loop, there are some lines that I would like to appear as bold text in the email. I can detect those lines easily enough; it's formatting them in Outlook that is the challenge. Any suggestions?

    Re: Calculation of Breach


    If 8 am to 6 pm:


    =B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>10,AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),14/24,0)


    If 8 am to 7 pm:


    =B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>11,AND(TEXT(B10,"H")*1=11,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),13/24,0)


    I changed the hour marker in two if statements from 10 to 11 because 11 am is now eight hours prior to closing time.


    Add the following to stop weekend calculation:


    +IF(AND(A10=3,TEXT(B10,"DDD")="Fri"),2,0)


    All that does is add two days if the ticket is priority 3 and opened on a Friday.


    You're still going to have trouble with holidays in that scenario. In the past, I've solved for holidays by having a table of holidays, and using a VLOOKUP function to see how many days the office would be closed (a table of dates and how many extra days to add). If the open date is before a three day weekend, I add 1. If it's the day before Thanksgiving, add 2.

    Re: Calculation of Breach


    1) It changes the formula, yes. If you were opening at 8 am instead of 6 am, you'd need to add 14 hours instead of 12. The part where you add 0.5 (twelve hours) would need to change to 14/24 (14 hours) if you open at 8 am, or 15/24 if you open at 9 am. The numerator is driven by how many hours from when you close to when you open.


    2) Priority 4 would complicate in two ways. First, the IF statements at the beginning of the formula would need another layer to account for the amount of time being added. Second, if you're saying the SLA is greater than the amount of business hours in the day, you're adding additional complexity. Consider the request that comes in at 5 p.m., it's not going to be due until the day after tomorrow. If you plan to go this route, I would simplify the whole thing by creating a more robust table with priorities and logic built in to determine how much extra pad you have to add to the new priority. You can then use VLOOKUPs to bring in the extra time by priority. Your formula would be much simpler; something like this:


    =B2+VLOOKUP(A2,SLAs!$A$1:$B$5,2,0)+IF(VLOOKUP(A2,SLAs!$A$1:$C$5,3,0)>TEXT(B2,"HH:MM"),0.5,0)+IF(VLOOKUP(A2,SLAs!$A$1:$D$5,3,0),0)>TEXT(B2,"HH:MM"),0.5,0)


    VLOOKUP 1: the amount of hours each priority has
    VLOOKUP 2: if the ticket is after the cut-off, add 12 hours so we know we can finish it the next day
    VLOOKUP 3: if the ticket is at a different time of day, we add another 12 hours (the 5:00 pm example with a 16 hour service level)