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

[fixer]

..You are smart enough to get others to do your work
Yes I am Smart enough Doc Sir to get others to do my work
But No Doubt Doc Sir The work that they do is priceless
If i were smart then i can make a vba code for me

Problem Solved Doc Sir thnx Alot for giving ur Precious time & Great Support

[fixer]

Calculate 2% of colum H & column I & considered the greater number between them
column S should be positive, so don't considere the no. which are negative
& if column S is lower than that 2% of column H or Column I (whichever is greater )then put -1
vba macro will be placed in a seperate file , sheet name can be anything, all files are located in different place
example
the U2 cell will become -1 after runing the macro

---

Similar to this from MoldyBread at excelforum
https://www.excelforum.com/excel-pro...ml#post5338184

[DocAElstein]

Hi

Before

_____ Workbook: ap.xls ( Using Excel 2007 32 bit )

Row\Col	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	NetBuyValue	NetSellValue	NetValue	NetBuyQty	NetSellQty	NetQty	BEP	SellAvgPrice	BuyAvgPrice	LastTradedPrice	MarkToMarket	Realized MarkToMarket	Unrealized MarkToMarket	EL MarkToMarket
2	10781.10	10878.30	97.20	54	54			201.45	199.65	201	97.2	97.2		97.2
3	420.60	430.50	9.90	2	2			215.25	210.30	210.35	9.9	9.9		9.9
4	2429.10	2405.70	-23.40	18	18			133.65	134.95	135	-23.4	-23.4		-23.4
5	6120.90	6064.20	-56.70	54	54			112.30	113.35	111.6	-56.7	-56.7		-56.7
6	66.30	65.70	-0.60	2	2			32.85	33.15	32.5	-0.6	-0.6		-0.6
7	15114.60	14966.10	-148.50	54	54			277.15	279.90	279.15	-148.5	-148.5		-148.5
8	570.60	567.00	-3.60	6	6			94.50	95.10	93.35	-3.6	-3.6		-3.6
9	7344.00	7272.40	-71.60	54	54			134.67	136.00	138.5	-71.6	-71.6		-71.6
10

Worksheet: ap-Sheet1

After

_____ Workbook: ap.xls ( Using Excel 2007 32 bit )

Row\Col	U
1	EL MarkToMarket
2	-1
3	9.9
4	-23.4
5	-56.7
6	-0.6
7	-148.5
8	-3.6
9	-71.6
10

Worksheet: ap-Sheet1


Macro here: https://excelfox.com/forum/showthrea...ll=1#post13424


Alan

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem Sir
Have a Awesome Day

---

1 June : https://www.eileenslounge.com/viewto...p?f=30&t=34710

[fixer]

Hi Experts,
I am looking for a macro that will do the things mentioned below

Check column H of 1.xls has how many number before decimal (If decimal is not present then check how many numbers it has )
after knowing this, match column I of 1.xls with Column B of 2.xlsx & if matched then put decimal in column E of 2.xlsx after that many numbers (if decimal is not present) & if decimal is present then remove that decimal & reput the decimal in column E of 2.xlsx according to the numbers

Plz see the sample file


Macro will be placed in macro.xlsm
both file can be located anywhere in the pc so the path should be hardcoded in the macro so that i can change it as per my needs in the future

Thnx For the Help

Any Doubts Any Question Plz Ask

[DocAElstein]

I assume that we do nothing if there is no match in data from column I 1.xls in the column B in sample2.xlsx

So, another way of explaining the requirement is
Try to find a match in the data from each row, starting at 2 of column I 1.xls in column B 2.xlsx
If we have a match, then we find the length of the integer of the value for the H ( in variable, LHint ) in 1.xls for that row
Once we have that we first, remove any decimal place in the matched row in 2.xlsx in column E
What we then do is convert that number in the matched row in 2.xlsx in column E to effectively be
_______________= the first LHint characters of it & a decimal point & then the rest of the characters
__ arr2E(MtchRes, 1) = Left(arr2E(MtchRes, 1), LHInt) & "." & Mid(arr2E(MtchRes, 1), LHInt + 1)





Before:
_____ 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	1265	1282.7	1246.5	1275.3	1247	22	BUY	116761.05
3	NSE	ADANIENT	EQ	151.85	165.45	151.4	151.85	152.35	25	BUY	14104.65
4	NSE	ADANIPORTS	EQ	348	348	338.5	346.55	338.85	15083	BUY	3235.03
5	NSE	ADANIPOWER	EQ	38.85	38.9	37.65	38.8	37.85	17388	SHORT	3469.653
6	NSE	AMARAJABAT	EQ	662.5	665.9	642.55	662.5	643.5	100	BUY	5680.24

Worksheet: 1-Sheet1 18July

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	22	6	<	115605	A					GTT
2	NSE	25	6	<	13965	A					GTT
3	NSE	15083	6	<	3203	A					GTT
4	NSE	17388	6	>	3504.7	A					GTT
5	NSE	100	6	<	5624	A					GTT

Worksheet: Sheet1 18 July

After:

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	22	6	<	1156.05	A					GTT
2	NSE	25	6	<	139.65	A					GTT
3	NSE	15083	6	<	320.3	A					GTT
4	NSE	17388	6	>	35.047	A					GTT
5	NSE	100	6	<	562.4	A					GTT

Worksheet: Sheet1 18 July


Macro Here : https://excelfox.com/forum/showthrea...ll=1#post14674

[fixer]

Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem
Have a Awesome Day Sir

[fixer]

Hi Experts,

I am looking for a macro that will do the the things mentioned below
Macro will be placed in a macro.xlsm
macro.xlsm & 1.xls both are located in different path so the path should be hardcoded in the macro, so i can change it as per my needs


In column K, If after decimal if the second digit is 0 then Move all the data from after decimals to before decimals
K2 has data that has the second number after decimal is 0, so the output would be in K2 10030.00


Thnx For the Help
https://www.excelforum.com/excel-pro...e-decimal.html

[fixer]

Problem Solved

[DocAElstein]

The macro that you obtained from excelforum just multiplies all the column K values by 100. Your description suggests that you did not want just that.