CODEWITHSUNDEEP
vbaexcel
Intriguing
Intriguing

Reputation: 59

Re-calculate formula

I have a Sub with variable "porcen" like this:

Dim porcen as Integer
porcen = Cells(1, 1).Value

After some calculations I have 3 variables. Var1, Var2 and Var3 depend on the variable porcen.

ActiveCell.FormulaR1C1 = "=(IF(R[" & var1 & "]C="""","""",((COUNTIF(R[" & var1+ 1 + var2 & "]C:R[" & var1 + 1 & "]C,R[" & var1 & "]C))-(COUNTIF(R[" & var3 & "]C:R[-5]C,R[" & var1 & "]C)))/(" & var2 & ")))"

I don't know why if I change the value in cell(1,1), the result of the formula remains unchanged.

I wish that when I change the value in Cell(1,1) the result also change.

Upvotes: 0

Views: 74

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

If you change in value in Cells(1,1), you must re-calculate porcen, var1, var2, var3 to reflect that change. You can do this automatically with an event macro.

Upvotes: 3

user4039065
user4039065

Reputation:

It looks like porcen is a long, string or maybe even a variant that gets assigned the Value from A1 (aka Cells(1, 1)). If you want to lock porcen to A1, declare it as a range type variable and Set it to A1.

dim porcen as range
set porcen = cells(1, 1)

' do stuff with porcen.Value or porcen.Row or porcen.Column, etc just like a cell

' some say you don't have to do the next step before exiting the sub. I do it anyways.
set porcen = nothing

Upvotes: 1

Related Questions