Posts by Rowddawg

    Re: Clean up/check vba code created from merging existing code


    Roy,


    First, thanks for the reply.
    I realize (and mentioned) that I know this isn't going to keep people out. It was more for me to see if it could be done and what I did correctly or incorrectly making it. Very new to vba, so this was my first attempt to create something semi-independently, with the assistance of what I found on the forums. I'm learning a ton by reading all the posts here and searching how to do new things. Really just want to know what's wrong or should be tweaked with it.


    If I ever did use it, the users I'm dealing with wouldn't even realize it was a date issue, let alone think about changing the computer's clock, but you are very correct, that would be one work around. As for the fixed date, why would that make the code time consuming? The reason it is there is just to cause a trigger that would start the event. Say the sheet expires at the end of a fiscal year where new data will be, so the old one can't be used and they are forced to use a new one.


    THanks again for the earlier reply.




    Re: sum if no blanks in matching cells


    =IF(AND(ISNUMBER(D1),ISNUMBER(E1)),SUM(D1:E1),"")


    Copy this down your list in column f. It will populate the sum when d and e are numbers, when they aren't, it will leave it blank.


    I'm assuming this is what you're looking for.

    Sorry if this sounds silly, and I know it's not a lot of code, but I wanted to see if I could create a macro that would, based on a specified date, effectively lock a workbook with a random password. I've pieced together the parts to do it, and it works. What I want to know is, does it look ok/efficient and if not what changes could be made to clean it up. The random password part came from Dave Hawley's reply to someone's post about generating random numbers/strings here on Ozgrid, so I'm pretty sure that code is fine. If I should link to what I found of his, I can certainly add that and I apologize for not doing so already,would just have to find it again. Just curious about how I merged everything.


    Few points:
    1) I know that this would never keep someone that knew anything about vba or was persistent enough out of a worksheet...
    2) I know the end throws out a msgbox with the password, would obviously take it out if I ever wanted to use it.
    3) This was more for me to learn how to 'nest' functions and work to an end than what the result is.


    Thanks in advance for any help.


    Re: Formula to calculate number of working days left in month


    Sorry for not getting back to you sooner. Long working weekend. Glad you found a solution that works. As for the holidays, maybe setting all holidays in a list with their actual dates and setting up a range that, when that date falls within the month, it subtracts one day? Just a thought. Will try to work something out in excel in a bit... Really need 26 hours in a day.

    Re: Formula not working


    silly question, and might not help, but have you tried stepping into the formula to see what excel says is happening as each if is evaluated? Probably not an issue since the formula looks good, but without it being a format issue just wasn't sure...

    Re: Formula to calculate number of working days left in month


    =EOMONTH(TODAY(),0)-TODAY()+IF(WEEKDAY(EOMONTH(A1,0),3)>5,-2,IF(WEEKDAY(EOMONTH(A1,0),3)>4,-1,0))


    This takes care of the last two days issue, subtracting 2 days from working days if the last day falls on a sunday, and 1 if it falls on a saturday, like this month.
    You could probably add the last section
    +IF(WEEKDAY(EOMONTH(A1,0),3)>5,-2,IF(WEEKDAY(EOMONTH(A1,0),3)>4,-1,0)) to what was listed before and it should correct the ending days issue.


    using 0 for end of month means weekdays fall from 0-4, so 5 is saturday and 6 is sunday. All my ending does is delete 2 days if it's a sunday, and 1 if it's a saturday, but that fixes your issue with the earlier formula...

    Re: Getting the top 3 value within different sheet using a button in Macro


    Sounds like this is a class assignment maybe? Either way what you need and why you need a macro isn't well detailed. If you only need the top 3 values from each worksheet, 3 formulas in each sheet will get you that, no matter how many columns/rows you have... =LARGE(array,k) is one function, where the array is the data set you're pulling from and k = what number you want (1 = largest, goes down farther as you increase number)


    So for example you could, on worksheet 1 put
    =LARGE($c$1:$c$1000,1) into one cell,
    =LARGE($c$1:$c$1000,2) into another cell, and
    =LARGE($c$1:$c$1000,3) into a third cell and those cells would give you the top 3 numbers in that column c, regardless of how many columns you had. Then you could have the third sheet reference those cells.


    Again, as jindon said, you wouldn't need a macro. Different functions can already handle that.


    If, however, you had to use one, large is in the list of worksheet.functions that can be used in vba...
    http://msdn.microsoft.com/en-u…f822194(v=office.14).aspx

    Re: Find duplicate values in multiple sheets


    Can you attach the worksheet that's adjusted? That way I can see exactly what you're talking about and better help. I'm assuming that when you're moving them it's changing the reference point and that's why you're getting different results, but with the sheet I can give you a specific answer.

    Re: Find duplicate values in multiple sheets


    https://www.dropbox.com/s/dq9m…013%20Cust-samplerev.xlsx


    See if this is what you want for the highlight... added a column basically doing the same thing as in the first worksheet, but in reverse, then highlighted the row based on if the result was a row number using conditional formatting. For that, I added a format for the C column on the second worksheet, then applied it to the first three cells.


    In case you're wondering about the +1 or +375 in the formulas, that just offsets the row that the tables start on.


    Let me know.



    Anyway, see if this is what you want.

    Re: Find duplicate values in multiple sheets


    If you paste =IF(LEN([@Host])>14,LEFT([@Host],(LEN([@Host])-13)),[@Host]) into a column in ocptabvhost, it will give you the name, or the ip address if the name isn't listed. Then on the other (first) sheet you add =MATCH((LOWER([@[System Name]])),Table2[Column210],0)+1, and it will give you the row number where the item is on the second sheet.


    Then you apply a conditional format to the sheet to add whatever color you wanted


    Hope this is what you're looking for.

    I have a worksheet with a row that has pars of data in it, c4:z4. I want to add a macro that selects that row, but pastes the values into whatever the active cell is, so I can copy into the following row to look for trends.

    I'm thinking this has to be simple, but I'm missing something obvious. I've tried using the recorder to do what I want, and adjusting it to ActiveCell instead of the selection that I picked to paste into, but it doesn't seem to work.


    Thanks in advance for any help. Attached is what I've tried... Thinking I've butchered it somehow, maybe multiple ways...


    Re: Recurring projects in a week


    Here's what you can enter for just one cell, i.e. project a... will have to redo for other projects, but will sum all of that type. =SUM((IF(ISNA((IF((MATCH("project a",$C2,0))=1,B2,0))),0,B2)),(IF(ISNA((IF((MATCH("project a",$C3,0))=1,B3,0))),0,B3)),(IF(ISNA((IF((MATCH("project a",$C4,0))=1,B4,0))),0,B4)),(IF(ISNA((IF((MATCH("project a",$C5,0))=1,B5,0))),0,B5)),(IF(ISNA((IF((MATCH("project a",$C6,0))=1,B6,0))),0,B6)),(IF(ISNA((IF((MATCH("project a",$C7,0))=1,B7,0))),0,B7)),(IF(ISNA((IF((MATCH("project a",$C8,0))=1,B8,0))),0,B8))) since this is repetitive, i'm sure it could be done much more cleanly with an array. Just not sure best way to write that.