Question: how to use variables in formulas?

[Osmond]

Hey,

I'm stuck with this one, please help. I'm trying to use variable in formula but excel gives Run-time error 1004: Application-defined or object-defined error.

Original and working line in the code was:

Cell.Offset(0, 3).FormulaR1C1 = "=(1-COS((6.28318*RC[-3])/102.3))/2"

Then I tried to replace value 102.3 with variable MaxTime (value defined earlier in code):

Cell.Offset(0, 3).FormulaR1C1 = "=(1-COS((6.28318*RC[-3])/" & MaxTime & "))/2"

Any idea why this doesn't work? Also following line works without problems:

Cell.Offset(0, 3).FormulaR1C1 = "=(1-COS((6.28318*RC[-3])/" & "102.3" & "))/2"

[TMS]

What's in MaxTime? In Excel and, therefore, VBA, a time is represented by a numeric value which is a fraction of a 24 hour period (a day). So, 0.25 is 06:00, 0.5 is 12:00 PM, 0.75 is 18:00, etc. MaxTime sort of implies you have a time value in there so perhaps it isn't equivalent to 102.3


Regards, TMS

[Osmond]

Thank you for your reply. I have data set where I have time variable, and MaxTime is the highest value in the data set. I used msgbox to confirm that MaxTime has value 102,3.

I began to doubt that decimal separator "," has something to do with error. Indeed, posted line works well when MaxTime has integer value. After this I changed default decimal separator from options of Excel from "," to ".". Unfortunately, msgbox still says that value of MaxTime is 102,3 and posted line doesn't work.

[TMS]

Maybe try something like:

Please Login or Register  to view this content.

Regards, TMS

[Osmond]

Simple replacement didn't work. I had to make second variable which is defined as string:

Dim MaxTime2 As String
MaxTime2 = Replace(MaxTime, ",", ".")

When I use this sting variable in formula, it works! Changing language settings from windows also helps but this solution is much better.

Case closed, solution found. Thanks TMS!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[dlow]

"Case closed, solution found" could someone mark this as solved I read through all that to see if I could help & its solved.

[Osmond]

Sorry, I'm new here and didn't know the policy...

[TMS]

@Osmond: no problem, thanks for the rep.

@dlow: It is the responsibility of the OP to mark a thread solved, but, as in this case, new members are not always familiar with the etiquette and rules of the forum. Sometimes a Moderator or Administrator will mark a thread closed based on the content of the post(s), but not always.

But, just because a thread is solved and/or marked solved, it doesn't mean you can't contribute if you have an alternative and/or better solution. In this case, we have come up with a compromise. However, I'm not convinced it is the only, or the best, solution. It does, though, solve the immediate problem in the absence of any other offerings.

Regards, TMS