Converting formula to VBA code

[Saky]

Friends,

I need your help to convert a simple formula to VBA code to reflect data of cell Y1 in cell F1.

Formula is

Please Login or Register  to view this content.

. Can it be done using VBA code.

Really i need your help to do it.

Please assist.

Thanks Saky

[StephenR]

You can always record a macro to get the code for things like this:

Please Login or Register  to view this content.

[Saky]

Friend,

Thanks for your response. But if i record a macro and run it works fine.

But for ex i am giving range from F1 to F100 then if i run the macro it pulls data till F100. But if there is no data in Y100 then F1 shows as 0. That is where the problem.

Thats y i dont prefer record macro... Can it be done using worksheet change event???

[StephenR]

This maybe? Not sure I follow you.

Please Login or Register  to view this content.

[Saky]

I found a VBA code for the same

Please Login or Register  to view this content.

it works fine but the problem is the data is reflecting in column F only if any action is performed in column Y. But the datas in column Y is automatically getting calculated by a formula. So is there any changes that can be made to this code to work??

[royUK]

Why do you need this? What are you going to do with the code?

Please Login or Register  to view this content.

[Saky]

I am preparing a report excel for my team. do you want me to detaily explain about my project?

If you need tell me i will explain you.

Even the above code works as the same...can you help me with FOR loop codings so i believe every time when i run the macro the formula will get pasted one by one. first click the formula will be in first cell and in the second click it will come to the next cell...is it possible??

[royUK]

IThe code that posted puts the formula into F1:F100, not displaying zeros. Your code looks like it should be in the WorkSheet_Change event

[Saky]

Yes it is in worksheet change event. It works fine but the column F reflects data only when any action is performed in column Y. But i want the column F to be automatically populated once the data is populated in column Y.

Column Y consists of formula like

Please Login or Register  to view this content.

. so it will automatically calculate the date once the column i and x is filled. But i need the date to be refelected in column F once the column Y is populated.

Can it be done???

[royUK]

Is this what you mean

Please Login or Register  to view this content.

[Saky]

Roy, Thanks this what i exactly expecting. This code works fine but if you see this code works fine but the Cell F1 is reflecting the data only when i go and click or press F2 in cell Y1. So it requires some trigger in cell Y1.

cant it be done automatically. Because i will never click cell Y1 everytime as because it reflects the data automatically as it consists of formula

Please Login or Register  to view this content.

. So when I fill values in cell I1 and X1 then the value will get calculated automatically in cell Y1. so there is no manual intervention required.

Hence i am expecting the macro to work automatically. Is it possible to do it roy?