Posts by p45cal

    Re: Copy Only Used Cells In Column

    re:"What did you mean when you said it wouldn't work since the code will copy the third column?" in msg #15.

    Well, I said: "if the usedrange leftmost column is not column A then this code will copy the third column of that usedrange which won't be column C".

    Column C, as everyone knows, is the third column on any spreadsheet. In the code we've referred to it as Range("C:C") or Columns("C:C"). However, maybe everyone doesn't know that it's really referring to the third column of what immediately precedes it, normally it's 'ActiveSheet', explicitly or implicitly ('cos you don't need the ActiveSheet bit). But if, as we have done, we precede it with UsedRange, then it's the third column of the used range, look, I'll show you:

    Open a new, virgin, sheet. Select say cells D2:H7, type a few characters on the keyboard then holding the control key down press the Enter key. You should now see that range filled with whatever you typed. Now click a cell elsewhere on the sheet to deselect the block. Go to the vbe and in the Immediate window type the following:


    and press enter, then take a peep at the sheet. Hopefully D2:H7 is selected. Note that the usedrange does not start in column A of the whole sheet. Click elsewhere again to deselect. Once again go into the immediate pane and type:


    and press enter. Again take a look at the sheet. Instead of cells of the expected column C being selected you've got cells in column F selected, that's the third column of the Range D2:H7 (the used range). I hope this illustrates graphically what I was trying to say.

    It's not common that the used range does not extend to column A of the whole sheet, but could easily happen if vba code adds a sheet and plonks some data in the middle of it somewhere.
    Just something to be aware of, that's all.


    Re: Copy Column Values Only

    Re:"but it wants to look through a million rows instead of only the used range"


    Sheets("Sheet4").[B]Range[/B]("C:C").Copy Destination:=Sheets("Sheet4").Range("F:F")


    Sheets("Sheet4").[B]UsedRange.Columns[/B]("C:C").Copy Destination:=Sheets("Sheet4").Range("F:F")

    should work in this case BUT.. be aware that if the usedrange leftmost column is not column A then this code will copy the third column of that usedrange which won't be column C.

    Also note that the destination in the copy command doesn't need to have the same dimensions as the source range, you only need to specify the top left cell. Viz.:

    Sheets("Sheet4").UsedRange.Columns("C:C").Copy Destination:=Sheets("Sheet4").Range("F1")


    Re: Code To Click Button In Seperate Workbook

    Assuming there is some compelling reason to do it this way (for example the macros assigned to the buttons having the same name in each of the different worksheets are each different and/or have a different name) then it can be done on these lines:

    For Each bk In Workbooks
        Application.Run bk.Sheets("Sheet1").Buttons("Button 1").OnAction
    Next bk


    Re: Display Degrees, Minutes, Seconds

    Decimal degrees in A1




    all work OK for positive degrees, BUT, how are negative degrees going to be converted? Are the likes of 270.5 degrees going to be converted to 89.5 West. Are we also talking +/-90 degrees as North and South?

    Googling resulted in the likesof:,1759,1828932,00.asp
    and on this site:

    If none of the above suits, post again with more detail as I don't want to go through all the possibilities if most of them are going to be irrelevant.

    Re: Color Entire Row When Cell Condition Met

    Since the OP has deleted his question in a fit of pique, here it is again (assuming my response is up to standard and of use to others?):

    Quote from whatthe

    Using conditional formatting is it possible shade an entire row when a cell is = to 0. I am currently creating a spreadsheet for a small amount of inventory. I am manually editing the amount of product available.

    What I want to happen is when I replace them number with the text ‘Sold Out’ I want then entire row to automatically grey that row out. Is it possible to make this happen using conditional formatting?

    "when a cell is = to 0" or "when I replace them number with the text ‘Sold Out’ " ?

    For the latter, select the whole range where cells you want to be greyed, then go to
    Format|Conditional formatting... and in the first field choose 'Formula is', and in the next field enter a variation of this:
    choose your format and click OK.

    The formula won't be exactly the same; where I've got $B3, the B was the column where 'Sold out' might appear, and row 3 was the top row (actually the row with the active cell). You'll have to adjust this yourself, but keep the $.

    I've used TRIM and UPPER to allow for lower case and a few errant spaces to be used on the worksheet, it could have been:
    =$B3="SOLD OUT"

    If you're looking for the value zero instead of 'sold out', then:


    p45cal[hr]*[/hr] Auto Merged Post;[dl]*[/dl]and for both:
    =OR(TRIM(UPPER($B3))="SOLD OUT",$B3=0)


    Re: Add Ticks or Check Marks in Cells

    OK. Have a go with the attached.
    It rebuilds the list afresh with each change in any checkbox (which have been removed as I've used Marlett checkboxes). The snag to this one is the increased processing overhead, which might become significant if there very many rows to process, you'll have to experiment and see.
    Checks are added/removed with a double-click. The reset button works too.
    Re:"When you say "sheet2 is clear", does it mean an empty sheet?"
    No, just the whole sheet below row 15.
    The rows of data on sheet2 will be in the same order as they are on sheet1.

    Tell me how you get on.

    Re: Add Ticks or Check Marks in Cells

    I notice there's a remark in the code about unique items. Is it allowable for there to be two lines the same on Sheet2? That could happen (at the moment) if a checkbox is unchecked and rechecked.

    The reason I ask is that I'm thinking it will be easier to code and more robust if the list on sheet2 is cleared then built up afresh from the checked items on sheet1 each time any check box is checked or unchecked. This would be difficult if two or more similar lines were allowed to exist on sheet2

    Re: Split Time Into Minutes

    Quote from taurusx

    and 10 minutes in the 17th hour of the day (5:10pm)

    It may not matter now but it may do so later; 5:10PM is 10 minutes into the 18th hour of the day (consider that 1:10AM is not 10 minutes into the first hour of the day but 10 mins. into the second hour of the day). I think shg got it with his question.
    Answer Reafidy's and shg's Qs about how/where you want this displayed, and I'll try to suggest a solution (or two).

    Re: List Of Items As One Variable

    Quote from tristevoix

    it would give a positive result.

    Should it?! 'cardiac arrest' isn't in 'angina pectoris, heart attack, myocardial infarction'.
    Anyway, see code below:

    Sub blah()
    EP = "cardiac arrest"
    ICAD = "angina pectoris, heart attack, myocardial infarction"
    If InStr(ICAD, EP) > 0 Then
      MsgBox "EP is found in ICAD"
      MsgBox "not found"
    End If
    End Sub

    doesn't give a positive result, but change the line:

    EP = "cardiac arrest"


    EP = "heart attack"

    and it will

    Re: Countif For A Non Continuous Cell Range

    Quote from coolhandphil

    p45cal - I copied your code into a new module but when I put the user function into the cell it messed up my data, so I deleted it. I'm not sure what happened there!

    There is absolutely NO WAY that my function could have messed up your data.

    Re: Batch Process A Single Macro In Multiple Files

    Shouldn't be difficult as long as the macro doesn't contain specific references to say ThisWorkbook and the like. Post your macro as is and I/someone will try to adjust it.

    Re: Vba Code Line Carriage Return

    like this?

    MsgBox "Only enter data in white cells." _
    & vbNewLine & "Yellow cells contain formulas or dates entered automatically" _
    & vbNewLine & "Do not insert blank rows. Copy row(s) and Insert/Paste", _
    vbInformation, "Spreadsheet by GJF"

    the continuation characters are ' _' which is a space and an underline character. You can often get away without the space character. You type in a space then and underline character and press Enter.


    Re: Countif For A Non Continuous Cell Range

    Try this UDF:

    Function SumNAs(ParamArray arglist() As Variant)
    For Each arg In arglist
    For Each cll In arg
    If WorksheetFunction.IsNA(cll) Then SumNAs = SumNAs + 1
    Next cll
    Next arg
    End Function

    To add this function to a Module. While in Excel push Alt+F11 and then go to Insert>Module. In here paste the code. Click the top right X to get back to Excel, then use in worksheet, for example:

    Re: Format Date In Listbox

    ..or for all the items in all columns in the listbox to take on the corresponding cell's format in the source range:

    With Workbooks(wbn).Worksheets(wsn)
    dataarray = .Range("data")
    For myCol = 1 To .Range("data").Columns.Count
    For myRow = LBound(dataarray) To UBound(dataarray)
      dataarray(myRow, myCol) = Format(dataarray(myRow, myCol), .Range("data").Cells(myRow, myCol).NumberFormat)
    Next myRow
    Next myCol
    End With
    Me.ListBox1.List = dataarray


    Re: Suffix Numbers With A Through to IV

    The problem I have is with a line such as:

    rngQuote.Range("a65536").End(xlUp)(2, 1) = lngQuoteNum & NewVarianceLetter

    within a function. A bit of trial and error shows that while the function is called from vba it seems to both return a value and add data to a worksheet cell, but when the function is called from a worksheet cell, the formula returns #VALUE to that cell and no change to other cells occur on the worksheet. Removing such lines from the function allows the worksheet UDF to return its value properly.
    Of course, if the function is only ever called from vba that may not be a problem! (..when it would be easy to put such lines after the function call.)

    Re: Suffix Numbers With A Through to IV

    something like this:

    but not tested as other unrelated errors crop up. You have a function trying to do things that a Sub should be doing. Functions are supposed to return values, and I think that's all they're supposed to do.


    Re: Format All Textboxes With A Single Procedure

    try on the lines of this in the userform code module:

    For Each ctrl In Controls
    If TypeName(ctrl) = "TextBox" Then ctrl.Value = Format(ctrl.Value, "0000.00")
    Next ctrl

    or in a standard module:

    For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "TextBox" Then ctrl.Value = Format(ctrl.Value, "0000.00")
    Next ctrl

    I don't think that textboxes can have a format like cells on a worksheet do, so you might have to reset the format of the text in the textboxes everytime it gets changed by the user (or code).