Apply formula Calculation by VBA Value ="to Forumula"

[DocAElstein]

Hello Avinash
Similar to here
http://www.excelfox.com/forum/showth...ll=1#post11472
Also is similar to many of your posts and questions
So I have merged Threads

---

…..note we have to use the formula till the end of the data (till the column H has data )
example if column H has data till H17 then we have to use the formula till
N17 & Q17

' make Lr1 dynamic .... http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474
 Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
 

---

both files are located in a same place
___there is a file name sample1.xlsx
MyPath = ThisWorkbook.Path
___strWb1 = "sample.xlsx"

---

Merged with Sub Vixer9c() from http://www.excelfox.com/forum/showth...ulation-by-vba http://www.excelfox.com/forum/showth...ll=1#post11479


( I did all this from memory on computer at father in law not with Excel so maybe some error is )


10c is like 9c .. It is "fixed vector applied across a range" ( https://teylyn.com/2017/03/21/dollarsigns/#comment-191 , http://www.excelfox.com/forum/showth...ll=1#post11479 )

Sub Vixer10c() ' http://www.excelfox.com/forum/showthread.php/2420-calculation-by-vba?p=12523#post12523
'Sub Vixer9c() ' demo for   fixed vector applied across a range
Rem 1 Workbook and worksheets info
'1a) Workbook info
'                                                                                Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook ' This will be set later when the workbook is opened
Dim MyPath As String: Let MyPath = ThisWorkbook.Path '  "both files are located in a same place                                                                                                                                      ."C:\Users\sk\Desktop"....The file will be located in C:\Users\sk\Desktop ....
Dim strWb1 As String: Let strWb1 = "sample.xlsx" '                                                          " ....and file name is sample.xlsx
'1b) Worksheets info
Dim Ws1 As Worksheet ' This will be set later when the workbook is opened)
Dim Lr1 As Long '     note we have to use the formula till the end of the data (till the column H has data )                                                                                                                     Let Lr1 = 10 for sample file  , but we will determine it dynamically after opening the file
Rem 2 Open file   "..... file is not opened so we have to open the file by vba
'                                                                                                                                  Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\sample.xlsx"
'Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1  '  ...both files are located in same place
 Workbooks.Open Filename:=MyPath & "\" & strWb1              '                                                                      ...file will be located in C:\Users\sk\Desktop
 Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
 Set Ws1 = Wb1.Worksheets.Item(1)

' note we have to use the formula till the end of the data (till the column H has data )         make Lr1 dynamic .... http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474
 Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
Rem 3 The Process ..."....
'In column N we have to use the formula =H2/M2                        .....and paste the result in values in column N
 Ws1.Range("N2:N" & Lr1 & "").Value = "=H2/M2"
'I need only result in the cell no formulas
 Ws1.Range("N2:N" & Lr1 & "").Value = Ws1.Range("N2:N" & Lr1 & "").Value '.....paste the result in values in column N
'in coulmn Q we have to use the formula =N2*P2                         ......and paste the result in values in column Q
 Ws1.Range("Q2:Q" & Lr1 & "").Value = "=N2*P2"
'I need only result in the cell no formulas
 Ws1.Range("Q2:Q" & Lr1 & "").Value = Ws1.Range("Q2:Q" & Lr1 & "").Value
                                                        '        '3(i)(ii) ....Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2..   ....drag it formula will be added by me in the code, put that formula in
                                                        '         Ws1.Range("D2:D" & Lr1 & "").Value = "=B2*(1.5/100)*56"
                                                        '        '3(iii) I need only result in the cell no formulas
                                                        '         Let Ws1.Range("D2:D" & Lr1 & "").Value = Ws1.Range("D2:D" & Lr1 & "").Value
Rem 4 and save and close the sample1.xlsx    ... save it and close it
 Wb1.Save
 Wb1.Close
'End Sub
End Sub

Alan