BreadCrumbs: Excel

Excel

From Luke Jackson

(Difference between revisions)
Jump to: navigation, search
Revision as of 02:47, 27 September 2008 (edit)
Ljackson (Talk | contribs)

← Previous diff		 Revision as of 04:31, 27 September 2008 (edit)
Ljackson (Talk | contribs)

Next diff →
Line 1: Line 1:
= Windows XP = = Windows XP =
 +
 +== Conditional Formating "At Change In Value" =
 +
 +<pre>
 +Private Sub Workbook_Open()
 +
 + Dim C As Variant
 + Dim Clast As Variant
 + Dim CI As Integer
 + Dim Col As Variant
 + Dim FirstRow As Long
 + Dim LastRow As Long
 + Dim Rng As Range
 + Dim Wks As Worksheet
 + Dim Color As Boolean
 +
 + Col = "A"
 + FirstRow = 2 'Assumes header row is row 1
 + Set Wks = Worksheets("Scoreboard")
 + LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
 + LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
 +
 + Set Rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
 + For Each C In Rng
 + If IsDate(C) Then
 +
 + If Clast = "" Then
 + CI = 25
 + Clast = C
 + 'MsgBox "Start " & C & " - " & Clast
 + ElseIf C <> Clast And Color = False Then
 + CI = xlColorIndexNone
 + Color = True
 + 'MsgBox "CF CNE " & C & "<>" & Clast
 + Clast = C
 + ElseIf C <> Clast And Color = True Then
 + CI = 25
 + Color = False
 + 'MsgBox "CT CNE " & C & "<>" & Clast
 + Clast = C
 + ElseIf C = Clast And Color = False Then
 + CI = 25
 + 'MsgBox "CF CE " & C & "=" & Clast
 + Clast = C
 + ElseIf C = Clast And Color = True Then
 + CI = xlColorIndexNone
 + 'MsgBox "CT CE " & C & "=" & Clast
 + Clast = C
 + Else
 + CI = xlColorIndexNone
 + MsgBox "Else " & C
 + End If
 +
 + C.Interior.ColorIndex = CI
 +
 + End If
 + Next C
 +
 +End Sub
 +</pre>
== Function == == Function ==

Revision as of 04:31, 27 September 2008

Contents

Windows XP

= Conditional Formating "At Change In Value" 

Private Sub Workbook_Open()

  Dim C As Variant
  Dim Clast As Variant
  Dim CI As Integer
  Dim Col As Variant
  Dim FirstRow As Long
  Dim LastRow As Long
  Dim Rng As Range
  Dim Wks As Worksheet
  Dim Color As Boolean
  
    Col = "A"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("Scoreboard")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
    
      Set Rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        For Each C In Rng
          If IsDate(C) Then
            
            If Clast = "" Then
                CI = 25
                Clast = C
                'MsgBox "Start " & C & " - " & Clast
            ElseIf C <> Clast And Color = False Then
                CI = xlColorIndexNone
                Color = True
                'MsgBox "CF CNE " & C & "<>" & Clast
                Clast = C
            ElseIf C <> Clast And Color = True Then
                CI = 25
                Color = False
                'MsgBox "CT CNE " & C & "<>" & Clast
                Clast = C
            ElseIf C = Clast And Color = False Then
                CI = 25
                'MsgBox "CF CE " & C & "=" & Clast
                Clast = C
            ElseIf C = Clast And Color = True Then
                CI = xlColorIndexNone
                'MsgBox "CT CE " & C & "=" & Clast
                Clast = C
            Else
                CI = xlColorIndexNone
                MsgBox "Else " & C
            End If

            C.Interior.ColorIndex = CI
            
          End If
        Next C

End Sub

Function 

=IF(INDIRECT(ADDRESS(ROW(),1,3))<>INDIRECT(ADDRESS((ROW()-1),1,3)),1,0)

Date Filters Not Available for Pivot Table / Chart

Date Filters are not supported in Excel 2003 compatibility mode.

Re-save document in Excel 2007 format and the options will no longer be greyed out.

Mac OS X

File Not Found

When starting Microsoft Excel 2004 for Mac OS X you may get the error "File Not Found".

First place to check is the Excel Startup folder: 

ljackson 14:46:50 ~/Library/Preferences> cd /Applications/Microsoft\ Office\ 2004/Office/Startup/
ljackson 14:47:33 /Applications/Microsoft Office 2004/Office/Startup> ll
drwxrwxr-x   4 ljackson  admin  136B May  3 14:39 Excel
drwxrwxr-x   2 ljackson  admin  68B Apr  8  2004 PowerPoint
drwxrwxr-x   2 ljackson  admin  68B Apr  8  2004 Word
ljackson 14:47:33 /Applications/Microsoft Office 2004/Office/Startup> cd Excel/
ljackson 14:47:37 /Applications/Microsoft Office 2004/Office/Startup/Excel> ll
total 96
-rw-r--r--   1 ljackson  admin    25K May  3 14:15 8DAE9700
-rw-r--r--   1 ljackson  admin    16K May  3 14:39 Personal Macro Workbook

In my case the alphanumerical file was generated when Excel crashed. So I will delete it: 

ljackson 14:47:38 /Applications/Microsoft Office 2004/Office/Startup/Excel> rm -f 8DAE9700 
ljackson 14:47:53 /Applications/Microsoft Office 2004/Office/Startup/Excel> ll
total 40
-rw-r--r--   1 ljackson  admin    16K May  3 14:39 Personal Macro Workbook

Now I don't get the anoying error "File Not Found" every time I launch Excel.

VBA Macros 

Sub RangeDateFix()
    Dim ndate As String
    'ActiveCell.CurrentRegion.Cells.Select
    For Each c In ActiveWindow.RangeSelection.Cells
        ndate = USdateEU(c.Value)
        c.Value = ndate
    Next
End Sub

Public Function USdateEU(ByVal tdate As String)
    Dim txt As String, ftxt As String, x As Variant, i As Long
    txt = tdate
    x = Split(txt, "/")
    'For i = 0 To UBound(x)
       'MsgBox x(i)
    'Next i
    ftxt = x(1) & "/" & x(0) & "/" & x(2)
    
    USdateEU = ftxt
    'MsgBox ftxt
End Function

Public Function Split(ByVal sInput As String, _
Optional ByVal sDelimiter As String, _
Optional ByVal nLimit As Long = -1, _
Optional ByVal bCompare As Integer = vbBinaryCompare _
) As Variant
  
Dim nCount As Long
Dim nPos As Long
Dim nDelimiterLength As Long
Dim nStart As Long
Dim sOutput() As String
  
If nLimit = 0 Then
    Split = Array()
Else
    nDelimiterLength = Len(sDelimiter)
    
    If nDelimiterLength = 0 Then
        Split = Array(sInput)
    Else
        nStart = 1
        nPos = InStr(nStart, sInput, sDelimiter, bCompare)
    
    Do While nPos
      
    ReDim Preserve sOutput(0 To nCount) As String
      
        If nCount + 1 = nLimit Then
            sOutput(nCount) = Mid(sInput, nStart)
            Exit Do
        Else
            sOutput(nCount) = Mid(sInput, nStart, nPos - nStart)
            nStart = nPos + nDelimiterLength
        End If
      
    nCount = nCount + 1
      
    nPos = InStr(nStart, sInput, sDelimiter, bCompare)
      
    Loop
      
    ReDim Preserve sOutput(0 To nCount) As String
      
    sOutput(nCount) = Mid(sInput, nStart)
      
    Split = sOutput
      
    End If
  
End If
  
End Function

Souces

Retrieved from "http://wiki.ljackson.us/Excel"
Personal tools