Hi All,

I am using VBA to get some records from an Oracle db and then sort and
subtotal them. Everything works fine except that I would like one of
the fields which does not drive the subtotals to be displayed next the
field which shows Item Total. Example:

Account Name | Account # | Amount
Overpayment | 12345678 | 23.00
Overpayment | 12345678 | 77.00

If you drive your subtotals off of Account # then when you collapse the
subtotal one level you will see only 12345678 Total and the Account
Name field will be empty. This normally makes sense because the
subtotaled list could potentially have different information in each
Account Name field. But in my case it is always the same for each
subtotal group. Does anyone know how to programatically make it display
when it is collapsed? I guess I could evaluate the cell above it and
take that value but maybe someone knows a better way here?

Thanks
Mike
msilva@ed (dot) umuc (dot) edu



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Works like a charm - Thanks Tom!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Actually only works like a charm if you want the same value across all
subtotals. I will work out how to loop through each subtotal and
display the proper value for that subtotal. Unless someone already has
it done?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Perhaps a modification:

Set rng = Range(Cells(2,"A"),Cells(rows.count,"A").End(xlup) (2))
set rng1 = rng.SpecialCells(xlblanks)
rng1.Formula = "=" & rng1(0,1).Address(0,0)
rng.Formula = rng.Value

I that should fix it. Pardon my omission.

--
Regards,
Tom Ogilvy


"rhody" wrote in message
...
Actually only works like a charm if you want the same value across all
subtotals. I will work out how to loop through each subtotal and
display the proper value for that subtotal. Unless someone already has
it done?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

This was a great tip for creating a subrange of just the blank cells in
a range so that a value could be put in there to aid the user when the
subtotals are collapsed to level 2.

Can this same method be used for finding cells in a range which contain
a certain value and then changing the value in the adjacent cell? So
instead of using:

Set rng = Range("E2:E" & EndCell)
Set rng1 = rng.SpecialCells(xlBlanks)

Maybe something like

Set rng = Range("E2:E" & EndCell)
Set rng1 = rng. ??? Where value = "Foo"

Any ideas?

TIA
Mike



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Easiest thing to do is:

For Each c In Range("E2:E" & RowCnt)
If c.Value = "Your Value" Then
c.Offset(0, 1).Value = "New Value"
FindCnt = FindCnt + 1
End If
Next c



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Easiest thing to do is:

For Each c In Range("E2:E" & RowCnt)
If c.Value = "Your Value" Then
c.Offset(0, 1).Value = "New Value"
FindCnt = FindCnt + 1
End If
Next c



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

No it can't - there isn't an option to do a special cells on a particular
value. Look in Excel VBA help on the Find or FindNext methods to see code
for finding a value in a range.

--
Regards,
Tom Ogilvy

"rhody" wrote in message
...
This was a great tip for creating a subrange of just the blank cells in
a range so that a value could be put in there to aid the user when the
subtotals are collapsed to level 2.

Can this same method be used for finding cells in a range which contain
a certain value and then changing the value in the adjacent cell? So
instead of using:

Set rng = Range("E2:E" & EndCell)
Set rng1 = rng.SpecialCells(xlBlanks)

Maybe something like

Set rng = Range("E2:E" & EndCell)
Set rng1 = rng. ??? Where value = "Foo"

Any ideas?

TIA
Mike



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/