copy,paste,calculate Cell value based on calculations & comparisonsother cells same row. Decimal places

---

“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthrea...ll=1#post14972
https://excelfox.com/forum/showthrea...ll=1#post14972
I am no longer monitoring what its doings. It had curiosity appeal for a while, but even that has worn off me now!
It’s getting worse by the Day. Its still doing whatever it is that it is doing and getting Replies and answers at excelforum.com and likely a few places I don’t know about.

---

---

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

---

Almost the same as from MoldyBread at excelforum
https://www.excelforum.com/excel-pro...ml#post5338184

---

Some more recent cross posted duplicsate on another one of Avinashes infinite cycles of starting again
https://eileenslounge.com/viewtopic.php?f=30&t=34932
https://chandoo.org/forum/threads/pu...9/#post-266257 ( Leonardo1234 starting again )

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

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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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

I will place the macro in a seperate file macro.xlsm
and i have a file name 1.xls
in 1.xls in column K i have data (i have attached the sample pic of that)
what i need is i need a macro that will remove third number after decimal and along with it it should remove the decimal
Result will be
1090.699 after runing the macro the ouput will be 109069
147.965 after runing the macro the ouput will be 14796
264.4785 after runing the macro the ouput will be 26447
30.2495 after runing the macro the ouput will be 3024

plz help me in solving this problem by vba

Thnx Alot Doc Sir for helping me in solving this problem

Using simple VBA techniques
' We can find the position of the . using Instr function https://docs.microsoft.com/en-us/off...instr-function
' Then we can take the left of the number for a length equal to the position of the . + 2 using the Left function https://docs.microsoft.com/en-us/off.../left-function
' Then we can remove the . using the Replace function , https://docs.microsoft.com/en-us/off...place-function

Code:

---

Sub TrimRemoveDot() '  http://www.excelfox.com/forum/showthread.php/2456-Remove-decimals-by-vba?p=13068#post13068
Dim Ws1 As Worksheet
 Set Ws1 = Workbooks("1.xls").Worksheets.Item(1) ' First worksheet in open workbooks 1.xls
 Dim LrK As Long: Let LrK = Ws1.Range("K" & Ws1.Rows.Count & "").End(xlUp).Row
Dim RngK As Range: Set RngK = Ws1.Range("K2:K" & LrK & "")
Dim SnglCel As Range
    For Each SnglCel In RngK
    Dim Pos As Long: Let Pos = InStr(1, SnglCel.Value, ".", vbBinaryCompare)      '    We can find the position of the . using Instr function                                                            https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function
    Let SnglCel.Value = Left(SnglCel.Value, Pos + 2)                            '    Then we can take the left of the number for a length equal to the position of the . + 2 using the Left function  https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/left-function
    Let SnglCel.Value = Replace(SnglCel.Value, ".", "", 1, -1, vbBinaryCompare)  '    Then we can remove the . using the Replace function                                                              https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function
    Next SnglCel
End Sub

---

---

Or using formulas
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	K
1
2	1090.699
3	147.965
4	264.4785
5	30.2495
6

Worksheet: 1-Sheet1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	L	M	N	O
8	=FIND(".",K2)	=LEFT(K2,L8+2)	=SUBSTITUTE(M8,".","")	=SUBSTITUTE(LEFT(K2,FIND(".",K2)+2),".","")
9	=FIND(".",K3)	=LEFT(K3,L9+2)	=SUBSTITUTE(M9,".","")	=SUBSTITUTE(LEFT(K3,FIND(".",K3)+2),".","")
10	=FIND(".",K4)	=LEFT(K4,L10+2)	=SUBSTITUTE(M10,".","")	=SUBSTITUTE(LEFT(K4,FIND(".",K4)+2),".","")
11	=FIND(".",K5)	=LEFT(K5,L11+2)	=SUBSTITUTE(M11,".","")	=SUBSTITUTE(LEFT(K5,FIND(".",K5)+2),".","")

Worksheet: 1-Sheet1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	L	M	N	O
8	5	1090.69	109069	109069
9	4	147.96	14796	14796
10	4	264.47	26447	26447
11	3	30.24	3024	3024

Worksheet: 1-Sheet1

---

Or using a final formula in Evaluate Range One liner technique

Code:

---

Sub EvaluateRangeTrimRemoveDot() '  http://www.excelfox.com/forum/showthread.php/2456-Remove-decimals-by-vba?p=13068#post13068
Dim Ws1 As Worksheet
 Set Ws1 = Workbooks("1.xls").Worksheets.Item(1) ' First worksheet in open workbooks 1.xls
 Dim LrK As Long: Let LrK = Ws1.Range("K" & Ws1.Rows.Count & "").End(xlUp).Row
Dim RngK As Range: Set RngK = Ws1.Range("K2:K" & LrK & "")
 Let RngK.Value = Evaluate("=if({1},SUBSTITUTE(LEFT(" & RngK.Address & ",FIND("".""," & RngK.Address & ")+2),""."",""""))")
End Sub

---

Alan

---

Some more recent cross posted duplicsate on another one of Avinashes infinite cycles of starting again
https://eileenslounge.com/viewtopic.php?f=30&t=34932
https://chandoo.org/forum/threads/pu...9/#post-266257 ( Leonardo1234 starting again )

Thnx Alot Doc Sir for ur Great Support
Problem Solved

all file are located in a different path
vba code will be placed in a seperate file macro.xlsm
my file name is 1.xls
If column H of 1.xls is lower than column D of 1.xls then with column K (subtract it and make it to the number ending with 0 or 5 whichever met first)
If column H of 1.xls is greater than column D of 1.xls then with column K (add it and make it to the number ending with 0 or 5 whichever met first)
i have attached the sample file and result too.. plz have a look sir
and help me in solving this problem sir

just for fun with Power Query

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"raw", type text}}, "en-GB"), "raw", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"raw.1", "raw.2"}),
    Extract = Table.TransformColumns(Split, {{"raw.2", each Text.Start(Text.From(_, "en-GB"), 2), type text}}),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(Extract, {{"raw.1", type text}}, "en-GB"),{"raw.1", "raw.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result"),
    Type = Table.TransformColumnTypes(Merge,{{"Result", Int64.Type}})
in
    Type

---

raw	Result
1090.699	109069
147.965	14796
264.4785	26447
30.2495	3024

should be ok
thanks Alan :cool: