Apply formula Calculation by VBA Value ="to Forumula"

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-...ation-vba.html
http://www.vbaexpress.com/forum/show...Formula-by-vba
https://www.excelforum.com/excel-pro...on-by-vba.html

2 Attachment(s)

Hello Avinash

Quote:

Originally Posted by sumanjjj

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-pro...ml#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

Quote:

Originally Posted by sumanjjj

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/showth...ll=1#post11466 )
For example …. Sample.xlsx : -
http://www.excelfox.com/forum/showth...ll=1#post11474

Quote:

Originally Posted by kaja

, 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

Quote:

Originally Posted by 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…..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:-

Code:

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 :-

Code:

' 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-pro...on-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/showth...ll=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/showth...ll=1#post11473
http://www.excelfox.com/forum/showth...ll=1#post11474 )

Ref
Artik http://www.vbaexpress.com/forum/show...l=1#post394129

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

Hello Avinash
Here are some extra notes and further code examples:
http://www.excelfox.com/forum/showth...ll=1#post11479
http://www.excelfox.com/forum/showth...ll=1#post11484

Alan

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 )

3 Attachment(s)

Quote:

Originally Posted by fixer

…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...-in-Windows-XP
https://www.youtube.com/watch?v=cr8T...ature=youtu.be
Vista : https://www.instructables.com/id/How...crosoft-Vista/
http://www.excelfox.com/forum/showth...ll=1#post11167
https://www.youtube.com/watch?v=bfo3UyaS_j8
http://www.excelfox.com/forum/showth...ll=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
Attachment 2431

Television is hanging on the house wall 20190916_Television on Wall.jpg : https://imgur.com/FSSTVb6
Attachment 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
Attachment 2433

Quote:

Originally Posted by fixer

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/

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)

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

Quote:

Originally Posted by fixer

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/genera...her-questions/
https://www.mrexcel.com/forum/lounge-v-2-0/

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

calculation by vba

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

3 Attachment(s)

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

Code:

' 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 )

Code:

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

Code:

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

1 Attachment(s)

Attachment 2764

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

convert formula result to values by macro

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

See here:
http://www.excelfox.com/forum/showth...ll=1#post11479
http://www.excelfox.com/forum/showth...ll=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

Code:

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

Code:

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

_Row\^Col	I	J	K
1	=1	=2	=3
2	="a"	="b"	=1+6
3

_Row\^Col	I	J	K
1	=1	=2	=3
2	a	b	7
3

_Row\^Col	I	J	K
1	1	2	3
2	a	b	7
3

Code:

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

This will work:.._

Code:

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…._

Code:

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
Attachment 2795

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

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

Code:

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

Code:

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

Thnx Doc Sir

2 Attachment(s)

copy,paste,calculate

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

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

1
Exchange Symbol Series/Expiry Open High Low Prev Close LTP

2
NSE ACC EQ
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"))

3
NSE ADANIPOWER EQ
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"))

4
NSE DLF EQ
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"))

5
NSE AMBUJACEM EQ
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

1
Exchange Symbol Series/Expiry Open High Low Prev Close LTP

2
NSE ACC EQ
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"))

3
NSE ADANIPOWER EQ
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"))

4
NSE DLF EQ
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"))

5
NSE AMBUJACEM EQ
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...

Code:

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

Column J

Code:

=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

Code:

=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/art...rs=en-US&ad=US
https://www.techonthenet.com/excel/formulas/if.php

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1087	1030	955.5	998.45	1079.9	22	=IF(H2>D2,1/100H2,IF(H2<D2,1/100H2,"D is equal to H"))	=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))
3	NSE	ADANIPOWER	EQ	148.05	27.75	25.65	25.65	146.5	25	=IF(H3>D3,1/100H3,IF(H3<D3,1/100H3,"D is equal to H"))	=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H"))
4	NSE	DLF	EQ	265	419.7	350.05	387.25	267.15	17388	=IF(H4>D4,1/100H4,IF(H4<D4,1/100H4,"D is equal to H"))	=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H"))
5	NSE	AMBUJACEM	EQ	30.4	155.8	142.55	145.85	29.95	15083	=IF(H5>D5,1/100H5,IF(H5<D5,1/100H5,"D is equal to H"))	=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H"))

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1087	1030	955.5	998.45	1079.9	22	=IF(H2>D2,1/100H2,IF(H2<D2,1/100H2,"D is equal to H"))	=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))
3	NSE	ADANIPOWER	EQ	148.05	27.75	25.65	25.65	146.5	25	=IF(H3>D3,1/100H3,IF(H3<D3,1/100H3,"D is equal to H"))	=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H"))
4	NSE	DLF	EQ	265	419.7	350.05	387.25	267.15	17388	=IF(H4>D4,1/100H4,IF(H4<D4,1/100H4,"D is equal to H"))	=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H"))
5	NSE	AMBUJACEM	EQ	30.4	155.8	142.55	145.85	29.95	15083	=IF(H5>D5,1/100H5,IF(H5<D5,1/100H5,"D is equal to H"))	=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H"))

Code:

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

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: …_

Code:

" " "

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

Code:

" " "

or this

Code:

" " "

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

Code:

" "" "

Or this:

Code:

" "" "" "

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_"____"

Code:

"=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_""____"

Code:

"=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_""____"

Code:

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

Thnx Alot Doc Sir for helping me in solving this problem