Once subtotals are initiated, can navigate to a GrandTotal cell [know how to
do that] but then ...

Have VBA edit that cell adding "/2" to the end of it ... effectively
dividing it by 2 ... whichever cell it ends up being, which is frequently a
different row.

Have been able to accomplish that, but only with a fixed cell address.

Any assistance will be sincerely appreciated.

-Mike

Assuming your Grand total is in Column D

In your code do this:
Dim iLastRow as integer

iLastRow=Range("D65536").end(xlup).row
This will provide you a dynamic Last Row Cell Reference, then you can
substitute your fixed cell reference to something like:

Range("D"& iLastRow).value = "=" & Range("D" &ILastRow).value & "/2"
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"MikeF" wrote:

> Once subtotals are initiated, can navigate to a GrandTotal cell [know how to
> do that] but then ...
>
> Have VBA edit that cell adding "/2" to the end of it ... effectively
> dividing it by 2 ... whichever cell it ends up being, which is frequently a
> different row.
>
> Have been able to accomplish that, but only with a fixed cell address.
>
> Any assistance will be sincerely appreciated.
>
> -Mike

I'm not completely sure what you meant by "once subtotals are initiated".
What is in your GrandTotal cell... a value or a formula?

If a value, try this subroutine (after selecting the GrandTotal cell)...

Sub HalfValue()
ActiveCell.Value = ActiveCell.Value / 2
End Sub

If a formula, try this (again, after selecting the GrandTotal cell)...

Sub HalfFormula()
ActiveCell.Formula = ActiveCell.Formula & "/2"
End Sub

If something else, then please describe your situation in more detail.

--
Rick (MVP - Excel)


"MikeF" <(E-Mail Removed)> wrote in message
news:4050790B-4D75-4618-A98F-(E-Mail Removed)...
> Once subtotals are initiated, can navigate to a GrandTotal cell [know how
> to
> do that] but then ...
>
> Have VBA edit that cell adding "/2" to the end of it ... effectively
> dividing it by 2 ... whichever cell it ends up being, which is frequently
> a
> different row.
>
> Have been able to accomplish that, but only with a fixed cell address.
>
> Any assistance will be sincerely appreciated.
>
> -Mike

You would probably have to use a relative reference to some point that you
know for sure, or a named reference. Look in VBA help under Referring to
Cells Relative to Other Cells and How to Reference Cells and Ranges. There
are illustrations in both these help files that can give you some ideas on
how to do it.
"MikeF" wrote:

> Once subtotals are initiated, can navigate to a GrandTotal cell [know how to
> do that] but then ...
>
> Have VBA edit that cell adding "/2" to the end of it ... effectively
> dividing it by 2 ... whichever cell it ends up being, which is frequently a
> different row.
>
> Have been able to accomplish that, but only with a fixed cell address.
>
> Any assistance will be sincerely appreciated.
>
> -Mike

Rick,

The Sub HalfFormula() worked great.
Thank you, and to all other replies!!

- Mike


"Rick Rothstein" wrote:

> I'm not completely sure what you meant by "once subtotals are initiated".
> What is in your GrandTotal cell... a value or a formula?
>
> If a value, try this subroutine (after selecting the GrandTotal cell)...
>
> Sub HalfValue()
> ActiveCell.Value = ActiveCell.Value / 2
> End Sub
>
> If a formula, try this (again, after selecting the GrandTotal cell)...
>
> Sub HalfFormula()
> ActiveCell.Formula = ActiveCell.Formula & "/2"
> End Sub
>
> If something else, then please describe your situation in more detail.
>
> --
> Rick (MVP - Excel)
>
>
> "MikeF" <(E-Mail Removed)> wrote in message
> news:4050790B-4D75-4618-A98F-(E-Mail Removed)...
> > Once subtotals are initiated, can navigate to a GrandTotal cell [know how
> > to
> > do that] but then ...
> >
> > Have VBA edit that cell adding "/2" to the end of it ... effectively
> > dividing it by 2 ... whichever cell it ends up being, which is frequently
> > a
> > different row.
> >
> > Have been able to accomplish that, but only with a fixed cell address.
> >
> > Any assistance will be sincerely appreciated.
> >
> > -Mike
>
>