Adding in a calc using cell refernces

Resolved · High Priority · Version 2007

Thu 14 Jul 2011, 11:03 Delegate Donna said...

Donna has attended:
Excel VBA Intro Intermediate course

Adding in a calc using cell refernces

I want to use my named VBA integer which holds the Row size of the imported table to add in a % calc lower down on a newly created copied table.

I've tried loads and assumed my best option was:

Application.WorksheetFunction.???
However I can find one for = Same column up 24 rows (Inrowcount + 2) / same column up 15 rows (introwcount - 9)

Ideally I like the introwcount to be $introwcount as all percentages will be driven from this row.

This one worked if type but I can't use the cell refences for it:

ActiveCell.FormulaR1C1 = "=(R[-24]C/R[-15]C)"

Tried

'Sheets(StrShtName).Cells(1, 1).Value = "+B" & Cells(-24, 1) & "/B$" & Cells(-16, 1)

ActiveCell.FormulaR1C1 = "=(R[-24]C/R[-15]C)"

ActiveCell.FormulaR1C1 = "(R[" &intpercentslave& "C/R[" &intpercentmaster"&]C)"

Please help

Donna Leach

Thu 14 Jul 2011, 16:47 Trainer Stephen said...

RE: Adding in a calc using cell refernces

Hi Donna

Thanks for the question

This is tricky to solve without seeing the actual worksheet. Could you send me an example of the data and I will build a function for you that solves the problem.

My email is sw@stl-training.co.uk

Thanks

Stephen

Mon 19 Sep 2011, 10:44 Trainer Simon said...

RE: Adding in a calc using cell refernces

Hi Donna,

I am just following up the outstanding question on out Forum.

Unfortunately Stephen is out of action and has been for a couple of months.

Can I check whether you sent the excel spreadsheet to Stephen after his request?

If you didn't and it hasn't been resolved, please send it to:

sk@stl-training.co.uk

My VBA colleagues and I can then have a look and see whether we can resolve your Post for you.

Regards

Simon

Mon 19 Sep 2011, 11:14 Delegate Donna said...

RE: Adding in a calc using cell refernces

I found it all by myself, Dim ***** Long, seems to work ok.

Ta

D

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Hiding rows and columns

To hide a column or row in Access, you need to minimise the column or row. This can be done by placing your mouse on the right edge of a column, wait for the mouse to change to a double aroow and then drag to minimise the column

View all Access hints and tips