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
    Sub AddFormulas()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    If Left(ws.Name, 1) = "Q" Then
    Call SetCF(ws)
    ws.Visible = xlSheetVeryHidden
    End If
    Next ws
    End Sub


    Code
    Sub SetCF(ws As Worksheet)
    Dim rng As Range: Set rng = ws.Range(ws.Cells(2, 3), ws.Cells(5, 3))
    With rng.FormatConditions
    .Delete
    End With
    Application.Goto ws.Range("C2")
    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
    Sub SetCF(mySheet As Worksheet)
    Dim rng As Range: Set rng = Sheets(mySheet).Range(Cells(2, 3), Cells(5, 3))
    
    With rng.FormatConditions
    .Delete
    End With
    
    Application.Goto Sheets(mySheet).Range("C2")
    
    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
    Sub Transfer_Data()
        Dim wsSrc   As Worksheet: Set wsSrc = Sheets("Raw")
        Dim wsDest  As Worksheet: Set wsDest = Sheets("SIRs")
        With wsDest
            .Range("A" & Rows.Count).End(xlUp).Offset(1) = wsSrc.Range("M18")
            .Range("B" & Rows.Count).End(xlUp).Offset(1) = wsSrc.Range("N26")
            .Range("C" & Rows.Count).End(xlUp).Offset(1) = wsSrc.Range("K13")
            .Range("D" & Rows.Count).End(xlUp).Offset(1) = wsSrc.Range("K14")
        End With
    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
    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?

    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


    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","")