Posts by Peixe

    Re: date range dependent on current date

    I'm not sure you're context, your message reads confusing, but here's an example of comparing the current date with the date in another cell. In this case, if the current date is greater than the date in cell A1, then it displays the note, otherwise, no note.

    Date Compare Formula

    Here's your note

    Spreadsheet Formulas

    Re: Excel Template Location

    Quote from Leonard Lutz;659010

    Thank you for the reply. I should have mentioned that I'm using Excel for Mac.

    Sorry, can't help you there. I'm a PC, a Mac is an Apple and PC's eat apples, not play with them. :) hehe Just kidding around, I'll have to see what I can find about where Office for Mac stores things. I'm already late going home, so it'll have to be tomorrow.

    Re: Excel Template Location

    On my system they are located here....

    C:\Documents and Settings\LocalAdmin\Application Data\Microsoft\Templates

    However, on your system they could be located under a different user, so look under all folders under Documents and Settings, then continue looking for Application Data\Microsoft\Templates

    Keep in mind that Application Data is a hidden folder, which is probably why the Search is not finding it. Make sure your system is set to "Show hidden files and folders" or you will not be able to find it either.

    Re: VBA code to identify non matching text

    I was playing with this some more today and thought you might find it more useful as a function.

    You can use it in a formula like this.

    Cell A1: I am a boy
    Cell A2: I m a boy

    Any cell: =RevDiffStr(A1, A2, True)
    Would produce result: "am a boy"

    Any cell: =RevDiffStr(A1, A2, False)
    Would produce result: "m a boy"

    The true or false is to determine which of the two strings to produce the result from. True uses the first string, False uses the second string.

    or you could also use it in other vba code like this....


    Re: VBA code to identify non matching text

    Quote from Deino;657516

    Cell A1 = I am a boy
    Cell A2 = I m a boy

    I am looking for a code that identifies and displays in another cell or highlights "m a boy" is the text that is not matching.

    This may not be pretty, but it works as described. Starting with strings in A1 and A2, this will put the result in B3.

    Re: VBA and Tablet Orientation

    and here I thought I was just going to post something brilliant because I got this to work. LOL I'll post it anyway, but your way is probably better.....

    Using the vb6 initialize code as posted previously, but calling the form in the following fashion.

    Re: VBA and Tablet Orientation

    This doesn't work the way it looks like it would work, but I thought I'd post it to maybe spark some more thought on the subject. The intent being that it would load the form, and while the form is loaded, every 200 milliseconds it would check the orientation while the form was loaded. However, it did not work as intended and never fully displayed my form. If there was a way to truly run code in the background, then something like this might work and let the form do its job. In this case, I just unloaded and reloaded the form, however, you'd probably just want it to do your form resizing. That is, if we can figure out a way to make it work in the background.

    Re: VBA and Tablet Orientation

    No, even the one was an onLoad event.

    Private Sub UserForm1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)

    I'm sure that anything else than this would require API calls and checks every so many milliseconds, which would be far beyond my expertise.

    Re: VBA and Tablet Orientation

    Rather than using the code, I used the vb6 code provided further down the same page.

    With a minor tweak, I got it to work. Although, I do not have a tablet, so you'll have to test it to see if it works. The way it looks, it will work if you load the form, but will not change if the form is already loaded.

    Re: Run SAME code for different ranges using BeforeDoubleClick event

    Quote from Musoguy;657749

    Peixe, that worked great. I'd actually attempted to do that, but had placed quotation marks around each named range, not just one set around all three so it didn't work.

    When working with multiple range, all ranges should be encompassed within a single set of double quotes.

    For example:
    To cover A1 thru A10, B2 and C1 thru C10 it would look like this.

    Range("A1:A10, B2, C1:C10")

    that would cover everything. If add quotes and commas outside of the quotes then you can only have a starting and ending cell range.

    IE: the range A1 thru A10 would be like this

    This is the same as saying Range("A1:A10") except you are limited to only that range. Whereas including everything within a single set of double quotes, you can have many multiple ranges covered within a single range statement.

    The same apparently applies to named ranges. I don't use them, so I just took a logical guess. :)

    Re: Run SAME code for different ranges using BeforeDoubleClick event

    Quote from Musoguy;657733

    However I also need the code to work on two other named ranges "Checkboxes2" (E4:E100) and "Checkboxes3" is G4:G100.

    Hoping someone can help :)

    I don't work with named ranges, so this is untested, but in theory, you should be able to include them all as one range. It's the same as you would without naming the ranges.

    Re: Sort entire table eachtime after entering new data record at the end of the list

    A simple way to do it is when the last row of column AD is updated, then sort. You could also add in validation to ensure all columns were filled in.

    Re: Pull Data from sheet 2 and 3 to sheet 1 if data in column a matches

    What is the name of the 3rd sheet? Is it just Sheet4?

    As your vendors grow, this method can quickly become obsolete. You can only nest 7 IF statements I believe, so it might be better to merge vendors into one sheet with the ability pick out a single vendor.

    Let me know what the new sheet name is and I can update the formula or if you want to consider another route, that's ok too.

    Re: Pull Data from sheet 2 and 3 to sheet 1 if data in column a matches

    I can get you half way there. This formula works for row 2, just paste to row 2 and copy down.....

    =IF(ISNA(VLOOKUP(A2,Sheet3!$A$2:$B$26,2,FALSE)),VLOOKUP(A2, Sheet2!$A$2:$C$82, 3, FALSE),VLOOKUP(A2,Sheet3!$A$2:$B$26,2,FALSE))

    Its only downfall is if the part number isn't in either sheet2 or sheet3 it'll give you #N/A.

    I was trying to account for N/A on sheet3 also, but kept getting "too many arguments" and ran out of time to figure out why as it's time for me to go home, but at least you're half way there.

    Couldn't leave with something unfinished, so here's a formula that will work all around.

    =IF(ISNA(VLOOKUP(A2,Sheet3!$A$2:$B$26,2,FALSE)),IF(ISNA(VLOOKUP(A2, Sheet2!$A$2:$G$82, 7, FALSE)), "", VLOOKUP(A2, Sheet2!$A$2:$G82, 7, FALSE)),VLOOKUP(A2,Sheet3!$A$2:$B$26,2,FALSE))