**********************************
COMMENTS
Welcome to the 18th issue of the Excel Experts E-letter (or EEE), by
David Hager. EEE is now a monthly publication. Feel free to distribute
copies of EEE to your friends and colleagues and to contribute your Excel
gems to EEE so that others can benefit from your work.
Back issues are available for download from the EEE web page located on
John Walkenbach's web site. New issues are normally available on the 1st
of each month. There will be periods when EEE is not published due to time
and travel constraints.
http://www.j-walk.com/ss/excel/eee/index.htm
IMPORTANT NOTE!!!
I have just discovered that my mailing list has suffered from tremendous
problems. A significant number of people on the list have been receiving
multiple copies of EEE and over 100 people have been somehow kicked off of
the list over a period of months. The software I am using to manage the
list is old and obviously contains bugs I was not aware of. I have put in
a significant amount of time to try to correct this problem. I apologize
to all of the people that have not been receiving EEE. I hope that this fix
solves the problem.
**********************************
Top Excel Sites
For a list of fixed problems in Microsoft Office 2000 Service Release 1
(SR-1), go to:
http://support.microsoft.com/support/kb/articles/q245/0/21.ASP
**********************************
WORKSHEET FORMULA TIPS
by Bernie Deitrick
I have a formula
=COUNTIF('Sheet1'!Ddd2346, "=0")
in a cell, where Ddd2346 refers to a named range.
What I would like to do is to have an easy way to copy this formula down
a column of cells, and have the Ddd2346 number increment by one each
time. The next cell needs to be Ddd2347, then Ddd2348 etc.
In this specific case, use:
=COUNTIF(INDIRECT("Ddd"&2345+ROW(A1)),"=0")
When this formula is filled down the column, the numeric suffixes of the
named ranges increment by one.
**********************************
POWER FORMULA/FUNCTION TECHNIQUES
by George Simms
I have the following problem:
In cells A1:E1: 1, 2, 3, 4, 5
In cells A2:E2: 6, 7, 8, 9, 10
In cells A5:E5: 21, 22, 23, 24, 25
Now I want to total diagonally (A1, B2, C3, D4 and E5) and I do that by
the formula:
=SUM((ROW(A1:A5)=COLUMN(A1:E1))*(A1:E5)) - array entered.
This works fine (sum =65), but I wondered how can I total diagonally "the
other way" (here A5, B4, C3, D2 and E1)?
To sum A5:E1 diagonally, use:
=SUM(N(OFFSET(E1,ROW(1:5)-1,-1*ROW(1:5)+1)))
Or (not Array Entered):
=SUMPRODUCT(N(OFFSET(E1,ROW(INDIRECT("1:5"))-1,-1*ROW(INDIRECT("1:5"))+1)))
It would be better to use ROW(INDIRECT("1:5") in all the formulas, like
=SUM(N(OFFSET(E1,ROW(INDIRECT("1:5"))-1,-1*ROW(INDIRECT("1:5"))+1)))
as inserting a row above row 5 will change the reference.
To sum across sheets (Sheet1!A1 Sheet2!B2 Sheet3!C3..etc) use:
=SUM(N(INDIRECT("Sheet"&ROW(1:5)&"!"&ADDRESS(ROW(1:5),ROW(1:5)))))
Or (not Array Entered):
=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:5)&"!"&ADDRESS(ROW(1:5),ROW(1:5)))))
**********************************
VBA CODE EXAMPLES
by Tom Ogilvy and Dana DeLouis
I'd like to expand the Custom Autofilter to 3 or more entries.
This procedure assumes you want to display cells that have a,b, and c in
the Cell in column A. If you are looking for multiple "Or" conditions, then
use Union instead of Intersect.
Sub MultCustomAutoFilter()
Dim rng1 As Range
Dim rng2 As Range
Dim rngAll3 As Range
Range("A1").AutoFilter
With [_FilterDatabase].Offset(1, 0)
Range("A1").AutoFilter Field:=1, Criteria1:="*a*", Operator:=xlAnd, _
Criteria2:="*b*"
Set rng1 = .SpecialCells(xlVisible)
Range("A1").AutoFilter Field:=1, Criteria1:="*c*"
Set rng2 = .SpecialCells(xlVisible)
ActiveSheet.AutoFilterMode = False
Set rngAll3 = Application.Intersect(rng1, rng2)
.EntireRow.Hidden = True
rngAll3.EntireRow.Hidden = False
End With
End Sub
This procedure works for Excel 2000. For prior version change the range
object in the With statement to:
ActiveSheet.AutoFilter.Range.Offset(1, 0)
by Bill Manville
Is there a way to reliably code the show detail and hide detail commands
off of the "Data" menu into a VB macro?
This feature is not well supported by VBA. Thus, to hide the detail for the
block within which the cursor sits, use:
ExecuteExcel4Macro "SHOW.DETAIL(1," & ActiveCell.Row & ",FALSE)"
by Stephen Bullen
How can I change the name of the vbcomponent based on the name of the
related worksheet?
The CodeName of a sheet can be changed with:
Sub RenameCodeName(oWks As Sheet, sNewName As String)
oWks.Parent.VBProject.vbComponents(oWks.CodeName) _
.Properties("_CodeName") = sNewName
End Sub
**********************************
POWER PROGRAMMING TECHNIQUE
by Bill Manville
I have to check the contents of a large spreadsheet against a second more
up to date spreadsheet in another workbook. The structure of the 2 workbooks
is the same. How can I identify which cells differ so I can investigate
those individually.
This procedure creates a new workbook which lists the comparison results for
each worksheet in the two workbooks of interest. Each of the two workbooks
should be open prior to running this procedure. Replace the dummy names in the
the DoCompare sub with appropriate filenames.
Sub DoCompare()
Dim WS As Worksheet
Workbooks.Add
For Each WS In WorkBooks("SomeBook.xls").Worksheets
CompareSheets WS, Workbooks("SomeOther.xls").Worksheets(WS.Name)
Next
End Sub
Sub CompareSheets(WS1 As Worksheet, WS2 As Worksheet)
Dim iRow As Integer, iCol As Integer
Dim R1 As Range, R2 As Range
Worksheets.Add.Name = WS1.Name ' new book for the results
Range("A1:D1").Value = Array("Address", "Difference", WS1.Parent.Name, WS2.Parent.Name)
Range("A2").Select
For iRow = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Row, _
WS2.Range("A1").SpecialCells(xlLastCell).Row)
For iCol = 1 To Application.Max(WS1.Range("A1").SpecialCells(xlLastCell).Column, _
WS2.Range("A1").SpecialCells(xlLastCell).Column)
Set R1 = WS1.Cells(iRow, iCol)
Set R2 = WS2.Cells(iRow, iCol)
' compare the types to avoid getting VBA type mismatch errors.
If TypeName(R1.Value) <> TypeName(R2.Value) Then
NoteError R1.Address, "Type", R1.Value, R2.Value
ElseIf R1.Value <> R2.Value Then
If TypeName(R1.Value) = "Double" Then
If Abs(R1.Value - R2.Value) > R1.Value * 10 ^ (-12) Then
NoteError R1.Address, "Double", R1.Value, R2.Value
End If
Else
NoteError R1.Address, "Value", R1.Value, R2.Value
End If
End If
' record formulae without leading "=" to avoid them being evaluated
If R1.HasFormula Then
If R2.HasFormula Then
If R1.Formula <> R2.Formula Then
NoteError R1.Address, "Formula", Mid(R1.Formula, 2), Mid(R2.Formula, 2)
End If
Else
NoteError R1.Address, "Formula", Mid(R1.Formula, 2), "**no formula**"
End If
Else
If R2.HasFormula Then
NoteError R1.Address, "Formula", "**no formula**", Mid(R2.Formula, 2)
End If
End If
If R1.NumberFormat <> R2.NumberFormat Then
NoteError R1.Address, "NumberFormat", R1.NumberFormat, R2.NumberFormat
End If
Next iCol
Next iRow
With ActiveSheet.UsedRange.Columns
.AutoFit
.HorizontalAlignment = xlLeft
End With
End Sub
Sub NoteError(Address As String, What As String, V1, V2)
ActiveCell.Resize(1, 4).Value = Array(Address, What, V1, V2)
ActiveCell.Offset(1, 0).Select
If ActiveCell.Row = Rows.Count Then
MsgBox "Too many differences", vbExclamation
End
End If
End Sub
**********************************
EXCEL TIPS
by John Green
I need a simple macro to take the cell text in a selected cell(s) and add
characters such as "." after the text until the cell is filled to its width.
You get something like:
Text_here.....
and................
here..............
All you need to do is apply a custom format (Format|Cells - Number) to the
cell such as:
@*.
@ is a place marker for the text and the character after the asterisk is
repeated to fill the cell. "*.@" fills to the left.
If you want to do this in code:
Range("A1:A10").NumberFormat = "@*."
Note: If this is done with cells containing numbers, they cannot be operated
on as numbers since they are formatted as text. As a workaround to this
problem, use:
=SUM(VALUE(range)) ' array-entered
instead of
=SUM(range)
by George Simms
The problem I have is that in order to add a value to an existing value
field I need to insert an "=" at the beginning of the cell before it displays
the solution. If I simply type the "+" or "-" after the value Excel displays
the formula (obviously interpreting it as text). Can I perform this task
without inserting the "=" each time?
There is a way to do what you want, if you use it to only edit your data.
From the menu > Tools > Options >Transition tab> check the "Transition
formula entry" box.
It is recommend that once you have edited the data, go back and uncheck
the box. If left checked this can produce some odd results, entering
dates etc.....
**********************************
Issue No.18 OF EEE (PUBLISHED 01Apr2000)
Next issue scheduled for 01May2000.
BY David Hager
dchager@compuserve.com
**********************************