VBA - Sheet name variable for property .FormulaR1C1

Hurkk

New Member
Joined
Jan 17, 2018
Messages
13
Hi,

How can I insert a variable for the sheet name.

Example: I want sum cells A1:B10 in sheet1i=1

Range("A1").FormulaR1C1 = " =sum('sheet & i & '!R1C1:R10C2)" --> I know this is the wrong way to do it...

Thanks in advance!

Hurkk
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Very close . . .

Code:
Range("A100").FormulaR1C1 = "=sum('sheet" & i & "'!R1C1:R10C2)"
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
Range("A1").FormulaR1C1 = "=SUM('Sheet" & i & "'!R1C1:R10C2)"
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top