Apply formula Calculation by VBA Value ="to Forumula"

[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
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...

(    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/art...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