Posts by jproffer

    Re: Type Mismatch Evaluating An Array Formula

    Figured so, but it was a's another one. Again, I'm just shooting in the wind here, but this part:

    IF(F2:F" & AttributesFinalRow & "=""Mandatory"",D2:D"

    Looks like it's missing some & it should be

    IF(F2:F" & AttributesFinalRow & "=" &"Mandatory" &",D2:D"

    but like i said, not sure. Type mismatch sounds like a variable typo but I didn't see any, and assuming you're using Option Expicit that would have bugged out and highlighted right away.

    EDIT: Nevermind...I see what ya got now that I moved it over to a module.

    Re: macros to copy text to seperate sheet

    Put this into the sheet code of the Calendar Sheet and see if it does what you want.

    I tried to upload it back to you, but I guess the code made it too big to attach.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
        If Target.Column < 15 And Target.Row > 3 And Target.Row < 59 Then
            LR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
                If LR < 3 Then LR = 3
                    Sheets("Sheet1").Range("A" & LR).Value = Target.Value
        End If
    End Sub

    Re: sum text and zero number in one cell

    I can't speak for everyone else, but I can't figure out what you want to do. If you could post an example workbook with fake data (or the real data, if it's nothing secret, would be even better), that would help. Make one sheet showing your data as it is now...and a second worksheet showing what you want as a result after the formulas/VBA have done their thing.

    Even a manually entered result will help us figure out what you want to do.

    Re: HLOOKUP or another formula?

    Either one works :), but I think yours was adding the 3 highest and mine the 3 lowest. Concept is the same.

    with 4 values to look at:


    is the same as:


    of course the second relies on only having 4 values, so would be wrong if more were added later.

    Re: VBA CODE for Excel 2007 &amp; 2010

    If you do have something new, let's start a new thread. 1) this one is getting long, lol. 2) Seperate projects will interest different people, and mixing something different in here will get confusing. I'll keep an eye out though. :)

    Re: Locking worksheet

    Up to you. If you use that, you don't need the line:

    Cells.Locked = True

    because it doesn't matter if they're locked or not...the user can't do anything on the sheet as long as it's protected. And it looks like you figured that one out anyhow :)

    Re: VBA CODE for Excel 2007 &amp; 2010

    I'm always willing. Things like this are a learning experience for me too. Try to think it out and make as many requests as possible from the beginning, which you did pretty much :) .....had a few additions, but that's inevitable. But especially on the workbook side, the closer we get to what you want before the coding begins, the better off we are. As I'm sure you've found out by now, coding isn't intuative like formulas are. If you code something to go into, say column G, and then insert 2 columns between C and D, it will still put it into column don't know any different.

    Don't get me wrong, I don't mind changing things, but it can be stressful during testing if you forget to change one reference.

    At any rate, yes I'm willing to give anything a'll will be a process, just like this was, so give it a week-ish lead time to make sure we get it done before you have to go live with it....if you're on a deadline.

    The controls toolbox for worksheet controls is in the Developer Tab, "Controls" group, "Insert", and the one you want for something like that is a "Forms" button, rather than an "ActiveX" button. Create the button, then right click and assign macro...and the code is something like:

    Sub OpenTools
    End Sub

    Re: Corresponding term to reflect in next cell

    Right now there is no need to use VB coding. The comboboxes have to have a "linked cell", which I used the one right under each box, but you could use any cell you want. Then the linked cell is just an index in your list. I.E.-What row (of the table, not the whole worksheet) the selected value is on, then find that row and get the value from the opposite column on that row.

    Re: VBA CODE for Excel 2007 &amp; 2010

    You can use the sheet index:

    In your workbook, as it's set up right now...with Sheet 1 on the left and sheet 2 on the right you could use:

    Sheets(1) for sheet1, and Sheets(2) for sheet2. However, if you move the sheets around or add sheets, that may change. Sheet index is always from left to right. Sheets(1) right now, is sheet1....if you move sheet2 to the left of sheet1, then Sheets(1) would refer to sheet2. So you have to be careful, but yes it does remove sheet name problems. Index doesn't care what the name is.

    OR :)....there's always an or....

    You can use the sheet codename:

    As it is right now:




    all mean the same thing.

    The sheet code name is found in the VBE project explorer and it is NOT the one that's in parenthesis. For instance right now under "Microsoft Excel Objects" you have:

    if you change the name to "Properties" it will show:

    The Sheet1 is the code name and it will not change unless you delete the sheet then add it back in, then it may have a different code name... and, in hindsight, that's what I probably should have used in all use that, you don't have to wrap it with Sheets( ), it's simply:


    as shown above.

    Re: VBA CODE for Excel 2007 &amp; 2010


    We aim to please. If you have any other issues, let me know. Like I said, I keep everything for quite a while, so I can either resend or troubleshoot for/with you.