Posts by cochese

    This is a rather basic question. I've been developing tools using VBA for awhile (self taught), and thought it was time I learned if using .value was ever required when passing.

    For example: range("b2") = range("a1").value or strVariable = rng.value

    Sometimes I see sample code with .value referenced. From my experience it has always been okay to leave it off because Excel/VBA knows to use it.

    Re: Test whether entire selection intersects with range

    I do follow it. Thank you. I didn't give enough thought into how intersect worked.

    Quote from cytop;683907

    See if you can follow the logic used below...

    If you have a range of A1:A10 and test if the user selection of A10:A11 intersects it, you will get a positive result. I need to know if the entire selection (both cells A10 and A11) intersects with the range. That is, even if one cell of the selection is outside of the range I do not want a positive intersection result.

    Thank you.

    Re: Adding time and then subtracting exact time results in negative number

    That dim was accidentally pasted; it isn't' needed. Your solution works, but I have to see if it is something I can incorporate (my example was a very simplified version of my project). Thank you.

    Quote from jindon;681654

    I simplified my complex project with just a few lines of code, and my problem persists. The gist is that there are activities, each activity has an amount of time it takes to complete, and each activity is assigned to someone. The amount of time each person has needs to be tracked. If several activities are assigned to a person they will have X hours of activity, and if those exact same activities are subtracted from them they should have 0 hours of activity. This does not happen; they end up with negative hours.

    In the spreadsheet I attached, simply type a letter under the Person column (repeat for all four activities). Now delete those four letters. The hours cell well become "########" because it is formatted for time but the value is a negative number.

    Here are things I've discovered: if you assign three activities it does the same thing, but not with only two assignments AND if I change the first time (1:45) to 2:00 there is no problem (however, other time variations will cause problems).

    I have tried assigning each time to a variable declared as Date, as well as Double, and the problem persists. I have tried SO many variations.

    I created a custom tab on the ribbon, and a few buttons on that tab. One of those buttons unhides columns if the columns are hidden or hides them if they are unhidden. I need to change that button's label/caption depending on whether the columns have been hidden or unhidden. Is it possible to change a ribbon button's label/caption from vba?

    This isn't a toggle button (I did find a post on here about toggle buttons:…hlight=ribbon+change+text)

    Re: Checking if workbook is already open

    I don't think I explained the situation well because no one understands. I can't get Excel to display a warning message when I try to open a workbook that is already opened (doing it manually). I know in the past I got this but I just can't duplicate it today. Since Excel doesn't give me a warning message it would seem the code is unnecessary.

    Re: Efficient way to find all cells with particular format

    ** Okay, I didn't find the solution. It worked the first few times. Now it won't. Even when I create the code using the macro recorder, and then try to turn that code it errors on the application.relpaceformat.numberformat. Grrr.

    I found a solution that does this very quickly. The code is:

    Application.FindFormat.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
        Application.ReplaceFormat.NumberFormat = "$#,##0.00"
        wsReport1.Cells.Replace What:="", Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _

    More details at:…a176178(v=office.11).aspx

    I have this code to test if a workbook is already open:

    Dim wb As Workbook
    On Error Resume Next
        Set wb = Workbooks(strFName)
        If Not wb Is Nothing Then OpenedWB = True

    I can't get Excel (2007 or 2003) to say a workbook is already opened. I open a workbook, and then try to open the same workbook again and I get no error. Now I know in the past I have, which is why I had wrote this code, but I can't duplicate the behavior. Any clues how to, or is this code unnecessary nowadays?

    Re: Efficient way to find all cells with particular format

    The data is not continuous, which is the problem. I have attached an example of the data. All the currency cells I need to reformat.

    xlCellTypeAllFormatConditions is what I was thinking of - I at first thought it was my answer, but then learned it is only for finding conditional formats so it won't work. The way SpecialCells work (selecting just the cells within a range that match the specialcell) is what I was hoping for. I only see loops as my solution.

    FindFormat is the best solution, but it is still looping through a lot. This spreadsheet has a lot of rows & columns of data.

    I have a lot of data with the wrong number format. I want to find all cells with this particular number format and change it to another.

    I know I can do this by looping through all the cells on the sheet or by using the find method, but both ways require looping. I am hoping there is a quicker way. I originally thought there was a SpecialCells type that was format conditional (not conditional formatting), but there doesn't seem to be one.

    Thank you.

    Re: A better way to find when looking for two matching criteria

    I could work with sorting, but I just don't know if it would be a more efficient means than what I already created (and have working). I really like your idea of combining the two columns and then searching. I think that might just be the solution!

    Re: A better way to find when looking for two matching criteria

    Quote from dangle;636012

    Hi Cochese

    It may be that the using the 'Dictionary' object is a better way of doing this but it would help to see the actual data and desired results.

    I had never heard of the Dictionary object, but I Googled it and I'm very glad I now know about it; however, I'm not sure it solves my problem efficiently. I'm attaching a sample data spreadsheet. Thank you for the help.

    By better, I mean more efficient. The below code works, but I can't help but feeling that it's sloppy and there is a better way to do it.

    The goal is to match up two sets of data; however, each set of data has two variables to the match. Data Set 1 consists of a column of store numbers, a column of employee IDs, and then a column of results. Data Set 2 consists of a store number column and employee ID column. An employee ID can be used in multiple stores - that is employee ID '123' can exist in each store. Therefore, when I search Data Set 2 I have to make sure what is found is for the right store. In other words, a search from Data Set 1 for employee ID '123' (in store ABC) may find employee ID '123' in Data Set 2 but for an employee in store DEF, in which case findnext has to be used to search for the next employee ID '123' until a match of ID and store are found.

    Thank you.

    Re: Setting selected cells on other workbook/worksheet to a variable

    Quote from mikerickson;634827

    Try this. There is no need to activate BookB.

    ' resident in a module in Book A's VB project
    MsgBox Windows("BookB").Selection.Address

    Thank you, but it only returns one address (for example: $B$2) even when a range of cells are selected.