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:

Activesheet.usedrange.select

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:

Activesheet.usedrange.columns("C:C").select

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.

p45cal

Re: Copy Column Values Only

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

change:

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

to:

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

p45cal

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

p45cal

Re: Display Degrees, Minutes, Seconds

Decimal degrees in A1

Degrees:
=INT(A1)

Minutes:
=INT(MOD(A1,1)*60)
or
=INT(MOD(A1*60,60))

Seconds:
=MOD(MOD(A1,1)*60,1)*60
or
=MOD(A1*3600,60)

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:
http://support.microsoft.com/kb/213449
http://www.pcmag.com/article2/0,1759,1828932,00.asp
http://www.cpearson.com/excel/latlong.htm
and on this site:
http://www.ozgrid.com/forum/showthread.php?t=60352

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.
p45cal

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:
=TRIM(UPPER($B3))="SOLD OUT"
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:

=$B3=0

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

p45cal

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.
p45cal

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
p45cal

Re: Add Ticks or Check Marks in Cells

I'm having an explore on this one. Do the rows on Sheet 2 have to be in click order, the same order as on sheet 1 without any spaces of course, or it doesn't matter?
p45cal

Re: Return x Characters Based on x Cells Having Whole Numbers Above Zero

Whittling down some more.. how about (in E2):

=REPT("/",SUM((A2:C2=INT(A2:C2))*(A2:C2<>0)))

array-entered in one cell then copied down?

p45cal

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

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"
Else
  MsgBox "not found"
End If
End Sub

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

EP = "cardiac arrest"

to

EP = "heart attack"

and it will
p45cal

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.
p45cal

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.
p45cal

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.

p45cal

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:
[bfn]=SumNAs(H5,J5,L5,N5,P5,R5,T5,V5)[/bfn]or
[bfn]=SumNAs(H2:P12,H15:R15,J19,L19,N19,P19)[/bfn]whatever..
p45cal

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

p45cal

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

Re: Suffix Numbers With A Through to IV

something like this:

Public Function NewVarianceLetter(lngQuoteNum As Long, Optional rngQuote As Range) As String
    Dim a As String
    Dim i           As Long
    Dim rngFind     As Range
     
     Sheets("variation").Select
    Set rngQuote = ActiveSheet.Cells
     
    Set rngFind = rngQuote
     
    i = 0
     
     
    On Error Resume Next
    Do Until rngFind Is Nothing
        i = 1 + i
        strLetters = Split(Columns(i).Address(0, 0), ":")(0)
        Set rngFind = rngQuote.Find(What:=lngQuoteNum & strLetters, LookAt:=xlWhole, _
        MatchCase:=False, SearchFormat:=False)
     Loop
     'On Error GoTo 0
    NewVarianceLetter = strLetters
    Sheets("variation").Select
    Range("a65536").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = lngQuoteNum & NewVarianceLetter
End Function

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.

p45cal

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

Re: Suffix Numbers With A Through to IV

Yes, I was playing with the same idea!
I finished up with

strLetters=Split(Columns(i).Address(0, 0), ":")(0)

p45cal