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

Select Case Range("d69").Value

to

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:

Sub blah4()
If Selection.Areas.Count > 1 Then Exit Sub 'ensures only one contiguous block selected
HighlightOn = True 'this is flipped true/false everytime a new value is encountered
StartRow = Selection.Row 'First row of selection
EndRow = StartRow + Selection.Rows.Count - 1 'last row of selection
mycolumn = Selection.Column 'leftmost column of selection
FromRow = StartRow 'FromRow is the first row of a new value encountered


Do
 r = 1 'holds the number of cells found with a similar value
 Do Until Cells(FromRow, mycolumn).Value <> Cells(FromRow, mycolumn).Offset(r).Value Or FromRow + r > EndRow 'comes out of loop if different value encountered or end of selection reached
  r = r + 1
 Loop 'this loop exits when a different value is encountered
 If HighlightOn Then 'highlight the rows
  Cells(FromRow, mycolumn).Resize(r).EntireRow.Interior.ColorIndex = 40
  HighlightOn = Not HighlightOn 'flip the highlight
 Else
  HighlightOn = Not HighlightOn 'flip the highlight
 End If
 FromRow = FromRow + r 'set Fromrow to be the beginning of the next block of cells
Loop Until FromRow > EndRow 'stop when the bottom of the original selection is reached
End Sub

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:

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

HighlightOn = True

in blah 4, to:

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.

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:

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

to

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

Sub blah2()
Row = 5
 
StartPoint1 = "C19:"
EndPoint1 = "C" & 19 + Row
 
StartPoint2 = ",E19:"
EndPoint2 = "E" & 19 + Row
 
StartPoint3 = ",G19:"
EndPoint3 = "G" & 19 + Row
 
StartPoint4 = ",I19:"
EndPoint4 = "I" & 19 + Row
 
ActiveChart.SetSourceData Source:=Sheets("Summary").Range(StartPoint1 & EndPoint1 & StartPoint2 & EndPoint2 & StartPoint3 & EndPoint3 & StartPoint4 & EndPoint4), PlotBy:=xlColumns
End Sub

but it may be easier to:

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

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:

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&3 Letter Words As Upper

try

Public Function fProper(ByVal strInput As String) As String
     
    Dim strOutput       As String
    Dim astrWords()     As String
     
    astrWords = Split(strInput, " ")
    strOutput = ""
     
    For lng = LBound(astrWords) To UBound(astrWords)
    If Not (IsNumeric(Left(astrWords(lng), 1)) Or IsNumeric(Right(astrWords(lng), 1))) Then
        Select Case Len(astrWords(lng))
        Case Is = 2, 3
             'Do Nothing
        Case Else
            astrWords(lng) = Application.WorksheetFunction.Proper _
            (astrWords(lng))
        End Select
   End If
        strOutput = strOutput & astrWords(lng) & " "
    Next lng
     
    fProper = Trim(strOutput)
     
End Function

p45cal

Re: Autofilter To Filter Time Greater & 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:

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:

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 & 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:

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 & 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

Re: Tiled Windows Of Same Workbook Code Effecting Wrong Window

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

wdwData.Activate
wdwDiagram.Activate
wdwInstructions.Activate

to

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 & 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:

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

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:

Sub nameRegions()
Sheet1.Activate
Dim sNm As String, sRT As String
Dim iCol As Long


For iCol = 1 To Range("a1").End(xlToRight).Column
 With Cells(1, iCol)
  sNm = Replace(.Value, " ", "_")
  sRT = "=offset(" & .Address _
  & ", 1, 0, " & .CurrentRegion.Rows.Count - 1 & ")"
 End With
 ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT
Next iCol
End Sub

p45cal