# Posts by p45cal

Re: Transposing Table Layout

Try
in a10:
[frc]=INDEX(\$A\$2:\$A\$4,INT(1+(ROW()-ROW(\$A\$10))/COLUMNS(PROPERTIES)))[/frc]in B10:
[frc]=INDEX(\$B\$1:\$F\$1,MOD(ROW()-ROW(\$A\$10),COLUMNS(PROPERTIES))+1)[/frc]in C10:
[frc]=INDEX(PROPERTIES,INT(1+(ROW()-ROW(\$A\$10))/COLUMNS(PROPERTIES)),MOD(ROW()-ROW(\$A\$10),COLUMNS(PROPERTIES))+1)[/frc]
and copy down all three, so that all the formulae in a single column are the same.
These are NOT array formulae.
p45cal

Re: Restrict Event Code to Column

Maybe you're after this? Change

Code
``Select Case Range("d69").Value``

to

Code
``Select Case target.offset(0,-1).Value``

[SIZE="4"] Untested[/SIZE].No time just now, I'm sure others will respond to the other part of your question before I can get back to you (that is if the moderators haven't noticed!)p45cal

Re: Highlight row(s) Based On Column Values

Quote from Dave Hawley

No need for code, use Conditional Formatting.

Dave, could you show me how to do what the OP wanted using conditional formatting - no simple solution comes to mind, but then I'm no expert (I'm, sure Bub Umlas would have a 3 character CF formula!). I'm not trying to score points here, just to know of a good CF solution for my archives.
p45cal

Re: Highlight row(s) Based On Column Values

This will process the cells of the leftmost column of the current selection and highlight the entire rows:

p45cal[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Oops, missed the last line of your post. Quick and dirty solution, run this which calls the previous macro:

Code
``````Sub blah5()
For Each sht In ActiveWorkbook.Sheets
If sht.Visible Then sht.Activate 'only processes visible sheets in a book
Range("A2:A37").Select 'assumes column A is the column you're interested in
blah4 'calls previous macro
Next sht
End Sub``````

BTW, I'd change the line

Code
``HighlightOn = True``

in blah 4, to:

Code
``HighlightOn = False``

to stop a sheet with no information, or with all the same info in column A, being highlighted at all.
p45cal

Re: Deleting All Rows With Text Values

In the absence of a response to royUK's question, this only may do what you want.

Code
``````Columns("A:A").SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete 'text
Columns("A:A").SpecialCells(xlCellTypeFormulas, 23).EntireRow.Delete 'formulae
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'blanks``````

p45cal

Re: Select Multiple Non Adjacent Range For Chart With Variables

What macro gives the error, and which line (if applicable)?

Re: Select Multiple Non Adjacent Range For Chart With Variables

Try changing:

Code
``````ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _
"StartPoint1:EndPoint1,StartPoint2:EndPoint2,StartPoint3:EndPoint3,StartPoint4:EndPoint4"), PlotBy:=xlColumns``````

to

Code
``ActiveChart.SetSourceData Source:=Sheets("Summary").Range(StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4), PlotBy:=xlColumns``

or reduce the number of '&'s in the final line with

but it may be easier to:

Code
``````Sub blah2()
myRow = 5
With Sheets("Summary")
Set myRng1 = .Range("C19").Resize(myRow)
Set myRng2 = .Range("E19").Resize(myRow)
Set myRng3 = .Range("G19").Resize(myRow)
Set myRng4 = .Range("I19").Resize(myRow)
End With
ActiveChart.SetSourceData Source:=Union(myRng1, myRng2, myRng3, myRng4), PlotBy:=xlColumns
End Sub``````

p45cal
ps not fully tested (all except for setting up a chart (the ranges have been verified))
pps I changed Row to myRow since 'Row' is a reserved word (or some such!).

Re: Reference A Picture In Another Worksheet

Code
``````Sheets("Sheet2").Shapes("test").Copy
Worksheets("Sheet1").Paste Destination:=Range("A1")``````

perhaps? p45cal

Re: Control Cursor Movement

Not without significant change to the code you supplied. Yes if you've used my suggestion of an alternative way (on the last couple of lines of my last post) - it happens automatically, only you have less overall control over which is the next cell to be selected.
p45cal

Re: Defined Cursor Movement

Quote from Rhonda

First, are the entries in the Array {"\$A\$1", "\$F\$5", "\$B\$12", "\$A\$6"} the cell IDs?

Yes (adresses actually, and they don't need to be absolute, so "A1" would do instead of "\$A\$2")

Quote from Rhonda

If yes, is there a limit to the number that you can enter into the array?

I'm not sure!

Quote from Rhonda

can the cell IDs be split between lines as long as they are still enclosed with ()s for readability?

Yes, with the continuation characters (space and underline) thus:

Code
``````Addresses_InOrder = Array( _
"\$A\$1", _
"\$F\$5", _
"\$B\$12", _
"\$A\$6")``````

Quote from Rhonda

where do I put this code, in the workbook or in a module?

in a module, specifically the one for the worksheet you're wanting it to work on, which you can gain access to with a right click on the sheet tab and choosing 'View code' from the menu which pops up.
Though it may be easier to just select the cells you want the person to edit while holding down the control key, then under Format|Cells, choose the Protection tab and uncheck the Locked checkbox, OK. Then protect the worksheet by Tools|Protection|Protect Sheet and uncheck the Select Locked Cells checkbox while still allowing them to select unlocked cells, OK.
This, though, is perhaps not exactly what you want to do.
p45cal

Re: Change Text To Proper Case But Keep 2&amp;3 Letter Words As Upper

try

p45cal

Re: Autofilter To Filter Time Greater &amp; Less Than

Quote from milkshake

how do i convert the data into time format? because im pasting the data from elseware then correcting all the data, dont know if you have seen the macros iv wrote in there....

I see that you start with some string in C4 (and down), you apply the formula
[bfn]--MID(C4, FIND("":"",C4)-2,8)[/bfn] in column O which actually seems to give valid results (I'd have preferred to see [bfn]timevalue(MID(C4, FIND("":"",C4)-2,8))[/bfn]) but you then proceed to convert it to text with [bfn]TEXT(O4,"hh:mm:ss")[/bfn]. I don't think that last step is required (what's more, it could be what's screwing it up). Try copy/pastespecial values back into column C directly from column O and then format the whole column as Time with:

Code
``````Range(Range("C4"), Range("C4").End(xlDown)).NumberFormat = "h:mm:ss;@"
'or
Range("C4:C" & lastrow).NumberFormat = "h:mm:ss;@"``````

I'd like to hope that your filter would work properly then.
p45cal

[bfn][/bfn]

Re: Add Subtraction Formula To Cell Via Code

assuming mylogoff is/are cell(s) in column D then:

Code
``mylogoff.Offset(0, 1).FormulaR1C1 = "=R[-1]C-RC[-1]"``

should do it, p45cal

Re: Serializing Start From numeric 1

In cell A11, enter jusst the number 1
In cell A12 enter
[BfN]=A11+1[/BFN]and fill down.
p45cal

Re: Autofilter To Filter Time Greater &amp; Less Than

I did it manually by putting this formula in cell N4:
=TIMEVALUE(C4)
and filling down, then copy/paste special back to column C, then formatting that column to time.
However a bit of code can do it in situ:

Code
``````Sub blah()
For Each cll In Range(Range("C4"), Range("C4").End(xlDown))
cll.Value = TimeValue(cll.Value)
Next cll
Range(Range("C4"), Range("C4").End(xlDown)).NumberFormat = "h:mm:ss;@"
End Sub``````

p45cal

Re: Autofilter To Filter Time Greater &amp; Less Than

Column C cells are not times, but in text format. Attached is the same workbook (less vba code to save space) with column C text converted to Excel times with a filter before 8am (there weren't any pre 7am calls) and after 7pm in place.
p45cal

## Files

Re: Tiled Windows Of Same Workbook Code Effecting Wrong Window

Agreed, it's not friendly, and I can't explain it, but changing your

Code
``````wdwData.Activate
wdwDiagram.Activate
wdwInstructions.Activate``````

to

Code
``````wdwData.Activate
Worksheets("Block Wall").Select
wdwDiagram.Activate
Worksheets("Diagram").Select
wdwInstructions.Activate
Worksheets("Instructions").Select``````

and removing the Select statements earlier in the macro should give you what you want.

p45cal

Re: Maximum Value In Column Of Text &amp; Numbers

This is not as simple as it should be. Finding the max when a 'T' means a value is awkward. I would like to use an Array-Entered formula like this in cell AK132:
([ea]*[/ea])[bf]=IF(MAX(IF(AK36:AK128="T",0.0001,AK36:AK128))=0.0001,"T",MAX(IF(AK36AKH128="T",0.0001,AK36:AK128)))[/bf]
but because AK129 is a merge of 21 cells (2 rows x 7 columns) Excel says it's not valid, so a user defined function used in a cell thus (entered normally, not array-entered):
[bf]=TheMax(AK36:AQ128)[/bf]

The code for the function below should be pasted into a Standard code module, not a Worksheet code module:

Code
``````Function TheMax(rng As Range)
For Each cll In rng.Cells
If cll.Value = "T" Then ThisVal = 0.0001 Else ThisVal = cll.Value
If ThisVal > TheMax Then TheMax = ThisVal
Next cll
If TheMax = 0.0001 Then TheMax = "T"
End Function``````

p45cal

ps.Note the average formula in Cell AK129 is probably giving you wrong results (work it out on a calculator and compare). 2 points:
1.Is the rainfall 0 for 28th-31st Jan each day, or is the data simply missing? Empty cells are not used in the Average function, so to include those dates in the average you must enter 0 for each date.
2.Cells with T are also ignored, but I'd have thought you'd want to include them as a day.
Thankfully it's easy to put right (I think): As well as putting 0s in on no-rainfall days, change the formula in cell AK129 from:
[bf]=IF(AK36="","",AVERAGE(AK36:AQ128))[/bf]
to:
[bf]=IF(AK36="","",AVERAGEA(AK36:AQ128))[/bf]
or perhaps better:
[bf]=IF(AK36="","",AVERAGEA(AK36:AK128))[/bf]p45cal

Re: Restrict Text Entry To Column

Code
``````Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 Then '2=column B
Cancel = True 'saves you pressing enter, if you want to continue editing the cell remove this line
If ActiveCell.Value = "" Then
ActiveCell.Value = Range("B1").Value & " - "
Else
ActiveCell.Value = ActiveCell.Value & Chr(10) & Range("B1").Value & " - "
End If
End If
End Sub``````

p45cal

Re: Extend Range To Longest Column

Quote from StillNew

Could it be a problem because it's being run from a module?

Yes, there's no longer a Target object. Goal posts moved, so try this, it might work (depending on Currentregion giving the full region, which in turn depends on no complete blank rows and columns:

p45cal