Thnx All of u for contributing ur precious time and knowledge for this post
Thnx Alot Have a Great Day
Thnx All of u for contributing ur precious time and knowledge for this post
Thnx Alot Have a Great Day
This:can be replaced wholesale with:Code:Dim Cnt As Long ' Main Loop for all data rows ================================================ ' 3a) ' compare column O is greater or column P is greater For Cnt = 2 To Lr1 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 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 =======================================================
Code:With Ws1.Range("Y2:Y" & Lr1) .FormulaR1C1 = "=MAX(RC[-10]:RC[-9])*0.005*RC[-13]" .Value = .Value End With
Last edited by DocAElstein; 08-11-2019 at 08:11 PM.
Thnx Doc Sir for the Support and for giving ur Great Guidance to me
Thnx p45cal Sir for giving ur precious time and Great Support to this post
Have a Great Day Sir
Either of these 2 conditions are to result in macro will copy value in column I of sample1.xls to field 2(column B) of text file Sample2.xlsx on a new line:
Condition 1) Only If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls then Match Column I of sample1.xls with second field values (column B) of Sample2.xlsx & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of Sample2.xlsx
Condition 2) Only If column K of sample1.xls is lower than Column D of sample1.xls & Column H of sample1.xls is lower than column K of sample1.xls then Match Column I of sample1.xls with second field values (column B) of Sample2.xlsx & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of Sample2.xlsx
plz see the attached file
Plz Note
All files are located in a different path (So in the macro the path will be hardcoded, Assume any path & I will edit the path manually by myself)
Sheet name can be anything
Macro will be putted in a vba.xlsm
I am looking for a macro that will do the process mentioned below
If column I of 1.xls matches with sheet4 of column B of Alertcodes.xlsx & sheet4 of column D of Alertcodes.xlsx contains this symbol > then put SHORT in column J of 1.xls
If column I of 1.xls matches with sheet4 of column B of Alertcodes.xlsx & sheet4 of column D of Alertcodes.xlsx contains this symbol < then put BUY in column J of 1.xls
Plz Note
All files are located in a different path (So in the macro the path will be hardcoded, Assume any path & I will edit the path manually by myself)
Sheet name can be anything
Macro will be putted in a vba.xlsm
So plz have a look & take ur time
Thnx For ur Great Help
Solution is mostly already here:
https://excelfox.com/forum/showthrea...ll=1#post13482
Note errors in question…
Excel files have rows and columns
Either of these 2 conditions are to result in macro will copy value in column I of sample1.xls to column 2(column B) of text file Sample2.xlsx on a new line:
Condition 1) Only If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls then Match Column I of sample1.xls with second column values (column B) of Sample2.xlsx & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to the next free row of column (column B) of Sample2.xlsx
Condition 2) Only If column K of sample1.xls is lower than Column D of sample1.xls & Column H of sample1.xls is lower than column K of sample1.xls then Match Column I of sample1.xls with second column (column B) of Sample2.xlsx & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to the next free row of second column (column B) of Sample2.xlsx
The main adjustments to the previous macro are removing a lot of stuff associated with importing and remaking the text file. The basic logic remains the same
Before:
_____ Workbook: Sample1.xls ( Using Excel 2007 32 bit )
Worksheet: Sample1 5June
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 SHORT 3 NSE ADANIENT EQ SHORT 4 NSE ADANIPORTS EQ BUY 5 NSE ADANIPOWER EQ SHORT 6 NSE AMARAJABAT EQ SHORT
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Worksheet: sample2 9June
Row\Col A B C D E F G H I J K 1 NSE < A GTT 2 NSE < A GTT 3 NSE < A GTT 4 5 6
Run Macro
New macro here: https://excelfox.com/forum/showthrea...ll=1#post13479
After
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Worksheet: sample2 9June
Row\Col A B C D E F G H I J K 1 NSE < A GTT 2 NSE < A GTT 3 NSE < A GTT 4 5 6
sample1.xls : https://app.box.com/s/xh58fgjl74w06hvsd53jriqkohdm6a3q
sample2.xlsx : https://app.box.com/s/np7kbvjydnyiu95pzyrgn76qi1uqg0ma
vba.xlsm : https://app.box.com/s/lf6otsrl42m6vxxvycjo04zidya6pd2m
A Folk, A Forum, A Fuhrer ….
Where are your “After” ???
You have not given me after results to check against… Remember to do that always please.
So if the results are not exactly what you want , then you will have to modify the macro yourself.. I can only go by your description / explanation. ( Often your explanations are in error - your discriptions often have mistakes in them, so a before and after is important)..
If column I of 1.xls matches with sheet4 of column B of Alertcodes.xlsx & sheet4 of column D of Alertcodes.xlsx contains this symbol > then put SHORT in column J of 1.xls
If column I of 1.xls matches with sheet4 of column B of Alertcodes.xlsx & sheet4 of column D of Alertcodes.xlsx contains this symbol < then put BUY in column J of 1.xls
Or
__ If column I of 1.xls matches with sheet4 of column B of Alertcodes.xlsx Then..
___ look at symbol in column D, 4th worksheet of AlertCodes.xlsx for that matched row in column D, 4th worksheet of AlertCodes.xlsx
____ If symbol is > then put SHORT in column J of 1.xls for the matched row
____ If symbol < then put BUY in column J of 1.xls for the matched row
Before:
_____ Workbook: AlertCodes.xlsx ( Using Excel 2007 32 bit )
Worksheet: Sheet4
Row\Col A B C D E F G H I J K L 1 NSE < A GTT 2 NSE > A GTT 3 NSE < A GTT 4 NSE > A GTT 5 NSE < A GTT 6 NSE < A GTT 7 NSE < A GTT 8
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Worksheet: 1-Sheet1 (2)
Row\Col A B C D E F G H I J 1 Exchange Symbol Series/Expiry Open High Low Prev Close LTP 2 NSE ACC EQ 3 NSE ADANIENT EQ 4 NSE ADANIPORTS EQ 5 NSE ADANIPOWER EQ 6 NSE AMARAJABAT EQ 7 NSE ASIANPAINT EQ 8 NSE AMBUJACEM EQ 9
After running macro here: https://excelfox.com/forum/showthrea...ll=1#post13732
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Worksheet: 1-Sheet1 (2)
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 SHORT 3 NSE ADANIENT EQ BUY 4 NSE ADANIPORTS EQ SHORT 5 NSE ADANIPOWER EQ BUY 6 NSE AMARAJABAT EQ BUY 7 NSE ASIANPAINT EQ BUY 8 NSE AMBUJACEM EQ BUY 9
Alan
AlertCodes.xlsx : https://app.box.com/s/jwpjjut9wt3ej7dbns3269ftlpdr7xsm
1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
Vba.xlsm : https://app.box.com/s/lf6otsrl42m6vxxvycjo04zidya6pd2m
Last edited by DocAElstein; 06-16-2020 at 01:26 PM.
A Folk, A Forum, A Fuhrer ….
Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem Sir
Moderator Notice...
Here we go again cycle number 3 , slightly different strategy, posting almost the same question in a few cross posts, then juggling around , passing given macros from one place to the other to give the impression of having done something himself.....
https://chandoo.org/forum/threads/if...9/#post-266341
https://eileenslounge.com/viewtopic.php?f=30&t=34937
https://eileenslounge.com/viewtopic.php?f=30&t=34936
Code:Sub STEP4() Dim wb1 As Workbook, ws1 As Worksheet Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") Set ws1 = wb1.Worksheets.Item(1) Dim arrWs1() As Variant: Let arrWs1() = ws1.Range("A1").CurrentRegion.Value2 Dim Lr1 As Long: Let Lr1 = UBound(arrWs1(), 1) Dim arrS1() As Variant Let arrS1() = ws1.Range("A1:J" & Lr1 & "").Value Dim WbA As Workbook, WsA4 As Worksheet Set WbA = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xlsx") Set WsA4 = WbA.Worksheets.Item(4) Dim RwCnt4 As Long: Let RwCnt4 = WsA4.Range("A" & WsA4.Rows.Count & "").End(xlUp).Row Dim arrWsA4() As Variant: Let arrWsA4() = WsA4.Range("A1:K" & RwCnt4 & "").Value2 Dim ClmB() As Variant: Let ClmB() = WsA4.Range("B1:B" & RwCnt4 & "").Value Dim Cnt As Long For Cnt = 2 To Lr1 Dim MtchRes As Variant Let MtchRes = Application.Match(arrWs1(Cnt, 9), ClmB(), 0) If IsError(MtchRes) Then Else If arrWsA4(MtchRes, 4) = ">" Then Let arrS1(Cnt, 10) = "SHORT" ElseIf arrWsA4(MtchRes, 4) = "<" Then Let arrS1(Cnt, 10) = "BUY" Else End If End If Next Cnt Let ws1.Range("A1:J" & Lr1 & "").Value2 = arrS1() wb1.Save wb1.Close End Sub
Hi,
This is the Macro
I have to add one more condition in this macro
If it doesn't match then put delete word
Plz let me know if sample file is required?
Thnx For the Help
Last edited by DocAElstein; 07-09-2020 at 08:35 PM.
Posting Permissions
Reply With Quote
Bookmarks