Insert fumctions in other sheets where the address of the target cell varies

[Richard in Redditch]

Hello.
I have an application where I want to put a formula in one sheet of a workbook where the formula addresses another sheet. I am trying to use code such as this:
ActiveCell.Formula = "=summary! & Cells(LastRowNo + 1, 5)". Although I know this syntax is incorrect, I hope it is sufficiently apparent so that my intention is clear. Please can anyone help?
Richard

 

[Snakehips]

Richard,

Try like...

     ActiveCell.Formula = "='summary'!" & Cells(LastRowNo + 1, 5).address

I assume you are ok to establish the variable LastRowNo ?

Hope that helps.

 

[Richard in Redditch]

Hello again - and many thanks as well!

Now the code works like a dream. I couldn't find any reference to the .address modifier in any of my Excel books (I use Excel 2013 on W7), so can you point me in the direction of any good ones?

Thanks again from one happy bunny.

 

[Snakehips]

You are welcome.

As for books here is a link to HIKER"S LIST which lists a variety of resources.

I've not been a big user of Excel books. Google is your friend especially YouTube.
And there is a lot to be learned from the forum of course.

When you type e.g. ActiveCell. in the vb editor you should get a dropdown of available methods and properties.
Same when you have variables if you have declared their type.
Also Google use of the vb editor's Object Browser.

Enjoy!

 

[Richard in Redditch]

Hello
A very big "thank you" to everybody who has helped me out with the answer to my query. I did accidentally post it twice - I thought I had not got it right the first time, so apologies to anyone who thought I was taking the mickey. What a wonderful forum!
Richard