Apply formula Calculation by VBA Value ="to Forumula"

[fixer]

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
i need vba to do the same
https://www.mrexcel.com/forum/excel-questions/1109256-add-calculation-vba.html
http://www.vbaexpress.com/forum/showthread.php?65832-Formula-by-vba
https://www.excelforum.com/excel-programming-vba-macros/1289175-add-a-calculation-by-vba.html

[DocAElstein]

Hello Avinash




1.5% to .15 why i am unable to understand the same
I also do not understand....
1.5% is 1.5/100 = .015
Richard Buttrey ( https://www.excelforum.com/excel-programming-vba-macros/1289175-add-a-calculation-by-vba.html#post5190581 ) probably made typo mistake... just typo mistake - not important
Sheet1.Range("D2") = Sheet1.Range("B2") * .15 * 56 is wrong
Sheet1.Range("D2") = Sheet1.Range("B2") * .015 * 56 is correct



i have data upto 100 or 200 rows it can be more all it depends i have to do the same process till the end of the data
So we need to make Lr dynamic, ( http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466 )
For example …. Sample.xlsx : -
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474

, 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, …
…
Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2




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…..formula will be added by me in the code, put that formula in …. drag it …….. the result will be shown by the formula )
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
i need vba to do the same




So, I think requirement is
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
Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2..formula will be added by me in the code, put that formula in ….
drag it …….. the result will be shown by the formula
I need only result in the cell no formulas…
do the process and save it and close it

note- 1st row contains headers so ignore the first row
vba will be added in a seperate file process.xlsm
both files are located in same place
i need vba to do the same


Using macro recorder, record macro
Record Macro.JPG : https://imgur.com/I2gMvi9

This is the macro recorded by the macro recorder:-

Sub Makro4()
'
' Makro4 Makro
'

' file is not opened so we have to open the file by vba
Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\sample.xlsx"

' Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2..formula will be added by me in the code, put that formula in …
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]*(1.5/100)*56"

' drag it
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D10"), Type:=xlFillDefault

' I need only result in the cell no formulas
Range("D2:D10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

' save it and close it
Windows("sample.xlsx").Activate
ActiveWindow.Close savechanges:=True
End Sub

Stop macro recording.JPG : https://imgur.com/F0ygnd2




Here is a final macro written by me :-

'
Sub Vixer9a() ' http://www.excelfox.com/forum/showthread.php/2369-Calculation-by-vba https://www.mrexcel.com/forum/excel-questions/1109256-add-calculation-vba.html http://www.vbaexpress.com/forum/showthread.php?65832-Formula-by-vba[/url] https://www.excelforum.com/excel-programming-vba-macros/1289175-add-a-calculation-by-vba.html
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 = "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 ' 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)

' 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 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("C" & Ws1.Rows.Count).End(xlUp).Row
Rem 3 The Process ..."....
'3(i) ....Multiply the value of B2 by 1.5%, then multiply that result by 56 and then paste the result in D2.. formula will be added by me in the code, put that formula in
Ws1.Range("D2").Value = "=B2*(1.5/100)*56"
'3(ii) ....drag it
Ws1.Range("D2").AutoFill Destination:=Ws1.Range("D2:D" & Lr1 & ""), Type:=xlFillDefault
'3(iii) I need only result in the cell no formulas
Ws1.Range("D2:D" & Lr1 & "").Copy
Ws1.Range("D2:D" & Lr1 & "").PasteSpecial Paste:=xlPasteValues
Let Application.CutCopyMode = False
Rem 4 save it and close it
Wb1.Save
Wb1.Close
End Sub



Alan

( see also here:

http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11473&viewfull=1#post11473
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11474&viewfull=1#post11474 )




Ref
Artik http://www.vbaexpress.com/forum/showthread.php?65832-Formula-by-vba&p=394129&viewfull=1#post394129

[fixer]

Thnx Doc Sir and Each and Everyone for giving their Great Contribution,Support,Precious Time to all of my Problem's
Have a Great Day

[DocAElstein]

Hello Avinash
Here are some extra notes and further code examples:
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11479&viewfull=1#post11479
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11484&viewfull=1#post11484

Alan

[fixer]

Thnx Doc Sir for ur Great Efforts
Doc Sir i am looking to buy a laptop i have serched on google and i have seen forums also but i have not got any exact answer for my question
Sir i want to know which processor is best for laptop for vba only and graphics card are important or not
and u r a Proffessional Programmer so Plz help us with the clear picture of which type of laptop or desktop is best for vba programming
which type of configuration desktop or laptop is suitable for vba programming to achieve great performance(I know the speed of the vba programming depends on vba code but let us assume the code is best and we need to know about the configuration of desktop or laptop )

[DocAElstein]

…u r a Proffessional Programmer …
No , this wrong
I am a Builder. I build houses. I build now my castle in Germany
मी एक बिल्डर आहे. मी घरे बांधतो. मी आता जर्मनी मध्ये माझा किल्ला तयार करतो
I only do Excel for fun or for my private use
मी फक्त गंमत म्हणून किंवा माझ्या खाजगी वापरासाठी Excel करतो

I prefer older computers
I buy an old lap top. ( Operating system XP or Vista )
I buy an old large television.

I use Excel 2003 , 2007 or 2010. ( Microsoft Office 2003, 2007 or 2010)

I connect television to Lap top with HDMI cable ( or VGA cable )

Use two monitors /Extended desktop
XP: https://www.wikihow.com/Use-Extended-Desktop-View-in-Windows-XP
https://www.youtube.com/watch?v=cr8ToUYBV-s&feature=youtu.be
Vista : https://www.instructables.com/id/How-to-setup-dual-monitors-with-Microsoft-Vista/
http://www.excelfox.com/forum/showthread.php/2334-Tests-Windows-Vista-and-Excel?p=11167&viewfull=1#post11167
https://www.youtube.com/watch?v=bfo3UyaS_j8
http://www.excelfox.com/forum/showthread.php/2334-Tests-Windows-Vista-and-Excel?p=11167&viewfull=1#post11167



Then … Open Excel,
Alt + F11

Now you have Spreadsheet in high resolution on television, and VB Editor on lap top screen

This is me at home now:
Lap top is on table. 20190916_LapTop on Table.jpg : https://imgur.com/d4Bc5ZX
2431

Television is hanging on the house wall 20190916_Television on Wall.jpg : https://imgur.com/FSSTVb6
2432

Now you have Spreadsheet in high resolution on television, and VB Editor on lap top screen:
20190916_HDMI cable.jpg : https://imgur.com/aFWGKan
2433





Doc Sir i am looking to buy a lap top ….. let us assume the code is best …..

My opinion.is

If you have best code, then old cheap computer, + old cheap Television for extended second Monitor. Use XP or Vista Operating system. Use Excel 2003, or Excel 2007 or Excel 2010
If you have best code then this is OK. Just is my opinion.

Forget Windows 7 and Windows 10
Forget Excel 2013 2016 and 365
Forget New computer

Buy an old laptop. Buy an old television ( for second Monitor)
Buy old Office 2003 , 2007 and 2010

All is just my opinion.




Alan

P.S. You can also ask this question here:
https://www.excelforum.com/the-water-cooler/

[fixer]

No issue Sir i understood that any laptop will do but my question is vba what are the things which are the things that will make a process fast (90% of speed will depend on the code I agree on the same but 10% of the speed will depend on laptop configuration so plz let me know which type of laptop will be best for vba)

[fixer]

vba use how many cores of processor
so how many cores processor will do etc like this way plz tell me and educate us and plz let me know that which configuration is best

[DocAElstein]

my question is vba what are the things which are the things that will make a process fast (90% of speed will depend on the code I agree on the same but 10% of the speed will depend on laptop configuration so plz let me know which type of laptop will be best for vba).....vba use how many cores of processor
so how many cores processor will do etc like this way plz tell me and educate us and plz let me know that which configuration is best
Sorry, I do not know the answer to those questions
Alan

P.S.
Maybe ask here:
https://www.excelforum.com/the-water-cooler/
https://www.mrexcel.com/forum/general-excel-discussion-other-questions/
https://www.mrexcel.com/forum/lounge-v-2-0/

[fixer]

No Problem Sir
Thnx Alot For ur Great Support Sir
Have a Nice Day Sir

[fixer]

vba is placed in a seperate file c.xlsm
both files are located in a same place
there is a file name sample1.xlsx
open sample1.xlsx
in column N we have to use the formula =H2/M2 and paste the result in values in column N of sample1.xlsx
and in coulmn Q we have to use the formula =N2*P2 and paste the result in values in column Q of sample1.xlsx
and save and close the sample1.xlsx


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

so plz have a look sir and help me in solving this problem by vba macro sir

[DocAElstein]

Hello Avinash
Similar to here
http://www.excelfox.com/forum/showthread.php/2369-Calculation-by-vba?p=11472&viewfull=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/showthread.php/2369-Calculation-by-vba http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11479&viewfull=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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11479&viewfull=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

[fixer]

Sub calculation()
Dim wb1 As Workbook, Ws1 As Worksheet, lr1 As Long
Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb")
Set Ws1 = wb1.Worksheets.Item(1)
Let lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
Ws1.Range("N2:N" & lr1 & "").Value = "=H2/M2"
Ws1.Range("N2:N" & lr1 & "").Value = Ws1.Range("N2:N" & lr1 & "").Value
Ws1.Range("Q2:Q" & lr1 & "").Value = "=N2*P2"
Ws1.Range("Q2:Q" & lr1 & "").Value = Ws1.Range("Q2:Q" & lr1 & "").Value
wb1.Save
wb1.Close
End Sub

I used this code and i met with an error this code is if perfect but what this code is doing is suppose coulmn H has data till h20 then this formula is giving the result till n20 and q20 in values but after that also after n20 and q20 also formula is used

[fixer]

2764

[fixer]

Doc Sir ur code is perfect no doubt in it i got where the mistake started on more macro was causing an error i got the issue
Thnx Alot Doc sir for ur long lasting support sir have a great day sir

[fixer]

I have a data in cloumn I,column J,column K that data is the result of the formulas i have used
what i need i need to convert that formula result to values by a macro
all files are located in a same path
vba is palced in a seperate file
macro.xlsm
and file that has formulas is 2.xls

[DocAElstein]

See here:
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11479&viewfull=1#post11479
http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste?p=12540&viewfull=1#post12540

To convert formulas in a range, Rng, to values …

Rng.Value = Rng.Value

To convert formulas in a range, Rng1, to values …

Rng1.Value = Rng1.Value

To convert formulas in a range, Rng2, to values …

Rng2.Value = Rng2.Value



_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )


Row\Col

H


I


J


K


L




1



=1


=2


=3





2


="a"
="b"

=1+6





3








Worksheet: Tabelle3



Sub RngFormulasToValues()
Range("I2:K2").Value = Range("I2:K2").Value
End Sub


_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )


Row\Col

H


I


J


K


L




1



=1


=2


=3





2


a
b

7





3








Worksheet: Tabelle3



Sub RngFormulasToValues()
Range("I1:K2").Value = Range("I1:K2").Value
End Sub


_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )


Row\Col

H


I


J


K


L




1



1


2


3





2


a
b

7





3








Worksheet: Tabelle3

[fixer]

Sub STEP6()
Dim w1 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\Merge.xlsx") ' change the file path
w1.Worksheets.Item(1).Columns("I:K").Copy
w1.Worksheets.Item(1).Columns("I:K").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w1.Save
w1.Close
End Sub




Doc Sir I made this
correct the code if its wrong
Thnx Doc Sir for ur Great Guidance Sir

[DocAElstein]

This will work:.._


Sub STEP6()
Dim w1 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\Merge.xlsx") ' change the file path

' convert formula result to values --- Copy 3145728 cells 3145728 Cells in 3 Whole columns.JPG : https://imgur.com/kQYSQfg
w1.Worksheets.Item(1).Columns("I:K").Copy
w1.Worksheets.Item(1).Columns("I:K").PasteSpecial Paste:=xlPasteValuesAndNumberFormats


w1.Save
w1.Close
End Sub

_...This will also work…._


Sub STEP6()
Dim w1 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\Merge.xlsx") ' change the file path

' convert formula result to values --- Copy 3145728 cells 3145728 Cells in 3 Whole columns.JPG : https://imgur.com/kQYSQfg
w1.Worksheets.Item(1).Columns("I:K").Value = w1.Worksheets.Item(1).Columns("I:K").Value


w1.Save
w1.Close
End Sub


_.........But it is not good - you copy 3 x 1048576 = 3145728 cells
3145728 Cells in 3 Whole columns.JPG : https://imgur.com/kQYSQfg
2795

_.................This is better:-

3 x Lr Cells.JPG : https://imgur.com/LaIBKQL
2796


Sub STEP6()
Dim w1 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\Merge.xlsx") ' change the file path
Dim Lr As Long: Let Lr = w1.Worksheets.Item(1).Range("K" & w1.Worksheets.Item(1).Rows.Count & "").End(xlUp).Row

' convert formula result to values ---- 3 x Lr cells 3 x Lr Cells.JPG : https://imgur.com/LaIBKQL
w1.Worksheets.Item(1).Range("I1:K" & Lr & "").Value = w1.Worksheets.Item(1).Range("I1:K" & Lr & "").Value


Wb1.Save
Wb1.Close
End Sub


_......................................This is best


Sub STEP6()
Dim Wb1 As Workbook, Ws1 As Worksheet
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\Merge.xlsx") ' change the file path
Set Ws1 = Wb1.Worksheets.Item(1)
Dim Lr As Long: Let Lr = Ws1.Range("K" & Ws1.Rows.Count & "").End(xlUp).Row

' convert formula result to values ---- 3 x Lr cells 3 x Lr Cells.JPG : https://imgur.com/LaIBKQL
Ws1.Range("I1:K" & Lr & "").Value = Ws1.Range("I1:K" & Lr & "").Value


Wb1.Save
Wb1.Close
End Sub

[fixer]

Thnx Doc Sir

[fixer]

vba code will be placed in a seperate file macro.xlsm
i have one more file 1.xls (all files are located in a different path)
i have attached a sample pic of 1.xls & result pic too
check if Column H is greater or lower than column D
if column H is greater than column D then calculate 1% of column H and paste it to column J and then subtract Column H & column J and paste the result to column K
Or
if column H is lower than column D then calculate 1% of column H and paste it to column J and then add Column H & column J and paste the result to column K
Plz help me in solving this problem sir

[DocAElstein]

This is so simple, that even I find it easy to do, with a formula...



_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1087
1030
955.5
998.45
1079.9
22
=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))
=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))


3NSEADANIPOWEREQ
148.05
27.75
25.65
25.65
146.5
25
=IF(H3>D3,1/100*H3,IF(H3<D3,1/100*H3,"D is equal to H"))
=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H"))


4NSEDLFEQ
265
419.7
350.05
387.25
267.15
17388
=IF(H4>D4,1/100*H4,IF(H4<D4,1/100*H4,"D is equal to H"))
=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H"))


5NSEAMBUJACEMEQ
30.4
155.8
142.55
145.85
29.95
15083
=IF(H5>D5,1/100*H5,IF(H5<D5,1/100*H5,"D is equal to H"))
=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H"))
Worksheet: 1-Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1087
1030
955.5
998.45
1079.9
22
=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))
=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))


3NSEADANIPOWEREQ
148.05
27.75
25.65
25.65
146.5
25
=IF(H3>D3,1/100*H3,IF(H3<D3,1/100*H3,"D is equal to H"))
=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H"))


4NSEDLFEQ
265
419.7
350.05
387.25
267.15
17388
=IF(H4>D4,1/100*H4,IF(H4<D4,1/100*H4,"D is equal to H"))
=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H"))


5NSEAMBUJACEMEQ
30.4
155.8
142.55
145.85
29.95
15083
=IF(H5>D5,1/100*H5,IF(H5<D5,1/100*H5,"D is equal to H"))
=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H"))
Worksheet: 1-Sheet1

Each formula uses 2 Excel If functions, one is nested in the other...


( If _>_ , Do this , Else [ If _<_ , Do this , Else "......" ] )

If ( _>_ , Do this , Else If( _<_ , Do this , Else "......" ) )

Column J

=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"d is equal to H"))

if column H is greater than column D then calculate 1% of column H
=IF(H2>D2,1/100*H2,
Else
if column H is lower than column D then calculate 1% of column H
IF(H2<D2,1/100*H2,

Else
"D is equal to H"))

Column K



=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))

if column H is greater than column D subtract Column H & column J
=IF(H2>D2,H2-J2,
Else
if column H is lower than column D add Column H & column J
IF(H2<D2,H2+J2,


Else
"D is equal to H"))



Alan

Ref
https://support.office.com/en-us/article/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2?ui=en-US&rs=en-US&ad=US
https://www.techonthenet.com/excel/formulas/if.php

[fixer]

Sub STEP8()
Dim Wb1 As Workbook, Ws1 As Worksheet, Lr1 As Long
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets.Item(1)
Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
Ws1.Range("J2:J" & Lr1 & "").Value = "=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"d is equal to H"))"
Ws1.Range("J2:J" & Lr1 & "").Value = Ws1.Range("J2:J" & Lr1 & "").Value
Ws1.Range("K2:K" & Lr1 & "").Value = "=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))"
Ws1.Range("K2:K" & Lr1 & "").Value = Ws1.Range("K2:K" & Lr1 & "").Value
Wb1.Save
Wb1.Close
End Sub







Doc Sir i am getting error sir i dont know why i got it plz have a look sir




http://www.eileenslounge.com/viewtopic.php?f=30&t=34670

[DocAElstein]

Hi
Your macro is very good.
You have written good coding.
It is almost perfect.

There is just one very small problem. It is just because VBA is confused when quotes are inside quotes. VBA does not like to see quotes inside other quotes. It get confused.

If VBA see this: …_

" " "
_.. VBA does not know what it is. VBA thinks maybe it is either this

" " "
or this

" " "
VBA does not like to see inside quotes 1 quote or 3 quotes or 5 quotes … etc

VBA must always see 2 quotes "" ( or 4 quotes m"""", or 6 quotes """""" )etc..
We must do a trick , like this

" "" "

Or this:

" "" "" "

If you do this trick, then VBA will see for "" just one quote.

So in your macro , like this is the problem: "________"_D is equal to H_"____"

"=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))"

We must do trick: "________""_D is equal to H_""____"

"=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,""D is equal to H""))"



So in your macro,
change the single quotes inside the outermost enclosing quotes to double quotes
like this:
______________Ws1.Range(" 2: " & Lr1 & "").Value =__"__________""_D is equal to H_""____"

Sub STEP8()
Dim Wb1 As Workbook, Ws1 As Worksheet, Lr1 As Long
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets.Item(1)
Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
Ws1.Range("J2:J" & Lr1 & "").Value = "=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,""D is equal to H""))"
Ws1.Range("J2:J" & Lr1 & "").Value = Ws1.Range("J2:J" & Lr1 & "").Value
Ws1.Range("K2:K" & Lr1 & "").Value = "=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,""D is equal to H""))"
Ws1.Range("K2:K" & Lr1 & "").Value = Ws1.Range("K2:K" & Lr1 & "").Value
Wb1.Save
Wb1.Close
End Sub


You did almost perfect in your macro. It was very close.

Alan




http://www.eileenslounge.com/viewtopic.php?f=30&t=34670

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem