Posts by Brister

    Re: Check if File is open already


    Well I tried it, but it does not work. I left the msg Box's to test, and it always says it is not open even though it is. In fact it doesn't even seem to be checking it because it returns too fast. The external Workbook is on the company server, so it takes a minute to even open it, so for some reason it is not looking.


    Any thoughts?

    Hey guys,


    How would I check to see if a file is already open? I have a workbook that automatically opens another workbook and updates a sheet, saves then closes it. The problem is, if it is already open, it is not giving me the in use dialog, just opens a read-only version and carries through. So how could I check with code to see if it is open?


    Thanks ahead of time!

    Re: Deleting images in sheet


    I seem to have gotten it to work. I added A for loop using your ShpTemp declaration:



    Seems to work on both Workbooks now. Since I have a varied number of images from one workbook to the next, I figure I would try adding that loop. Seems to have worked.

    Re: Deleting images in sheet


    I wish I could, but 1)its too big, and it would be difficult to cut it down, because I have it set up to link to another Workbook, which is why I need to delete the images.


    The wierd thing is, the first workbook I did it on worked perfectly, pasted to another (separate workbook) and it gives the error at the same place everytime. Now the only thing I changed was:


    Code
    DeleteShapesInRange ActiveSheet.Range("C11:Q600")


    Now in your code, you have

    Code
    Dim shpTemp As Shape


    but it is not referred to in the rest of the code. does it not need to?

    Re: Deleting images in sheet


    Hey Andy, ran into error.


    The first sheet I did this on it worked great, but when I copied the module and stuff to another sheet, I get an error on this line:


    Code
    If Not Intersect(rng, .Shapes(lngIndex).TopLeftCell) Is Nothing Then


    Any ideas why? The wierd thing is it is written exactly as the other sheet, but still giving an error everytime at same point.

    Hey guys,


    I need to delete a section of a worksheet, for instance C11:Q133.


    Now in between there, I have some images that I need to remove, how would I do this?
    Right now, I need to delete these cells, but when I do, it does not delete the images, just shrinks them so small you can't see them. Is there an easy code to select all logos within a range and delete?

    Re: Select every other row using formula


    Thanks Thomach, this is great, but tthe problem I am having is I need it to count the first cell, but I modified your code slightly:


    =SUMPRODUCT((NOT(ISBLANK(A1:A36)))*(MOD(ROW(A1:A36)+1,2)=0))


    I just added a +1 to the end of the Mod, and it works perfectly. Thanks for the help.

    Hey everyone!


    Just trying to make my life easier, Is there a formula I could do that would take every other cell in a range?


    Right now I am doing a formula like: If(CountIf(A2,A4,A6,A8)=4,"T","F") but I have a lot more cells than that and a lot of ranges. Is there a way to do something like A2:A8 offset by 1 to get the same result?



    Thanks ahead of time. I tried search first to no avail, so if anyone has link, that would be great also. Thanks!

    Re: Formula to change font


    Thanks Seti,


    I came up with a way to do it without VBA. Using the Insert Symbol, I inserted the check mark symbol in X1. Then did my formula:
    =IF(COUNTA(D17:D46)=20,X1,COUNTA(D17:D46))


    This works perfectly.


    Thanks for your help.

    Hey guys,


    Is there a way to change the font of a cell with a formula? What I need to do is check 20 cells in a column, and if all 20 cells are filled in I want a check mark which I can get from Font:Wingdings which is Char(252). Now if there are not text in all 20 cells, it is just going to count, and give me a number, but I have to switch fonts again. Any ideas on this? I can do the If formulas and stuff, just don't know what to put in to change Font from Arial to Wingdings or visa versa.


    Thanks

    Re: Race results and the "Now" function


    Put this formula in there instead. I tested and works.


    =IF(ISERROR(VLOOKUP($A2,'Finish Times'!$A$3:$C$501,3,FALSE)=TRUE),"",VLOOKUP(A2,'Finish Times'!$A$3:$C$501,3,FALSE))

    Re: Formula Breakdown for Understanding


    Thomach, Awsome Link
    Using that link I was able to modify my Code to


    Code
    =SUMPRODUCT(($I$50:$I$58>=TODAY())*1)&" Active"


    So I think I am starting to understand it now. The SumProduct can be used to count Arrays, and in my formula the "*1" part must be the way it turns it into a number value, so this formula works as well, and is much simpler than the original.

    Re: Formula Breakdown for Understanding


    Thanks for the Link, it did shed some light on it, but on another point it raised another question. My Current formula:


    Code
    =SUMPRODUCT(($I$50:$I$58>=TODAY())*($I$50:$I$58="")+($I$50:$I$58>=TODAY()))&" Active"


    Now basically all the first two parts are doing is coming up with a 0 value then adding the initial value. Why do it this long way instead of just doing


    Code
    =SUMPRODUCT((0)+(I50:I58>=TODAY()))&" Active"


    After testing it, it works perfectly, so why the long way, am I missing something? This is what is confusing me.


    Thanks again!

    Hey guys!


    I am using a formula that looks at a range of cells and counts the number of cells with the date greater than todays date. Now I used the SUMPRODUCT formula that has been posted many times, but I like to have an understanding of exactly what the formula is doing. When I see SUMPRODUCT, I am thinking that something is being added, but I just can't get it to click in my mind. Can someone who has a few moments, just explain what each part of the formula is doing please, or just a summary of how SUMPRODUCT works?


    I modified it slightly for my use, but the gist of it is the same.


    Code
    =SUMPRODUCT(($I$50:$I$58>TODAY())*($I$50:$I$58="")+($I$50:$I$58>=TODAY()))&" Active"


    Thanks a bunch!