Formula by vba

[kaja]

Open the sample.xlsx file by macro, formula will be added by me in the code, put that formula in C2 and drag it the result will be shown by the formula in column C, change the result in column C as paste as values (formula will be removed and only result will be there)and save and close the file

[Paul_Hossler]

1. Little too brief

2. Where's sample.xlsx?

3. What formula?

[kaja]

The file will be located in C:\Users\sk\Desktop
Formula- calculate 1.5% of column B multiply the result with 56 and paste the result in column D
thats all i need

[kaja]

If u have the code in which i can add any type of formula and it will work then plz provide the same i am looking for that but if that is not possible then plz provide the code as per third post

[Artik]

Open the sample.xlsx file by macro

Workbooks.Open "Full_Name"



put that formula in C2 and drag it
Drag it? Where? Probably to the last nonblank cell in column B. Then find the row number of the last nonblank cell in column B using the End(xlDown).Row property (maybe use End (xlUp)) and assign this value to a variable, e.g. lRow. Look for such constructions in the forum. It's a lot.
Then create a reference to the range "C2:C" & lRow by assigning it to a variable, e.g. rngScope, and insert your formula into this range. It would be safest if the formula was in the R1C1 convention. More or less this way
rngScope.FormulaR1C1 = "=My_Formula_R1C1" At this point, you have formulas in column C.



change the result in column C as paste as values

rngScope.Value = rngScope.Value

and save and close the file

ActiveWorkbook.Close True
And it's ready. :)


If you have a problem with something, ask us.


Artik

[kaja]

unable to make a vba code of the same so plz help sir

[Aussiebear]

I am not sure you are reading and understanding the posts by Paul Hossler and Artik. Both people have indicated that you have provided too little information for them to be able to build a specific vba solution. Artik has gone to great lengths to how your individual requests "could" be written, but again because you didn't supply enough information its hard to tailor a specific solution.

Before you next reply, have a think about the following information needs;

Since you have a formula in cell C2, what do you want done with it? Your suggestion to "Drag it" is fine but where do you want to drag the formula to? What governs how far to "drag" the formula and in what direction?

The formula as you stated "Formula- calculate 1.5% of column B multiply the result with 56 and paste the result in column D", doesn't mean much as it currently stands. Do you mean that you would like to Multiply the value of B2 by 1.5%, then multiply that result by 56 and then post the result in D2?

[kaja]

Do you mean that you would like to Multiply the value of B2 by 1.5%, then multiply that result by 56 and then post the result in D2?
Yes Sir

[Aussiebear]

What about the other questions?

[kaja]

2 things
1)if u have the universal code in which i can add any formula and it will work then i need that vba code , i am looking for this but if in case it is not possible then i need the 2)
2)Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2(i need only result in the cell no formulas)
note- 1st row contains headers so ignore the first row
The file will be located in C:\Users\sk\Desktop and file name is sample.xlsx
file is not opened so we have to open the file by vba and do the process and save it and close it
vba will be added in a seperate file process.xlsm
both files are located in same place

[pike]

https://www.excelforum.com/excel-programming-vba-macros/1289175-calculation-by-vba.html

[kaja]

By mistakely i forgot to add a link to that post and when o tried to edit the post of excelforum i was unable to do so

[kaja]

Problem is not solved in that forum so plz dont close this post Sir

[Fluff]

Also cross posted here https://www.mrexcel.com/forum/excel-questions/1109256-add-calculation-vba.html

[kaja]

Yes Sir i have cross posted the question
But i have followed the forum rules
i have shared the link Sir

[Fluff]

You have not supplied any links in this thread to the other sites.

[Aussiebear]

This akin to drawing teeth..... Okay I'll have yet another go.

1. Is it your intention to have two workbooks, one that is currently opened, and another one that needs to be opened?

2. Of the workbook you need opened, what triggers this event? For example, are you waiting for a click event or of a result from a worksheet change event in the initial workbook that is currently open?

3. Since we've worked out that you want a formula in cell C2 which works off cell B2 value and pastes a result in D2, ( by the way your version "Dragged" is redundant so let's use the term "Copied"), how far do you want C2 formula copied to? Options could be "down x number of cells", "down to the last row of data in column X", "to the last cell in Column C"? Three people have alluded to this particular point and you have not given us any indication is to your intent. This is important for us to know

4. Where does the value "56" come from? Do you obtain this from a particular cell, or is it simply a hard value, ( one that doesn't change)? If its a value that doesn't change then it can be written into the via formula and no one other than you will know any different. If however, its a soft value ( one that resides in a particular cell) where is this value obtained from?

I have asked you once before about a good think about what you are asking here and to try supplying the correct information, and as such I am asking you to do so yet again, before responding.

Now, while you are here, we need to discuss the issue of cross posting. What upsets people is that until someone else mentioned it, you did not indicate that you posted this issue on another forum. You need to do so in your very first post after you post the issue on another forum/s. Please show respect to those members of the forums which you ask help from. Okay?