Storing values in a name

[Kendo]

I am using the following code to store a formula in a range name. Is there a way to store the value derived from that formula in another name so that the next time the macro is run it can compare the old value to the current value.

Names.Add name:="TotalExp", rrefersTo:="=Sum(Sheet1!$D:$D)"

Any suggestions would be appreciated.

 

[kumarapush123]

Hope this helps.

Names.Add Name:="TotalExp1", RefersTo:=WorksheetFunction.Sum(Sheet1.Range("D:D"))

To know more on using excel formula in VBA, try reading this link

 

[Kendo]

Hi Kumarapush,
Your code worked perfectly. Thanks for the help

Regards
Kendo

 

[Kendo]

Hello again,
I seem to be missing something.
I used your code to get the correct value which I can insert into a worksheet but I cannot do any calculations within VBA; for example I want to deduct the value in "TotalExp1" from the value in "TotalExp2" But when I attempt to run the macro I get Run time error 13 Type mismatch.
What am I missing?

 

[Rick Rothstein]

Hello again,
I seem to be missing something.
I used your code to get the correct value which I can insert into a worksheet but I cannot do any calculations within VBA; for example I want to deduct the value in "TotalExp1" from the value in "TotalExp2" But when I attempt to run the macro I get Run time error 13 Type mismatch.
What am I missing?

Show us the code you tried to use that produced that error.

 

[Kendo]

My code is:


Names.add Name:="NewTotExp1", RefersTo:=WorksheetFunction.Sum(Sheet1.Range("D:D"))
CurrExp = Names("NewTotExp1").Value
PrevExp = Names("TotalExp1").Value

Mydiff = CurrExp - PrevExp

 

[Rick Rothstein]

My code is:


Names.add Name:="NewTotExp1", RefersTo:=WorksheetFunction.Sum(Sheet1.Range("D:D"))
CurrExp = Names("NewTotExp1").Value
PrevExp = Names("TotalExp1").Value

Mydiff = CurrExp - PrevExp

How did TotalExp1 get created? Is it a cell reference or a calculated value (like you did with NewTotExp1)?

 

[Kendo]

Sorry,

Yes it is calculated; the same as NewTotExp1

 

[kumarapush123]

Is this resolved Kendo???

Try F8 instead of F5. This will execute the code line by line. And it will be easy to debug what is wrong with the code.
You can try to add 'quick watch' for PrevExp variable and get some clue about the error.

 

[Kendo]

No I have not resolved the problem.
I am getting a run time error 13 Type Mismatch at the line MyDff = CurrExp - PevExp
When I hover over PrevExp it shows the value as "=1152" Likewise with CurrExp it shows the value as "=2392"
Do the quotes around the values indicate that they are of type string.
If s How do I convert them to integer?