Posts by mikerickson

    Re: Shifting column # in a referencing formula

    Quote from The Heggins;776031

    I absolutely agree with you the INDEX/INDIRECT would work very nicely, but lets just say that management likes to see that the cells are directly linked when they are reviewing.

    Management that imposes those kinds of restraints on its coders isn't competent to review worksheet formulas.
    [ insert rant that reminds you of the Wobblies (IWW) ]

    External Content
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

    Perhaps management would be satisfied by the use of Names.

    Instead of having ='xxxxxxxxx\[Book1.xlsx]Sheet1'!$A$1 in a cell and being altered by the user changing OtherSheet!Z1
    Perhaps the cell would contain =myCell and the name myCell referring to an INDEX function.

    Re: Count no of time it appears in the data

    It depends a whole lot on how your data is layed out, but some version of COUNTIF or COUNTIFS would probably be involved.

    ("How many times a potential buyer has viewed the car in a specific month"? If you've had enough potential buyers that a spreadsheet is warranted and the car has been on the market for months, I don't know that a formula will address the problem of why the car hasn't sold in all that time.)

    Re: ListBox Numbers Column Alignment

    Column Alignment is not a feature of Excel's listbox.
    It is a constructed feature of the custom Object described in the class module.

    Excel cannot be set to default to a custom object. (i.e. the ToolBox cannot be set to insert this custom object rather than Excel's built in ListBox object)

    Re: ListBox Numbers Column Alignment

    Do not use the .RowSource of the list box, that code demands that you fill the listbox other ways.

    Fortunatly, the replacement is simple

    Rather than

    Me.ListBox1.RowSource = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Address(, , , True)


    Me.ListBox1.List = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Value

    I consider RowSource to be one of those "sounded like a good idea at the time" ideas that Microsoft occasionally has, somewhat like merged cells.

    Re: Shifting column # in a referencing formula

    Quote from The Heggins;775797

    For specific reasons, I need to leave the formula in referencing format, which is why INDEX and INDIRECT wouldn't work here.

    What are those reasons? The INDEX formula would work with closed workbooks.

    Re: Shifting column # in a referencing formula

    search and replace

    might work.

    This will convert the formula in someCell, but it will adjust all the other references in the formula

    With SomeCell
        formStr = .FormulaR1C1
        formStr = Application.ConvertFormula(formStr, xlR1C1, xlR1C1, xlRelative, Sheet2.Range("a1"))
        formStr = Application.ConvertFormula(formStr, xlR1C1, xlA1, xlAbsolute, Sheet2.Range("b1"))
        .Formula = formStr
    End With

    One other question I have is whether that target workbook is open when this code runs. If so,

    Workbooks("otherWorkbook").Sheets("otherSheet").Range("A1").Insert shift:=xlToRight 
    formStr = someCell.Formula
    Workbooks("otherWorkbook").Sheets("otherSheet").Range("A1").Insert shift:=xlToLeft
    someCell.Formula = formStr

    Re: X to close a userform, open another?

    Why not have one use form with a multi page rather than swapping different user forms in and out?
    One page for Menu
    One for Settings

    You can use the MultiPage_Change event to set the size of the user form to be appropriate for the controls on that page.
    Setting MultiPage1.Syle = fmTabStyleNone will hide the tabs. and force the user to navigate with between with command buttons.
    The QueryClose event can cause the corner X to swap pages.

    The user will be not be able to distinguish between this style and the multi-userfrom approach, while you will miss all the headaches. And passing variable from one page to another is much easier than from one user from to another. (Particularly when your Settings form get data from one instance of Menu and sends data to a different instance of Menu.)

    Re: VBA code to remove specific special character with hyphen

    Select Case Mid(ce.Value, i, 1) 
         'Getting the special character string using the code
        Case Is = "& mystring", "–" 
            myString = Replace(ce.Value, Mid(ce.Value, i, 1), "-") 
            ce.Value = myString 
        Case Else
    End Select

    Mid(ce.Value, i, 1) will always have length = 1.
    "& mystirng" will never be of length 1, neither will the variable mystring.
    So the only time that case will apply will be when Mid(ce.Value, i, 1) = "-". But all that instruction does is replace the "-" with "-", ie. it will do nothing.

    In words, what are you trying to do?

    Re: Using a TextBox as a continuous Comment Box on a Worksheet

    My Mac doesn't support the ActiveX text box that you used, so I substituted a different style of text box, that is supported by both Windows and Mac.

    You didn't specify, but I assumed that the two text boxes on the user form were for Name and Comment. This uses the current username as its default.
    I also added a Cancel button to the user form.

    This is the code in the user form

    and this is the code that the button on the sheet calls.

    Re: Excel VBA - change colour of last row cells based on cell value

    Could you attach a workbook with an example, including the desired results.

    I'm not sure what you mean by expanding the Sheet1 range to A1:F6.
    And I don't understand what you mean by "although I will be entering other rows in that range in sheet 1 and sheet 2" and how you want the code to react to these added cells.

    Re: Excel VBA - change colour of last row cells based on cell value


    Re: Message box if date in userform textbox is less than today

    If you use this code, the TextBox1_BeforeUpdate will handle when the date is typed in.
    The other routine explicitly calls TextBox1_BeforeUpdate when a value is put in the text box.
    This example uses the ListBox1_DoubleClick event, but whatever event you are now using to double click from lookup listbox to load into textbox would work. The key is the explicit call of TextBox1_BeforeUpdate after the value has been put in the text box.

    One potential issue is what you want to happen to the focus after the MsgBox appears. If the MsgBox is for information only, then this should be fine. If an early date is an un-allowable error that the user must fix, what do you want to happen to the focus after the MsgBox appears

    Re: Generate an array with positive integers that sum to a specified value

    Your limitation that the number is between 1 and 1,000 is problematic.
    If the target number is 20,000 there is no solution. Similarly, if the target is 100, there is no solution if any of the terms are 101.

    You could use this UDF. All of the terms will be <= the target value although some might be greater than 1000, if the target is.
    If the target value is in C1, the array formula =TRANSPOSE(RandomSumTo(C1, 15)) would be the formula used.

    Re: Identify Most Frequently Occurring Combinations of 3

    That idea was harder to implement than I thought.

    BUT... instead of using number, use strings
    on the patient sheet, create a helper column that concatenates each patient's data (a formula like =a1&b1&c1&d1&e1&f1)

    (call this the PatientColumn)

    Then do the same to your triplet data
    Then substitute "0" with "?" on the triplet data

    Then the formula =COUNTIF(patientData, "111???") will return how many patients have the first, second and third conditions
    and COUNTIF(patientData,"11?1??") will return how many patients have the first, second and fourth conditions.

    A patient with all four conditions will be counted by both of those formulas.

    With 27 conditions, you will need sub-helper columns
    (e.g. one with =a1&b1&c1 another with =D1&E1&f1 and then another combining those two)

    With 100,000 patients and 27*26*25 tripplets, this is going to be a big, slow workbook, but I think this approach is the way to go.