Hi Guys.

Ok i'll try and explain this as simply as i can.

I want to create an expenses sheet which allocates the net amount to
individual cells depending on which cell is highlighted, by clicking a button.

So in cell B10 i put my gross amount = 100.00
C10 works out the VAT for me = 14.89

Now the user highlights the cell (in different columns) under the
appropriate heading eg. 'Petrol' and clicks the button at the top of the
sheet which automatically puts 85.11 in the highlighted cell.

Sounds simple.

I have created the button and i have created the Macro:
ActiveCell = "=B10-C10"

My question is, how can i make the macro formula relative to which cell i
have highlighted?

At present everytime i click it obviously just works out B10 - C10! Say i
went onto the next item in the list and had my amounts in B11 and C11, how
can i make the macro relative to the next row down?

Thanks for your time.

Using an R1C1 formula is the way to do it:

ActiveCell.FormulaR1C1 = "=RC2-RC3"

Regards,
Per

"Bazy2k" skrev i meddelelsen
...
Hi Guys.

Ok i'll try and explain this as simply as i can.

I want to create an expenses sheet which allocates the net amount to
individual cells depending on which cell is highlighted, by clicking a
button.

So in cell B10 i put my gross amount = 100.00
C10 works out the VAT for me = 14.89

Now the user highlights the cell (in different columns) under the
appropriate heading eg. 'Petrol' and clicks the button at the top of the
sheet which automatically puts 85.11 in the highlighted cell.

Sounds simple.

I have created the button and i have created the Macro:
ActiveCell = "=B10-C10"

My question is, how can i make the macro formula relative to which cell i
have highlighted?

At present everytime i click it obviously just works out B10 - C10! Say i
went onto the next item in the list and had my amounts in B11 and C11, how
can i make the macro relative to the next row down?

Thanks for your time.

One way...

With ActiveCell
.Formula = "=B" & .Row & "-C" & .Row
End With
--
HTH...

Jim Thomlinson


"Bazy2k" wrote:

Hi Guys.

Ok i'll try and explain this as simply as i can.

I want to create an expenses sheet which allocates the net amount to
individual cells depending on which cell is highlighted, by clicking a button.

So in cell B10 i put my gross amount = 100.00
C10 works out the VAT for me = 14.89

Now the user highlights the cell (in different columns) under the
appropriate heading eg. 'Petrol' and clicks the button at the top of the
sheet which automatically puts 85.11 in the highlighted cell.

Sounds simple.

I have created the button and i have created the Macro:
ActiveCell = "=B10-C10"

My question is, how can i make the macro formula relative to which cell i
have highlighted?

At present everytime i click it obviously just works out B10 - C10! Say i
went onto the next item in the list and had my amounts in B11 and C11, how
can i make the macro relative to the next row down?

Thanks for your time.

PERFECT!!!

Thankyou for your speedy response!!

"Per Jessen" wrote:

Using an R1C1 formula is the way to do it:

ActiveCell.FormulaR1C1 = "=RC2-RC3"

Regards,
Per

"Bazy2k" skrev i meddelelsen
...
Hi Guys.

Ok i'll try and explain this as simply as i can.

I want to create an expenses sheet which allocates the net amount to
individual cells depending on which cell is highlighted, by clicking a
button.

So in cell B10 i put my gross amount = 100.00
C10 works out the VAT for me = 14.89

Now the user highlights the cell (in different columns) under the
appropriate heading eg. 'Petrol' and clicks the button at the top of the
sheet which automatically puts 85.11 in the highlighted cell.

Sounds simple.

I have created the button and i have created the Macro:
ActiveCell = "=B10-C10"

My question is, how can i make the macro formula relative to which cell i
have highlighted?

At present everytime i click it obviously just works out B10 - C10! Say i
went onto the next item in the list and had my amounts in B11 and C11, how
can i make the macro relative to the next row down?

Thanks for your time.

.