Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Adding in a calc using cell refernces

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Adding in a calc using cell refernces

Resolved · High Priority · Version 2007

Reply Thu 14 Jul 2011, 11:03Delegate 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

For upcoming training course dates see: Pricing & availability

Reply Thu 14 Jul 2011, 16:47Trainer 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

Thanks

Stephen

Reply Mon 19 Sep 2011, 10:44Trainer 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

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

Regards

Simon

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

RE: Adding in a calc using cell refernces

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

Ta

D

Access articles

· How To Make One Combo Show Values Determined By Another Combo In Access
· Quick Access Course: Creating a Customer Contact Database in 8 Simple Steps
· Access VBA Training Adds Power to your Databases
· Access: Things You Probably Already Know
· How Does The Startup Work When Access 2010 Launches The Northwind Database

Please browse our web site to find out more about
access advanced courses and other Microsoft training courses.

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