Posts by jronaldlee

    Ha! Just enough to be dangerous. A bit of my education hanging on by a thread.

    This feels like a weird gap on Microsoft's part... they offer the NPER function, but the majority of banks these days accrue interest more frequently than the gap between payments. Shouldn't be hard to build out, IMO... but I don't feel like going through building a custom formula so here we are.

    I can use that for total compounding interest accrued, but it would assume no payments made. I could do a table to build out the loan with number of days, compound the interest, and reduce the amount of principal for each period (and get the number of periods that way), but I am hoping to find the equivalent of NPER that uses daily compounding when considering how many payments will be needed. If NPER is doing this, I'm getting some funny results and need to reconsider my inputs...

    Howdy, folks. I'm familiar with NPER, but I've come to realize that it's compounding interest based on the number of periods, whereas my bank is compounding interest daily. Is there an alternative formula that will calculate the number of periods remaining on a loan that will allow for interest to compound daily, and recognize that payments are made at a different time scale (e.g., biweekly or monthly)?

    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:

    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

    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,


    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?