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
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Formula by vba
0
Answers
1
Selected Answer
Hi style36
I like this site as I can both learn and teach.
I must confess that I cannot see why you would use a macro to insert a fomular then using the same macro replace the formula with its values.
I woul simple use the macro to calculate and store the result.
I do have code to insert a fomula.
'errcnt = 1
For cls = 7 To intLastRow
If Cells(cls, StatusColn) = "" Or Cells(cls, StatusColn) = "Pending" Or Cells(cls, StatusColn) = "To Script" Then
Cells(cls, StatusColn) = "=IF(" & TCIDcola & cls & "<>"""",IF(" & ExpectResultCola & cls & "<> """",""Pending"",""To Script""),"""")"
End If
This is used to report project status which updates from "To Script" to "Pending" as test expected results are entered not that anyone cares.
However, it is just a sample from a larger process which is one of many.
So please describe what your problem is rather than suggest a method to a solution so that we can all learn or show the various ways it can be solved.
Here you go this code was pinched from a response to someone elses question
Sub UpdateValue()
' 16 Jul 2019
Dim Rl As Long
Dim R As Long
' re-define workbook if the workbook isn't the one containing the code
' replace "Sheet1" with tab name
With ThisWorkbook.Worksheets("Sheet1")
Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
' row 2 is the first row to sum up assume a heading row to ignore
For R = 2 To Rl
' write the result to column B
.Cells(R, "D").Value = .Cells(R, "B") 'Put your code here
'example to myltiply by 2 .Cells(R, "D").Value = .Cells(R, "B")*2
Next R
End With
End Sub
Discussion
Formula- calculate 1.5% of column B multiply the result with 56 and paste the result in column D
this i have to do only by macro
this i have to do only by macro
style36 (rep: 24) Sep 6, '19 at 11:05 am
If u have the code in which i can add any type of formula and it will work then plz provide the same
If u dont have the code and u r unable to make it then i need the macro as per below condition
Formula- calculate 1.5% of column B multiply the result with 56 and paste the result in column D
this i have to do only by macro
If u dont have the code and u r unable to make it then i need the macro as per below condition
Formula- calculate 1.5% of column B multiply the result with 56 and paste the result in column D
this i have to do only by macro
style36 (rep: 24) Sep 6, '19 at 1:38 pm
hi style36
I won't be able to provide you with the answer to your homework until Monday.
You might like to make a start by recording a macro of doing stuff in column B and D. The trick when you are new to coding is to understand what the = sig means.
In arithmetic, the calculation is written on the left and the answer on the right. In coding the = sign has an invisible word Let in front of the line
So you function will be "Let D = (B*.15) * 56
I don't have access to Excel at home - sorry.
I won't be able to provide you with the answer to your homework until Monday.
You might like to make a start by recording a macro of doing stuff in column B and D. The trick when you are new to coding is to understand what the = sig means.
In arithmetic, the calculation is written on the left and the answer on the right. In coding the = sign has an invisible word Let in front of the line
So you function will be "Let D = (B*.15) * 56
I don't have access to Excel at home - sorry.
k1w1sm (rep: 197) Sep 7, '19 at 12:51 am
No problem Sir i will try by myself
i will wait till monday no issue take ur time and plz provide the code of the same Sir
i will wait till monday no issue take ur time and plz provide the code of the same Sir
style36 (rep: 24) Sep 7, '19 at 6:26 am
sir the example which u have given in the code does not consist the percentage part
my problem-calculate 1.5% of column B multiply the result with 56 and paste the result in column D
so plz help Sir
i try to edit but i was unable to make it
my problem-calculate 1.5% of column B multiply the result with 56 and paste the result in column D
so plz help Sir
i try to edit but i was unable to make it
style36 (rep: 24) Sep 9, '19 at 3:45 am
please show us the edit that you made that did not work and we will try and fix it
k1w1sm (rep: 197) Sep 9, '19 at 2:45 pm
Sub UpdateValue()
' 16 Jul 2019
Dim Rl As Long
Dim R As Long
' re-define workbook if the workbook isn't the one containing the code
' replace "Sheet1" with tab name
With ThisWorkbook.Worksheets("Sheet1")
Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
' row 2 is the first row to sum up assume a heading row to ignore
For R = 2 To Rl
' write the result to column B
.Cells(R, "D").Value = .Cells(R, "B") *.15 *56
'example to myltiply by 2 .Cells(R, "D").Value = .Cells(R, "B")*2
Next R
End With
End Sub
style36 (rep: 24) Sep 9, '19 at 9:23 pm
try this
Sub UpdateValue()
' 16 Jul 2019
Dim Rl As Long
Dim R As Long
' re-define workbook if the workbook isn't the one containing the code
' replace "Sheet1" with tab name
With ThisWorkbook.Worksheets("Sheet1")
Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
' row 2 is the first row to sum up assume a heading row to ignore
For R = 2 To Rl
' write the result to column B
.Cells(R, "D").Value = (.Cells(R, "B") * 0.015) * 56
'example to myltiply by 2 .Cells(R, "D").Value = .Cells(R, "B")*2
Next R
End With
End Sub
k1w1sm (rep: 197) Sep 9, '19 at 9:31 pm
Thnx Alot Sir for giving ur Precious Time and Great Support to all of my Post
Have a Great Day Sir
Have a Great Day Sir
style36 (rep: 24) Sep 10, '19 at 12:40 pm