# Posts by p45cal

• ## Sum Column With Date Matching Choice

Re: Todays Date Match Column Date Add Column

Quote from h1h

=SUM(OFFSET(A6;1;MATCH(B1;B5:F5;FALSE);5;1))

a slight adjustment or two (as it misses a cell), either:
[bfn]=SUM(OFFSET(A5,1,MATCH(B1,B5:F5,FALSE),6,1))[/bfn]
or:
[bfn]=SUM(OFFSET(A6,0,MATCH(B1,B5:F5,FALSE),6,1))[/bfn]

or even my offering:
[bfn]=SUM(INDEX(B6:F11,,MATCH(B1,B5:F5,0)))[/bfn]

not forgetting to put:
[bfn]=TODAY()[/bfn]
in B1

p45cal

• ## Hide/Unhide Columns Based On Cell Value

Re: Hide/Unhide Columns Based On Cell Value

Change the code to:

re copying cell values,

Code
``Sheets("Sheet1").Range("A2")=Sheets("help").range("F5").value``

will copy the value of cell F5 on sheet 'help' to cell A2 on sheet 'Sheet1'.
p45cal

• ## Extract Fax & Phone Numbers From Cells

Re: Extract Fax &amp; Phone Numbers From Cells

Joe, note I had to make a correction or two to my formulae. Hope the bosses at SLP appreciate you/me!
p45cal

• ## Extract Fax & Phone Numbers From Cells

Re: Extract Fax &amp; Phone Numbers From Cells

2 solutions:

1. Worksheet formulae. Create two blank columns for the formulae. In your sample file I inserted 2 columns to the right of column C. In cell D3 I put:
[bfn]=MID(C3,SEARCH("Phone",C3),(SEARCH(CHAR(10),C3,SEARCH("Phone",C3))-SEARCH("Phone",C3)))[/bfn]
In cell E3:
[bfn]=MID(C3,SEARCH("Fax",C3),(SEARCH(CHAR(10),C3,SEARCH("Fax",C3))-SEARCH("Fax",C3)))[/bfn]
(Note that although the code tags have capitalised 'Fax' and 'Phone' above, SEARCH is not case-sensitive so it probably won't matter.) Copy up/down as far as you need, then if you want copy/paste special - values.

2. VBA solution. Run this macro with the single-column block of cells you want to extract the data from selected, after having ensured there are two blank columns to the right of those cells for the extracted data:

Code
``````Sub blah()
For Each cll In Selection.Cells
p = Split(cll.Value, vbLf)
For i = 0 To UBound(p)
If InStr(p(i), "Phone") > 0 Then cll.Offset(, 1) = p(i)
If InStr(p(i), "Fax") > 0 Then cll.Offset(, 2) = p(i)
Next i
Next cll
End Sub``````

p45cal

UPDATE: I made a mistake in the formulae in solution(1), I have corrected it now (it was the removal of '-1' from the end of both which resulted in the truncating of one digit from the numbers).

• ## Hide/Unhide Columns Based On Cell Value

Re: Hide/Unhide Columns Based On Cell Value

yes, remove

Code
``.Activate``

p45cal

• ## Toggle Cell From Validation List To Formula

Re: Dual Lookup Feature

So you want a formula to be overwritten with a number entered by the user, but you still want the formula there? Not possible without vba (I think). Paste the following code into the worksheet code module of the Control sheet:

which detects when a single cell is changed in the range C2:C5, grabs the new value, uses it to find what should be in the leftmost column and puts it there, then reinstates the formula in the cell manually changed, which should then result in the same code as the user has just entered! If the user enters a code which is out of range then his changes are removed. Also in attached file.
p45cal
postscript: It's not quite what you asked for (no colouring of cells because formulae remain as they were), but I'd be interested if you prefer it?

• ## Hide/Unhide Columns Based On Cell Value

Re: Hide/Unhide Columns Based On Cell Value

See attached
p45cal

• ## Hide/Unhide Columns Based On Cell Value

Re: Hide/Unhide Columns Based On Cell Value

The code is in a worksheet module so I doubt it'll get called if it's on another sheet.
p45cal

Code
``If Target = Range("D2") Then``

will allow the code to work if the value of any cell you change on the sheet is equal to the value of what's already in cell D2.
You could change it to

Code
``If Target.address = Range("D2").address Then``

which, in this case, is easier than the Intersect route I took.

• ## Hide/Unhide Columns Based On Cell Value

Re: Hide/Unhide Columns Based On Cell Value

Try this adaptation of Dave's code in the worksheet code module:

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Range("D2"), Target) Is Nothing) Then
Dim lVal As Long
lVal = Range("D2")
Columns("F:G").Hidden = Not (lVal = 1)
Columns("I:J").Hidden = Not (lVal = 2)
Columns("L:M").Hidden = Not (lVal = 3)
End If
End Sub``````

p45cal

• ## Return Minimum Value Above Zero

Re: Search Minimum Nonzero Value

or array entered: =MIN(IF(YourRange<>0,YourRange)) or =MIN(IF(YourRange>0,YourRange)) [ea]*[/ea]
p45cal

• ## Copy Matching Cells In Series From Column

Re: Macro

First select the range on the worksheet that you want processing (so that you don't get stuff copied beyond the bottom of your table) then run the following macro:

p45cal

• ## Add x Time To Current Date

Code
``Range("A1").value= Range("A1").value+ TimeValue("00:30:00")``

p45cal

• ## Find, Copy, Paste To Another Sheet

Re: Find, Copy, Paste To Another Sheet

Try the macro below, I'll try to attach a file too. You will have to continue your formatting down the 'Import Sheet'. There's no rigorous error checking in it:

p45cal

• ## Select Sheet Code. Sheet Name Changes

Re: Select Sheet Code. Sheet Name Changes

A different angle.. and perhaps a little cheeky;
re:"Nom du nouvelle comparation"
What is a 'comparation'? I couldn't find it in the (french?) dictionary (online). Should it be 'comparaison'? And remembering my O-level French, should "du nouvelle" not agree genderwise and be "de la nouvelle"? In which case shouldn't the whole string be "Nom de la nouvelle comparaison"?

Also in "Veuillez saisir le nouvelle nom de la comparation", isn't 'nom' masculine leaving "Veuillez saisir le nouvel nom de la ..." or perhaps "Veuillez saisir le nouveau nom de la ..."

Awaiting and ready to duck incoming hail of bullets, p45cal

• ## Display Found Cell Address

A few Q.s and points:

Is this on a user form or embedded on a sheet? I've tested this a bit on a sheet, if it's on a user form you might have to make it modeless (not tried it out).

It seems to search only for the first occurrence on each sheet, but only ends up showing the one on the last sheet it's been found on.

re:

Code
``Application.Goto reference:=Worksheets(ws.Name).Range(cl.Address), Scroll:=True``
Code
``Application.Goto cl``

worked too.
p45cal

• ## Open File From Directory & Name Stored in 2 Cells

Re: Open File From Directory &amp; Name Stored in 2 Cells

The following attempts to develop Dave's and shg's responses in order to answer the "is opened and then saved, in the correct directory" part of the OP's question. It tries to use his folder naming convention, and if a file has a job number which doesn't fit into an existing folder, one is created, hopefully with the right name. You will have to adjust the file paths to suit you (I've left 'em as I tested them rather than introduce errors by trying to convert them to your pathnames):

p45cal

• ## Fill Column From Text Files

Re: Filling A Spreadsheet Column With Data From Text Files

Whether stuff gets copied across depends on the line:

Code
``If rng.Columns(1).Cells(i, 1).Value = report(1) Then``

I would put a breakpoint at this line (by clicking on the left grey margin in the VBE, then when it stopped there, examine report(1) in the Locals window and compare it with something like this in the immediate window (you can press F5 repeatedly until i is the value you'd expect equality):

Code
``?"xxx" & rng.Columns(1).Cells(i, 1).Value & "xxx"``

to see if they're the same. Usually at this point, you know what's gone awry.
p45cal

• ## Delete Rows Matching Criteria & Move Rows

Re: Macro To Delete Rows And Move Rows Based On Criteria

Try the following two macros, but see notes below:

Code
``````Sub blah1()
'Removes rows with TOM in column C
Set myRng = Range("A1").CurrentRegion 'adjust A1 to suit
FirstRow = myRng.Row
lastrow = FirstRow + myRng.Rows.Count - 1
For rw = lastrow To FirstRow Step -1
If Cells(rw, "C") = "TOM" Then Rows(rw).Delete
Next
End Sub``````

1.The line:

Code
``Set myRng = Range("A1").CurrentRegion 'adjust A1 to suit``

in each of the macros assumes that column A has no blanks in the region that needs to be processed. If this is the case, choose a cell instead of A1, within the region to be processed, where the column is blank-free.
2.The macros cut/copy/delete ENTIRE rows, right aross the sheet.
3.The sorting ascending/descending and the primary sort column may have to be adjusted.
p45cal
ps. it would've been nice to see what you'd tried codewise, this board isn't really a free programming service.

• ## Day Hour Minute Format To Total Hours

Re: Day Hour Minute Format To Total Hours

I think Dave meant it to be
[frc]=(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440[/frc]which is the duration in L2 converted to decimal days (where a .5 is half a day or 12 hours). These you should be able to sum, average, max etc.
p45cal

• ## Count Number Of Rows In Range

Re: Count rows within a Range.

the following line will enter the number of rows in JRange into each of the cells immediately to the right of all the cells in JRange. It does not refer to KRange at all, which may have to be manipulated to match JRange, not always the case if cells are inserted rather than rows.

Code
``Range("JRange").Offset(, 1).Value = Range("JRange").Rows.Count``

if you do arrange for KRange to match JRange or you don't care whether the ranges match, then

Code
``Range("KRange").Value = Range("JRange").Rows.Count``

should do that.
p45cal