Posts by jproffer

  • Type Mismatch Evaluating An Array Formula

    Re: Type Mismatch Evaluating An Array Formula


    Figured so, but it was a thought....here'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 & characters...like 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.

  • macros to copy text to seperate sheet

    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.



    Code
    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

  • sum text and zero number in one cell

    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.

  • HLOOKUP or another formula?

    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:


    =SUM(LARGE(B2:H2,{1,2,3})


    is the same as:


    =SUM(SMALL(B2:H2,{2,3,4})


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

  • VBA CODE for Excel 2007 & 2010

    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. :)

  • Locking worksheet

    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 :)

  • VBA CODE for Excel 2007 & 2010

    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 G...it 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 look...it'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:


    Code
    Sub OpenTools
     Userform1.Show
End Sub

  • Corresponding term to reflect in next cell

    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.

  • VBA CODE for Excel 2007 & 2010

    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:


    Code
    Sheets("Sheet1").Range("A1")
    Code
    Sheets(1).Range("A1")

    and

    Code
    Sheet1.Range("A1")


    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:
    Sheet1(Sheet1)


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


    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 references...to use that, you don't have to wrap it with Sheets( ), it's simply:

    Code
    Sheet1.Range("A1")

    as shown above.

  • VBA CODE for Excel 2007 & 2010

    Re: VBA CODE for Excel 2007 &amp; 2010


    :party:


    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.