# Posts by p45cal

• ## Calculate Multiple Times Residing in Single Cells

Re: Add Characters To The End Of A String

Not what you asked for, but this may help; a small user defined function used in a worksheet like:
[frc]=TotTime(K20)[/frc]

Code
``````Function TotTime(a As Range)
k = Split(a, ", ")
For i = 0 To UBound(k)
If InStr(k(i), ":") > 0 Then
x = Split(k(i), ":")
TotTime = TotTime + x(0) + x(1) / 60
End If
Next i
End Function``````

Then copy/pastespecial values if you want.
p45cal

• ## Clear x Columns Of Inserted Rows

Re: Clear Certain Cells, Not Entire Rows, Of Newly Inserted Rows

Try this macro below. I had to add 'Set' as I got an error. The only thing that may need adjusting is the way that it deals with dates in row 12; on the one hand you say "that contains a date" which could mean includes a date amongst other things, then later you say "these columns will be date columns and have a date as a heading". For the moment I've gone with the second bit assuming that the header IS a date and nothing else enabling me to use the IsDate function. I also interpreted to the letter the phrase "from column AP to the end of all adjacent columns like AP (all of these columns contain the word "Test" in row 12 as a heading)", meaning if there is one cell not containing the word "Test" it will stop there, even if there are more headers containing "Test" further to the right.

p45cal

• ## Find Maximum. Some Values Are Represent By Text

Re: Find Max Value In Column When Not All Values Are Numbers

Quote from Dave Hawley

Why not use WorksheetFunction.Max(.....?

I think he's already got the max, he's now getting the dates on which the max occurred (I think), as a list in a single cell.
(Maybe it's a case of a badly titled question - I'm not sure)
p45cal

• ## Extend Range To Longest Column

Re: Extend Range To Longest Column

re: "p45cal: I tried that code buy just substituting it in for the line that started the same way. I ran into an error, am I using your code wrong?"

What error? What line?
Make sure nothing's been mangled in you copy/pasting. I always test code before posting, and if I haven't, I'm explicit about it.
If it still fails, post that code as you copied it and a few lines above and below (or even the whole macro if it's not too long) saying what the error says and what line it stops at.

p45cal

• ## Find Maximum. Some Values Are Represent By Text

Re: Find Max Value In Column When Not All Values Are Numbers

It is the following lines which determine which cells are to be examined in the two loops to decide what appears in those two results cells.

Code
``````Set lpMax = Range("ex207", Range("et" & Cells.Rows.Count).End(xlUp).Address)
Set fpMax = Range("ez207", Range("ev" & Cells.Rows.Count).End(xlUp).Address)``````

As noted earlier, ET207:EX218 and EV207:EZ218 overlap, and I think contain more columns than they should. So which columns should they be searching? You can see the relation between each 'Set' line and its corresponding address, so you should be able to adjust those lines above to look in only the columns you want them to look at, and only you know which those columns are.

p45cal

• ## Address Fields From Rows To Columns

Re: Align Address Fields From Rows To Columns

This macro should copy data from Sheet1 of your file to Sheet3 as I think you want it:

p45cal

• ## Find Maximum. Some Values Are Represent By Text

Re: Find Max Value In Column When Not All Values Are Numbers

While the following macro gets the value you want to see in BH145, I'm not so sure that it will always do what you want, since it examines all the cells in ET207:EX218, including the grey bits inbetween. Likewise the routine later looks at the cells EV207:EZ218, which overlaps the first range, to determine BH151. So I'm not at all sure this is what you want:

p45cal

• ## Find Characters Within Text

Re: Finding Text Within Characters

[frc]=IF(ISERROR(FIND("ell",F25)),"not there","found it")[/frc]

p45cal

• ## Hlookup & Exact Match

Re: Hlookup &amp; Exact Match

You can use False! For it to return an exact match there has to be one. If there is an exact match it returns it properly. 45209 doesn't exist on the data sheet.

p45cal

• ## Lookup Value Based On Year in Dates

Re: Which Year In The Period

See cells K11:M11 formulae and comments in the attached

p45cal

• ## Extend Range To Longest Column

Re: Changing Region Size In Vba

This might do it:

Code
``````sRT = "=offset(" _
",1,0,counta(" _
& Cells(2, Target.Column).Resize(Target.CurrentRegion.Rows.Count - Target.Row).Address & _
") )"``````

p45cal

Since you say they're in column A, try the following macro. It will write to "Sheet2" rather than a new workbook, but you can copy the result to a new workbook yourself. So make sure that Sheet2 exists and that it is empty.
Before runing the macro, select a single cell in column A, being the first line of one of the adresses there (it will work down until there are two blank cells in succession in column A).

The macro:

p45cal

• ## Offset With Autofilter

Re: Offset With Autofilter

in which case this, array entered, works as the list is extended, as long as the autofilter is refeshed (say by clicking on the down arrow and reselecting the current filter):

[frc]=INDEX(OFFSET(_FilterDatabase,1,0,ROWS(_FilterDatabase)-1),MATCH(1, SUBTOTAL(3, OFFSET(OFFSET(_FilterDatabase,1,0,ROWS(_FilterDatabase)-1),ROW(OFFSET(_FilterDatabase,1,0,ROWS(_FilterDatabase)-1))-(MIN(ROW(_FilterDatabase))+1),,1)),0))[/frc]
[ea]*[/ea]

p45cal

but this is only a clumsy adaptation; there must be a more elegant solution.

• ## Offset With Autofilter

Re: Offset With Autofilter

I was going down the same route with the Subtotal function, however I was trying to provide a solution including the hidden named range '_filterdatabase' so that it would take account of an expanding list. Trying to adapt your solution I came across a full stop when I tried to replace ROW(A6). I could hard code a 6 in there, leave it as ROW(A6), both of which worked, I even tried defining a new name.. to no avail.
So just from an academic point of view, how could it be done (without vba, of course)?

p45cal

• ## Fill Column With Checkboxes For Cell Selection

Re: Fill Column With Checkboxes For Cell Selection

try this version:

p45cal

• ## Percentage For Entire Columns

Re: Percentage For Entire Columns

I have some sort of solution for you, but since you don't say where you want this information to be, I've chosen a spot at random. Adjust according to your requirements.
In cell L15:
[frc]=COUNTA(\$H\$2:\$H\$107)[/frc]
I've used H2 as the first row to look in as I've presumed you have a header in H1. I used H107 as the likely last row you'll need to look at, but make this bigger or smaller as required, it should be at least as big as the largest number of orders you're ever likely to get in a week. The formula should give the total number of orders for that week. (It's also used in the formulae to follow). In the cell to the left or right you could put a label such as 'Total Orders'.

In cell L16:
=COUNTIF(\$H\$2:\$H\$107,"Multiple Orders")/COUNTA(\$H\$2:\$H\$107)

In cell L17:
=COUNTIF(\$H\$2:\$H\$107,"No Order In System")/COUNTA(\$H\$2:\$H\$107)

In cell L18:
=COUNTIF(\$H\$2:\$H\$107,"Credit Info Incorrect")/COUNTA(\$H\$2:\$H\$107)

In cell L19:
=COUNTIF(\$H\$2:\$H\$107,"Other")/COUNTA(\$H\$2:\$H\$107)

Format cell L16:L17 as a Percentage.

Put labels in adjacent cells as appropriate.

p45cal

ps. I removed code tags from the later formulae as putting them in capitalised the lower case letters.

• ## Fill Column With Checkboxes For Cell Selection

Re: Fill Column With Checkboxes For Cell Selection

The following code places numbers in column A, and after each number is so placed, a checkbox is placed in the cell to the right. The check box is sized to fit the cell size, so adjust column woidth before running. Adjust code as required.

p45cal

• ## Check If Time

Re: Check If Time

If dt is dimmed as a Date, doesn't Isdate(dt) always return true, even if the cell is empty?

p45cal

• ## Check If Time

Re: Check If Time

I don't think there is.
Date is a variable type 'Date'. Time doesn't have its own (I don't think), it comes up as a Double, with a default format applied to the cell "hh:mm" by excel when a time is entered into a cell previously formatted as General.

But you could make your own function perhaps? I made this one:

Code
``````Function IsTime(x As Range)
If x.Cells.Count > 1 Then IsTime = CVErr(xlErrNA): Exit Function
If x.Value > 0 And InStr(x.NumberFormat, "mm") > 0 Then
IsTime = True
Else
IsTime = False
End If
End Function``````

which is used on a worksheet like:
=IsTime(D18)
and returns True, False or #N/A (if more than one cell is referred to).

BUT.. it's not especially robust. It uses the format of the cell to try and determine if it's a time format by looking for "mm" in the formatting string of the cell. It also checks that the value is greater than 0, which screws up midnight. If there is a time format applied to the cell like "hh:m" it won't see it as a time format (only one 'm'). Also if there exists some custom format which includes "mm" then it'll trip up, but I'd guess that's unlikely in the cells you're testing.

I also note that you wanted to "check if a time has been" entered into a cell, so the cell value might have been deleted, in which case you can only guess by looking at the formatting; remove the text:
x.Value > 0 And
from the function. This puts midnight right and guesses at a time having been in the cell by looking at the format. That doesn't stop True being returned if cells have been formatted as time before anything is put into them. So overall not very robust unless you adjust it to exactly what you might be looking for.

p45cal

• ## Switch Between Variable & Fixed Spreadsheets

Re: Switch Between Variable &amp; Fixed Spreadsheets

you could try the following, remembering to start the macro with the appropriate workbook being the active one, which may mean going through the menus to run the macro:

p45cal