Evaluate Formula in VBA

[cmore]

I'm trying to figure out the best way to evaluate this formula into a cells on a relative basis. Hate R1C1, but will do if have to. Thoughts?

=TEXT(F78,"MMMM YYYY")&IF(IF(COUNTIF(F$7:F78,F78)>1,"V"&COUNTIF(F$7:F78,F78),"")<>""," - "&IF(COUNTIF(F$7:F78,F78)>1,"V"&COUNTIF(F$7:F78,F78),""),"")


p.s. How do you build nested formulas using the VBA functions?

[venkat1926]

does this formula give you a correct andwer in a spreadsheet cell. if so

then you are not using any variabale. then WHAT IS THE NEED OF THE MACRO WHY NOT USE FORMLA AND COPY DOWn particularly the formla is ccomplex

anhow ses this macro does it satsify you
the result is on H1. if this is cell is already filled take it some other empty cell
depends upon congtents and configuration of data, without which it is difficlt to solve your problem

Please Login or Register  to view this content.

[cmore]

The formula does work I'm trying to automate such that everything populates on worksheet change

[JosephP]

why can't you put the formula in the cells? you can convert to values afterwards if necessary

[cmore]

That's what I was thinking, but was hoping there was more direct way?

[JosephP]

what's the difference? if your code adds the formula then converts it to a value it's not going to be more noticeable than using Evaluate, and it will be a lot simpler to do! :-)

[cmore]

Ohhh, so that's my issue it seems like it would be more of a pain, but I admittedly know nothing. So I'm thinking drop all the formulas, then specials cells for formulas, copy paste values? or am I under thinking?

[cmore]

Updated file, New problems

[JosephP]

wrong thread?

[cmore]

Yes wrong thread Cannot get ExcelForum to work from home, it's throwing me off, going to try and send formulas to cells then use special cells to copy and paste values, unless there's simpler solution for this?

[JosephP]

just assign the formula to the cell(s) then convert to values directly

Please Login or Register  to view this content.

for instance

[cmore]

Duhhh, good call, solved and solved. I just cannot understand how/why we can insert a formula relatively but cannot evaluate so sad.

[JosephP]

we put the formula in a specific cell so it has context; the evaluate method belongs to the worksheet or application and has no cell context. therefore you would need to calculate absolute addresses based on which cell you want the value in and then evaluate that formula. way simpler to just put the formula into one or more cells and let excel do its thing :-)

[cmore]

makes sense! So I'd have to write some script to adjust or write formulas in R1C1. Boo to evaluate, powerful method with slight shortcomings. Thanks for your help. Next phase of my tear down/rebuild has been pretty painful. Will post new topic if needed. Thank you thank you