Posts by nonno

    Scratch that, I found the error. In the SetCF sub, I did not fully qualify one of the Set statements. For those interested, here's the fix. The first Set statement in the SetCF sub, I left out the ws proceeding the .Cells.


    Code
    1. Sub AddFormulas()
    2. Dim ws As Worksheet
    3. For Each ws In ActiveWorkbook.Worksheets
    4. If Left(ws.Name, 1) = "Q" Then
    5. Call SetCF(ws)
    6. ws.Visible = xlSheetVeryHidden
    7. End If
    8. Next ws
    9. End Sub


    Code
    1. Sub SetCF(ws As Worksheet)
    2. Dim rng As Range: Set rng = ws.Range(ws.Cells(2, 3), ws.Cells(5, 3))
    3. With rng.FormatConditions
    4. .Delete
    5. End With
    6. Application.Goto ws.Range("C2")
    7. End Sub

    I can't seem to find the right combination on this one.


    I need to pass the sheet name to another Sub called SetCF. If I'm using the next ws syntax, can I just pass the ws name without having to qualify it with the Set statement? I cut out all the stuff that isn't important in the second sub.



    Code
    1. Sub SetCF(mySheet As Worksheet)
    2. Dim rng As Range: Set rng = Sheets(mySheet).Range(Cells(2, 3), Cells(5, 3))
    3. With rng.FormatConditions
    4. .Delete
    5. End With
    6. Application.Goto Sheets(mySheet).Range("C2")
    7. End Sub

    I'm using VBA to input a formula into a cell on a worksheet change. This formula is also taking advantage of a Function to round to significant digits.


    My question, how can I show the significant digits in the cell, specifically 4 significant digits? Am I overlooking the obvious? I found this custom format, but it leaves the decimal place.


    This is how the digits should show after rounding to 4 significant digits [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 54"]

    [tr]


    [TD="width: 72, align: right"]1.977[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [TD="align: right"]31.65[/TD]

    [/tr]


    [tr]


    [TD="align: right"]40[/TD]

    [/tr]


    [/TABLE]
    If I use the custom format

    0.###


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 54"]

    [tr]


    [TD="width: 72, align: right"] [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 54"]

    [tr]


    [TD="width: 72, align: right"]1.977[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2.[/TD]

    [/tr]


    [tr]


    [TD="align: right"]31.65[/TD]

    [/tr]


    [tr]


    [TD="align: right"]40.[/TD]

    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [TD="align: right"] [/TD]

    [/tr]


    [/TABLE]

    Give this a try. I'll let you build out the rest.


    Note: Please fix your code tags in post #1.


    Code
    1. Sub Transfer_Data()
    2. Dim wsSrc As Worksheet: Set wsSrc = Sheets("Raw")
    3. Dim wsDest As Worksheet: Set wsDest = Sheets("SIRs")
    4. With wsDest
    5. .Range("A" & Rows.Count).End(xlUp).Offset(1) = wsSrc.Range("M18")
    6. .Range("B" & Rows.Count).End(xlUp).Offset(1) = wsSrc.Range("N26")
    7. .Range("C" & Rows.Count).End(xlUp).Offset(1) = wsSrc.Range("K13")
    8. .Range("D" & Rows.Count).End(xlUp).Offset(1) = wsSrc.Range("K14")
    9. End With
    10. End Sub

    Re: Double click method to insert rows


    Hi mikerickson,


    Thank you very much, this will work out great.


    I've been trying to understand


    Code
    1. Select Case CLng(.OptionButton1.Value) + 2 * CLng(.OptionButton2.Value)


    by stepping through the code with F8 and a break point.


    I just can't seem to grasp how you come up with a -1 or a -2?


    If .OptionButton1.Value is checked, that is TRUE (1) and plus 2 makes 3. When multiplied by zero, your left with zero but how does that now become -1?

    Re: Double click method to insert rows


    patel,
    Thank you for the response and that is quite the interesting approach, but that isn't going to work for the user.


    Here is what I have so far. Double click on a cell in column A (Input tab) and whatever cell you pick, a row will be added below that name.


    You will see the userform pop up, but it isn't doing much right now. How can I store the choice, insert or delete and have the code go one way or the other?

    Files

    On one sheet I've setup a doubleclick method to insert not only a row on the active sheet, but also a row on another sheet equivalent to the active row on the active sheet.



    This works fine, but now I need to give the user the option to either delete one row or insert one row. I built a userform with two option buttons inside a frame with on form initialize the insert option is set to true. When the user clicks the Ok button on the userform, I need to pass which option button is active to the doubleclick event. How can I do this?

    Re: multiple IF statement


    Could be something more elegant, but how about,


    [F]=IF(AND(A1="No",B1<=21),"Pass",IF(AND(A1="No",B1>21),"Fail",IF(AND(A1="Yes",B1<=30),"Pass",IF(AND(A1="Yes",B1>30),"Fail"))))[/F]

    Re: Nested If Statement with multiple criteria


    See if this works. Maybe I am just missing the big picture, but if Term 1 = Rowing and Term 2 = Swimming then it doesn't really matter if Tennis and/or Squash is in Term 3 and Term 4 respectively, because to get to the Term 3/Term 4 criteria you have to start with Rowing and Swimming.

    Files

    Re: Nested If Statement with multiple criteria


    I am leaving in a few minutes, but the first thing I see that is off...


    This
    =IF(AND(C2="Rowing"+D2="swimming"+E2="Tennis"),"clash","")


    Should be
    =IF(AND(C2="Rowing",D2="swimming",E2="Tennis"),"clash","")