using variable in setting and ActiveCell

  • Thread starter Thread starter dawall33
  • Start date Start date
D

dawall33

I would like to replace the 'a1' with a String variable I have defined
earlier in the script in the following line:

ActiveCell.FormulaR1C1"=SUMIF('1a'!R6C1:R[32]C1,R5C1:R[32]C1,'1a'!R6C10:R[32]C10)"

I am trying to automate the creation of summary lines on a sheet for a
range of sheets in my workbook.
 
maybe...

Dim myName as string
myName = "1A"

ActiveCell.FormulaR1C1 _
= "=SUMIF('" & myname & "'!R6C1:R[32]C1,R5C1:R[32]C1,'" _
& myname & "'!R6C10:R[32]C10)"


Untested, uncompiled.
I would like to replace the 'a1' with a String variable I have defined
earlier in the script in the following line:

ActiveCell.FormulaR1C1"=SUMIF('1a'!R6C1:R[32]C1,R5C1:R[32]C1,'1a'!R6C10:R[32]C10)"

I am trying to automate the creation of summary lines on a sheet for a
range of sheets in my workbook.
Click to expand...
 
strSheet = "1a"
ActiveCell.FormulaR1C1="=SUMIF('" & strSheet &
"'!R6C1:R[32]C1,R5C1:R[32]C1,'" & strSheet & "'!R6C10:R[32]C10)"
 
Something like....

Dim sVar as String
sVar = "A1"
ActiveCell.FormulaR1C1"=SUMIF(" & sVar & "!R6C1:R[32]C1,R5C1:R[32]C1," &
sVar & "!R6C10:R[32]C10)"
 
Whenever you have a text String value and you want to insert the contents of
a String variable into that text String, you would break the text String at
the location you want the String variable's contents to go and concatenate
the String variable at that location. In your case (assuming it is the "1a"
that you show in your formula that you want replaced)...

ActiveCell.FormulaR1C1 "=SUMIF('" & YourVariable & _
"'!R6C1:R[32]C1,R5C1:R[32]C1,'" & _
YourVariable & "'!R6C10:R[32]C10)"

Just change my example String variable name of YourVariable to your actual
variable's name.

Note: I used line continuation characters to avoid having your newsreader
break the line at inappropriate locations... the above is really a single
statement.
 
Back
Top