VBA and subtotals

[rhody]

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

 

[rhody]

Works like a charm - Thanks Tom!

 

[rhody]

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?

 

[Tom Ogilvy]

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.

 

[rhody]

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

 

[rhody]

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

 

[Tom Ogilvy]

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.

 

[rhody]

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

 

[Tom Ogilvy]

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.

 

[rhody]

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