VBA and subtotals
 

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/

VBA and subtotals
 

Works like a charm - Thanks Tom!



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

VBA and subtotals
 

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/

VBA and subtotals
 

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/

VBA and subtotals
 

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/

VBA and subtotals
 

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/

VBA and subtotals
 

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/

VBA and subtotals
 

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/

VBA and subtotals
 

If you are going to replace the value, the easiest thing to do and by far
the fastest would be to turn on the macro recorder and use Edit=Replace
from the menu. Then turn it off and modify the code to meet your needs.

--
Regards,
Tom Ogilvy

"rhody" wrote in message
...
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/

VBA and subtotals
 

True, but I am replacing the value in the cell next to the one I
searched for. The value I searched for does not change only the one
next to it. Basically, I have a list of accounts and the account name
is correct but the account number is not. That same account number is
correct for another account so I do not want to do a global search and
replace on that just the one because it would change some values which
are correct. The code snippet here doesn't seem to have a unreasonable
performance hit.

Mike



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