Posts by Joe Derr

    Based off of the current thread:

    I borrowed some of the formula to help rectify my problem, however I need to add certain # of days depending on what day of the week it is. Here is what I have so far.

    =IF(WEEKDAY(M7)=4,M7+5,IF(WEEKDAY(M7)=2,M7+2, M7+1))

    M7 is a date entered by the user, This formula is in N7.

    If the date is a Monday or a Wednesday, the formula works...

    If its a Tuesday, it works (M7+1).

    However if the date is a:

    I need the formula to determine that and add the appropriate # to bring it back to that monday.

    For example, person enters 16 Aug 07 (Thursday) it needs to add 4 days to bring it back to display 20 Aug 07.

    Any ideas, I think I would run out of IF statements for this formula.


    I have a problem, I searched the archives, I thought the answer or close to it would be in there, but after searching multiple pages, and alot of actual repeat posts, I couldn't find it.

    I have a workbook (open) that I update...

    at a press of a button on the userform, I want to update a (closed) workbook with the data from the (open) workbook.

    I have the code below that pulls in the data from the (closed) workbook to the (open) workbook for updating, now I just need to re-write it back to the closed workbook.

    I retrieve from the closed workbook range E10:G24 and then I update it on the (open) workbook and now I need it to update the closed workbook at the press of a button. Button 1 = Retieve data (see code above)
    Button 2 = Update Master (need help on this part)


    Re: Data Validation Multiple Columns Data

    Thanks, I can get it to work sometimes, sometimes it just selects the bottom most data to.. your's works, its mine that I am having the problem, that is exactly what I need it to do, thanks again!

    I have a question about data validation drop downs.

    I have 2 columns that need to be filled in once a name is selected from the data validation drop down. The data is to the right of the name in the source area.

    Column A Column B Column C
    1 Name 1 Skill 1 Phone #

    This is the format, Every row in Column A has a Data Validation Drop Down, I need Column B and C filled with Data from the selected name from Column D (the data columns D-F)

    Column D Column E Column F
    1 Name 1 7 555-1111
    2 Name 2 5 555-2222
    3 Name 3 3 555-3333
    4 Name 4 4 555-4444

    So when I select say name 3...

    Column A Column B Column C
    1 Name 3 3 555-3333

    Is this possible? If so, can some one enlighten me?


    Re: Trim Month Year For Conditional Formatting

    LOL.. don't know if that is a good thing or a bad thing.. I put in the code late last night and it was still showing the same way.. I probably was missing something.. I am going to look into again here soon, got some sleep :)


    I changed how the textbox was initially populated to mm/dd/yy format and it works. If it could goto the mm/yy it would be the best, but this works just fine and *Thank You* very much!!!:cheers:

    Re: Trim Month Year For Conditional Formatting

    I will give that a whirl.

    The reason I am storying in month/year is for the expiration of the expense card eg 03/08 with credit cards I don't know if it matters if its the 1st of the month or the last day.

    I am going to try this and then head home, I will post more tomorrow.

    I want to say a big *Thank You* for all your help!


    Re: Trim Month Year For Conditional Formatting

    The application is a card file program, contact and information on my employees. The date (textbox24) is for their expense account card, the expiration date.

    What I am attempting to do is, when that date is overdue say 01/07 the textbox will be red, when its within two months ie 04/07 it will be yellow and when its greater than 04/07 (three or more months from being due) it will just be plain white.

    That is what I am trying to do.. what its doing is I have 3 test dates


    The 01/07 is red like its supposed to be
    The 03/07 is yellow like its supposed to be
    The 03/08 (over a year out) should be white, but it also is yellow.

    Simple, but yet so not working :)


    Re: Trim Month Year For Conditional Formatting

    *scratching head*

    Well.. not sure.. I will have to put it down for the day and go back to it tomorrow, my brain is fried. I have been at this all day trying one aspect to the other. I will re-attack tomorrow.

    The below code is how I get the date mm/yy style into the textbox24..

    I just tried to assign a variable to Textbox24, but it didn't work..

    ' Expiration Date
     ExpirationDate = Worksheets("Database").Cells(userow, usercolumn + 36).Value
     ExpirationDate = Format(ExpirationDate, "mm/yy")
     TextBox24.Value = ExpirationDate

    *reaching for asprin*


    Re: Trim Month Year For Conditional Formatting

    If you msgbox CurrentDate it comes out to 02/07/07
    If you msgbox MonthPlus it comes out 04/07

    MonthPlus is correct (as in month and year only)

    I would like CurrentDate to do the same (month and year only)

    I think it might be the reason I can't get the conditional formatting right...

    Red is less than current date (that works actually)
    Yellow if its greater than Currentdate but less than MonthPlus (works)
    White if its greater than MonthPlus (doesn't work)

    Having trouble with a date format:

    I am trying to get CurrentDate from a 02/07/07 to 02/07

    My CurrentDate = Format(CurrentDate, "mm/yy") doesn't seem to work, no matter what CurrentDate = mm/dd/yy

    My eyes are watering from stareing at the screen for so long....

    Any help would be apprciated...

    I am stuck, I am trying to take "currentdate" which is formatted "mm/yy" to add two months to the "currentdate"

    monthplus is my variable to get the currentdate plus 2 months. I have tried various solutions, none work, this will all have to be done in vba, as writing to a sheet isn't practical. I tried to use "evaluate" to put a formula in vba but it wasn't working.

    A combobox is used to fill the textbox (eg the userow and usercolumn)

    the formatting below that is just to change the textbox different colors if the date is older than today (red) within 2 months (yellow) and further out than two months (white)

    Re: Refresh Web Browser

    Taking this to a different level, changed the way the file is read, instead of reading a text file from the website, and putting into a query table, I am loading the webpage as a excel file and reading in the code from A1, then copying it to my workbook Data sheet Y2, then comparing the numbers.

    However, the webpage isn't refreshing, the code above with the refresh statement errors, the Reference is set.. can anyone get it to work, and maybe post and example file?

    Re: Vba Formula Variable

    Norie & Tom,

    Its working now, the formula part, once I plugged in Norie's code for the formula, but here is the code so you can see it..

    I am off to see about why I can't get it to update the webpage, I can change it, but it will show the old version # until I manually go in and refresh the webpage. I guess to test, you can change the path to a local file?! It will seem to work for you until I can get home to update the file # again.

    As said above to Tom, I am going to post in a new post.

    Re: Vba Formula Variable

    does it matter if the variable is dim'd as an integer ?

    This is trying to check for a new version from the website (another post)

    I have a routine that removes the dots once the .'s are removed, say would be read into this formula as 5895

    This formula then would add a fifth digit, so it would be 58950 and it does this because the version could expand to 5 digits eg. so it will be 58951 compare that to 58950 and you can see the first one is more current..

    getting off track..

    Does it matter for the Evaluate if its an integer or ??

    Re: Vba Formula Variable

    Thanks.. I changed the Variable Name and didn't Dim the new name..

    Any idea on how to either Dump a webpage, or refresh it??

    I will probably start another thread so It will follow the title correctly.

    I am trying to limit the amount of stuff I write to a sheet, and I figured I can just pass variables around inside of a routine with out having to write each step to a sheet like I am doing now.

    Below is the formula showing Z2 as where parsed data is

    CVersionFormula = Evaluate("Z2 * 10 ^ (4 - Int(Log(Z2)))")

    and here it is, with me trying to plug in a variable rather than read it from the data sheet. The variables for ease are read from the data sheet. In this example I would read in Z1 and assign it "CVersion", then the formula then takes CVersion (a 4 digit number) and makes it a 5 digit number..

    CurrVersionE = Evaluate("CVersion * 10 ^ (4 - Int(Log(CVersion)))")

    But I don't think I have the variables in there right.. Ideas ??


    Re: Refresh Web Browser

    I found that the reason for the error was the that it was calling a .htm file but the qualifier (if you will) was document.title....

    Take a look here..

    However, it still doesn't refresh the web-browser, thus my query table still doesn't show the new data.. any ideas.