Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

**DocAElstein** · 08-11-2019, 12:15 PM

Testing for this Thread Post:
http://www.excelfox.com/forum/showth...ll=1#post11416

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	UserId	AccountId	EntityName	Exchg-Seg	Symbol	Instrument Name	Option Type	NetBuyValue	NetSellValue	NetValue	NetBuyQty	NetSellQty	NetQty	BEP	SellAvgPrice	BuyAvgPrice	LastTradedPrice	MarkToMarket	Realized MarkToMarket	Unrealized MarkToMarket	EL MarkToMarket	Trading Symbol	Client Context	Series/Expiry
2		WC5758		NSE	MINDTREE	EQ		##	##	##	1	4			760.10	765.00	##	-5	-5		-5	MINDTREE-EQ		EQ
3		WC5758		NSE	BHEL	EQ		##	##	##	2	4	1	##	67.65	67.65	68	-0		-0	-0	BHEL-EQ		EQ
4		WC5758		NSE	SIEMENS	EQ		##	##	##	2	4			1252.05	1270.20	##	##	##		##	SIEMENS-EQ		EQ
5		WC5758		NSE	SUNTV	EQ		##	##	##	2	4			469.80	472.85	##	-6	-6		-6	SUNTV-EQ		EQ
6		WC5758		NSE	RELCAPITAL	EQ		##	##	##	1	4			58.10	58.50	57	-0	-0		-0	RELCAPITAL-EQ		EQ
7		WC5758		NSE	JSWSTEEL	EQ		##	##	##	2	4			262.65	263.60	##	-2	-2		-2	JSWSTEEL-EQ		EQ
8		WC5758		NSE	TVSMOTOR	EQ		##	##	##	1	4			422.30	423.10	##	-1	-1		-1	TVSMOTOR-EQ		EQ
9		WC5758		NSE	RECLTD	EQ		##	##	##	1	4			138.55	140.50	##	-2	-2		-2	RECLTD-EQ		EQ
10		WC5758		NSE	PIDILITIND	EQ		##	##	##	1	4			1178.20	1180.00	##	-2	-2		-2	PIDILITIND-EQ		EQ
11		WC5758		NSE	VOLTAS	EQ		##	##	##	1	4			594.70	595.70	##	-1	-1		-1	VOLTAS-EQ		EQ
12		WC5758		NSE	TITAN	EQ		##	##	##	1	4			1097.05	1111.00	##	##	##		##	TITAN-EQ		EQ
13		WC5758		NSE	PNB	EQ		##	##	##	2	4			74.55	74.85	74	-1	-1		-1	PNB-EQ		EQ
14		WC5758		NSE	OFSS	EQ		##	##	##	2	4			3226.10	3239.95	##	##	##		##	OFSS-EQ		EQ
15

Worksheet: ap-Sheet1

Column Y Before ( As above )
_____ Workbook: ap.xls ( Using Excel 2007 32 bit )

_Row\^Col	Y
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Worksheet: ap-Sheet1

Column Y After running routine Sub Vixer3_For_13_data_rows()
_____ Workbook: ap.xls ( Using Excel 2007 32 bit )

_Row\^Col	Y
1
2	15.3
3	1.353
4	25.404
5	9.457
6	1.17
7	5.272
8	8.462
9	2.81
10	23.6
11	11.914
12	22.22
13	1.497
14	64.799

Worksheet: ap-Sheet1

Macro version for 13 data rows

Code:

Sub Vixer3_For_13_data_rows() ' http://www.excelfox.com/forum/showthread.php/2352-calculation-and-multiply-by-vba?p=11416&viewfull=1#post11416
Rem 0 Open data workbook
' Workbooks.Open "F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\ap.xls"
Rem 1 Workbook and worksheets info
'Dim Wb1 As Workbook: Set Wb1 = Workbooks.Open("F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\ap.xls") '
Dim Wb1 As Workbook: Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\ap.xls")
Dim Ws1 As Worksheet: Set Ws1 = Wb1.Worksheets.Item(1) ' First worksheet, (as worksheet object) in open file "sample1.xlsx"
Dim Lr As Long
 Let Lr = 14 ' To work with 13 data rows

Rem 3 Main Loop for all data rows
Dim Cnt As Long ' Main Loop for all data rows ================================================
    ' 3a)(i) ' compare column O is greater or column P is greater
    For Cnt = 2 To Lr ' for 13 data rows starting at row 2
    Dim Bigger As Double
        If Ws1.Range("O" & Cnt & "").Value > Ws1.Range("P" & Cnt & "").Value Then ' if column O is greater
         Let Bigger = Ws1.Range("O" & Cnt & "").Value
        Else
         Let Bigger = Ws1.Range("P" & Cnt & "").Value ' if column P is greater
        End If
    '3a)(ii) calculate the 0.50% of that and multiply the same with column L
    Dim Rslt As Double '
     Let Rslt = Bigger * (0.5 / 100) * Ws1.Range("L" & Cnt & "").Value ' calculate the 0.50% of that and multiply the same with column L
    '3b) paste the result to sample1.xlsx column Y
     Let Ws1.Range("Y" & Cnt & "").Value = Rslt
    Next Cnt '     Main Loop for all rows =====================================================

Rem 4 save the changes and close the file
 Wb1.Close savechanges:=True
End Sub