Replies

Great solution, Hans. I didn’t even think to use SUMIF for this. Funny thing is that I was playing around with the problem today, and came across a solution using LOOKUP instead of VLOOKUP or MATCH/INDEX. Like this:

=SUMPRODUCT(A8:A13*LOOKUP(B8:B13,F7:F30,G7:G30))/100

However, I think your solution is preferable because LOOKUP is fussy about the search order and thus is more prone to error if something is not in the proper order.

Thanks,
Ken

Thanks to all for the input. Looks like I’ll be using an intermediate column.

Ken

Thanks Jezza, Rory, and Hans. Got lots of options now.

Ken

Here’s an array formula that will find the last “/” no matter how many owners. Enter using Ctrl-Shift plus Enter

=RIGHT(C2,LEN(C2)-MAX(IF(MID(C2,ROW(INDIRECT(“1:”&LEN(C2))),1)=”/”,ROW(INDIRECT(“1:”&LEN(C2))),0)))

Ken

An alternative would be to enter an apostrophe in the blank linked cell which forces excel to see null text.

for my verson of Excel (2000) if I append /e to the command in the shortcut Excel opens with no workbook.

Ken

Here’s a more generic version that I assigned a hot key to. Just select any group of cells, hit the hot key and paste the sum where you will.

Sub SelectionSumCopy()
‘may need to create and delete a userform in order
‘to force a reference to MS Forms 2.0 Object Lib
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText Application.WorksheetFunction.Sum(Selection)
MyData.PutInClipboard
End Sub

Fred

Here is a version with conditional formatting that highlights only grades just below the threshold of a higher grade.

Ken

Thank you!

Ken

See if this VBA subroutine will help.
AddISERROR will take a formula and wrap “=IF(ISERROR(FORMULA),””,FORMULA)” if there is no ISERROR already in the formula. If you have a really long formula this might create a resulting formula too long for XL to handle.

 
Sub AddISERROR()
Dim rngCell As Range
Dim I As Long
Dim iCalculationState As Integer
Dim Work As String
iCalculationState = Application.Calculation
Application.Calculation = xlCalculationManual
For Each rngCell In Selection
    If rngCell.HasFormula Then
        If InStr(rngCell.Formula, "ISERROR") = 0 Then
            Work = Right(rngCell.Formula, Len(rngCell.Formula) - 1)
            rngCell.Formula = "=IF(ISERROR(" & Work _
                                   & "),""""," & Work & ")"
        End If
    End If
Next
Application.Calculation = iCalculationState
Application.Calculate
End Sub

Also, I recall some months back a user-defined error function created by some loungers
that only required one instance of the formula being tested, not two.
Later Found it!: post 87,625
Ken

Folluwup:
I emailed a copy of the troublesome template to MIS who checked it on his machine using the same printer driver as I had and he did not have a problem printing. Then, later I came across a prior version of the template I had saved, so I tested it…it printed fine. Then I updated the old version with the changed code in the newer troublesome version. Problem solved. Seems there was a corruption in my template that didn’t show up under 97 that did show up under XP.

Indeed a more elegant solution. The problem is that the delimiter is (as far as I can tell, and I’m no expert in VBA) a “sticky” argument, but not an accessible property. If there’s a way to do what you suggest, I’d really like to know what it is. Reapplying the TextToColumns method with a different delimiter is the only way I’ve been able to get around it.

Thanks,
Ken

Thank you for the response. Seems the retention of TextToColumns delimiter characters is another MS feature that can be a nuisance. To resolve the problem I decided to use the principle that a program should clean up after itself. So in my macro that used the TextToColumns method, added at the end, I had it run a “dummy” run of the method by 1) forcing the selection to a single cell, 2) saving the contents in a variable, 3) putting a space character in the cell, 4) running the TextToColumns method using a space as the delimiter on the single cell, and 5) restoring the contents of the active cell. This resets the delimiter data in Excel so it ignores decimal points previously used.

Thanks Hans! Works Great.!

Thanks. I thought it would be something simple. Duh.