Edit Cell with "/2"

M

MikeF

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
 
M

Michael

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"
 
R

Rick Rothstein

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.
 
J

JLGWhiz

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.
 
M

MikeF

Rick,

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

- Mike


Rick Rothstein said:
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 said:
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
Click to expand...
Click to expand...
 
Top