Copy and Paste based on comparisons/Match and calculations of cells in two workbooks

Moderator notice.
After going around in circles at a few other forums for a year or two , and despite multi registration duplicating cross posting tricks , the OP , Avinash came here and started again.
After going around in circles here , 11 months and 3 bans later we are starting again as I write this, … here for example: https://excelfox.com/forum/showthrea...ll=1#post13607

The total waste of time caused by the OP is arising through an apparent inability, or deliberately trying not to, read or understand anything what so ever of what anyone does for him, with the possible exception of looking for a macro in a post , copying it and possibly running it. If that macro then does not do what he wants ( which he often does not know himself or might change erratically as time goes on ) , he posts any rubbish in a reply , then…
He will usually , read or understand nothing what so ever of what anyone does for him, with the possible exception of looking for a macro in a post , copying it and possibly running it. If that macro then does not do what he wants ( which he often does not know himself or might change erratically as time goes on ) , he posts any rubbish in a reply , then…
He will usually , read or understand nothing what so ever of what anyone does for him, with the possible exception of looking for a macro in a post , copying it and possibly running it. If that macro then does not do what he wants ( which he often does not know himself or might change erratically as time goes on ) , he posts any rubbish in a reply , then…

He will usually , read or understand nothing what so ever of what anyone does for him, with the possible exception of looking for a macro in a post , copying it and possibly running it. If that macro then does not do what he wants ( which he often does not know himself or might change erratically as time goes on ) , he posts any rubbish in a reply , then…

He will usually , read or understand nothing what so ever of what anyone does for him, with the possible exception of looking for a macro in a post , copying it and possibly running it. If that macro then does not do what he wants ( which he often does not know himself or might change erratically as time goes on ) , he posts any rubbish in a reply , then…
_... and so on…
Eventually, whether or not he gets what he wants he will post some canned reply like…“Thx for great help and ur precious time „..,“ problem solved Sir… „

Then later very likely, here , or somewhere else, or more likely at several places, the whole thing will start again…

He rarely makes any progress and mostly goes around in circles.
I am not sure if he does it on purpose or is insane or he just a total dim pig shit for brains. I expect a bit of all of those….

( Avinash Singh : https://excelfox.com/forum/showthrea...h-Introduction )

---

.... and now he has started again at Eileen's Lounge again, - he came here after getting banned there, now he is starting there again
https://www.eileenslounge.com/viewto...p?f=30&t=34936
this guy should be on the stage

---

My files name
target1.xlsx
target2.xlsx
macro.xlsm
If column E of target1.xlsx matches with column A of target2.xlsx then look column O of target1.xlsx is greater or column P of target1.xlsx is greater, whichever is greater calculate the 0.50% of that and multiply that with column K of target1.xlsx and paste the result to target2.xlsx from column C(if column C has data then column D and if column D has data then column E and so on...) the result should be in minus means whatever is the result put minus sign in that along with result

Hello vixer
Welcome to EXCELFOX

I only have time to look quickly at this, so I expect I may not fully get to understand exactly what you want.
A working example from you is always helpful for such questions, preferably in the form of sample files with a reduced amount of sample data.

I expect I will not get exactly what you want. But it may be some help. Also, as I do not know your level of VBA understanding , so I am not sure at what level to aim this.

My offering below is most likely not the best way. In VBA we can usually do such things in a large number of different ways.
So, in the absence of any more information from you, I will do a way that just amuses me at the moment! :)

I like playing with closed workbook references. So in my solution, the only thing that needs to be open is the file
macro.xlsm

The other two data files,
target1.xlsx
target2.xlsx
, will need to be stored in the same folder as macro.xlsm

Finally the file target2.xlsx will be opened to paste in the result.

I use the second worksheet of macro.xlsm, as a help worksheet. ( In my coding I have the name of that worksheet in a variable , strWsm


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276673#p276673
https://eileenslounge.com/viewtopic.php?p=276751#p276751
https://eileenslounge.com/viewtopic.php?p=276754#p276754
https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367
https://eileenslounge.com/viewtopic.php?p=274368#p274368
https://eileenslounge.com/viewtopic.php?p=274370#p274370
https://eileenslounge.com/viewtopic.php?p=274578#p274578
https://eileenslounge.com/viewtopic.php?p=274577#p274577
https://eileenslounge.com/viewtopic.php?p=274474#p274474
https://eileenslounge.com/viewtopic.php?p=274579#p274579
https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

i am getting error run time error 9 subscript out of range
error highlighted line Dim Wsm As Worksheet: Set Wsm = ThisWorkbook.Worksheets.Item(2) ' Second worksheet, (as worksheet object) in this workbook


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdPM65i9PG
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdPQHFk_zm
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
https://www.youtube.com/watch?v=fEHKPhJxgBA&lc=Ugxx8_MjhC9FDaQgcHN4AaABAg
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg. ACGbG9c76OWACGbjKa7H8k
https://www.youtube.com/watch?v=jpjYm4UvyWk&lc=Ugx_Qd4rfAN_ZYcJbo94AaABAg
https://www.youtube.com/watch?v=GyPHaydeng0&lc=UgzE4a4f_e_y9Rk5OR94AaABAg
https://www.youtube.com/watch?v=I5FkNG94BcQ&lc=UgxXnkEHqulXSR5tXwh4AaABAg
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6BSa173Z
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6-64Xpgl
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ms39yjd
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ZXJwRCM
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4Pr15NUt
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4I83JelY
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGADdMo2n-hyF
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg. 8mjgPNoTt_HABa3tnAjhZU
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Plz see the sample file

Hello Vixer,

A question for you please

i am unable to solve this problem plz have a look and help me sir
Thnx in advance Sir

---

Moderator Translation:
I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a fucking spacko .
I am not here to waste my time so I aint reading anything you wrote - you are the mug here to waste yout time so just give me the codez

Thnx DocAElstein Sir for giving ur precious time and great support to this post but plz provide me the simple vba code that i will attach to my file and i will run the code and it will do the work and file should be automatically selected i dont want the pop up which comes and then i have to select file in short one click and its done so plz have a rellok into this and guide sir

---

Moderator Translation:
I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a fucking spacko .

Hello fixer, ( rider , roger, RangeRover , Leonardo , umpsbug, xyz1234, Pftang Pftang OLE WigWam Biscuit Barrel )

---

Quote:

---

Originally Posted by fixer

i am unable to solve this problem plz have a look and help me sir
...

---

What is the problem? :confused:
I am unable to understand your problem.
I am unable to understand your question.
You have not supplied enough information.
I do not know what you want: You have not shown me what you want.

---

Quote:

---

Originally Posted by fixer

plz provide me the simple vba code that i will attach to my file and i will run the code and it will do the work

---

What work should it do?
I am unable to understand your problem.
I am unable to understand your question.
You have not supplied enough information.
I do not know what you want: You have not shown me what you want.

---

Quote:

---

Originally Posted by fixer

i dont want the pop up which comes and then i have to select file in short one click and its done

---

:confused: I do not understand what you are saying.

---

Quote:

---

Originally Posted by fixer

so plz have a rellok into this

---

I already did this: I told you:

Quote:

---

Originally Posted by DocAElstein

..I have looked at your files , ( http://www.excelfox.com/forum/showth...ll=1#post11399 )
But I do not understand any further what you want. :confused:

---

:confused:
I am still unable to understand your problem.
I am still unable to understand your question.

---

I do not think I can help you further because you are unable or unwilling to explain fully what you want.

Alan

Thnx Sir for the support have a great day

---

Moderator Translation:
I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a total fucking spacko ... and never mind anyway, fuck off you mug, as some other twat gave me a macro at one of my many duplicated cross postings.......( Intro to Avinash Singh... https://excelfox.com/forum/showthrea...h-Introduction )

file name is sample1.xlsx
vba code will be added to different file
compare column O is greater or column P is greater
if column O is greater then calculate the 0.50% of column O and after getting the 0.50% of column O multiply the same with column L and paste the result in column Y
and if column P is greater then calculate the 0.50% of column P and after getting the 0.50% of column P multiply the same with column L and paste the result in column Y
save the changes and close the file
all this i have to do by vba so plz have a look and do needful

This is a similar , shortened version of the question here: ( http://www.excelfox.com/forum/showth...centage-by-vba )

---

Quote:

---

Originally Posted by fixer

file name is sample1.xlsx
compare column O is greater or column P is greater
if column O is greater then calculate the 0.50% of column O and after getting the 0.50% of column O multiply the same with column L and paste the result in column Y
and if column P is greater then calculate the 0.50% of column P and after getting the 0.50% of column P multiply the same with column L and paste the result in column Y
save the changes and close the file

---

Example

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

Row\Col	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		Column L			Column O	Column P									Column Y
2		3			1	3
3		3			2	2
4		3			3	1
5

Worksheet: Tabelle1


After running routine, Sub Vixer2()
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		Column L			Column O	Column P									Column Y
2		3			1	3									0.045
3		3			2	2									0.03
4		3			3	1									0.045
5

Worksheet: Tabelle1

Code:

---

' Option Explicit
' file name is sample1.xlsx
' compare column O is greater or column P is greater
' if column O is greater then calculate the 0.50% of column O and after getting the 0.50% of column O multiply the same with column L and paste the result in column Y
' if column P is greater then calculate the 0.50% of column P and after getting the 0.50% of column P multiply the same with column L and paste the result in column Y
' save the changes and close the file
'
Sub Vixer2() ' http://www.excelfox.com/forum/showthread.php/2352-calculation-and-multiply-by-vba
Rem 1 Workbook and worksheets info
Dim Wb1 As Workbook: Set Wb1 = Workbooks("sample1.xlsx") ' Set using workbooks collection object of open files
Dim Ws1 As Worksheet: Set Ws1 = Wb1.Worksheets.Item(1) ' First worksheet, (as worksheet object) in open file "sample1.xlsx"
Dim Lr1 As Long, Lr2 As Long
 Let Lr1 = 4: Lr2 = 4 ' For this example I am using just three rows of data, and a header

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 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
    '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

---

Alan

I met with an error while runing the code
error name - run time error 9 subscript out of range
highlighted line : Set Wb1 = Workbooks("ap.xls") ' Set using workbooks collection object of open files

:confused: I do not have that code line in the macro that I gave you.

Quote:

---

Originally Posted by fixer

I met with an error while runing the code
error name - run time error 9 subscript out of range
highlighted line : Set Wb1 = Workbooks("ap.xls") ' Set using workbooks collection object of open files

---

This can only mean that you've got the file name wrong OR that that file is not currently an open file.

both files aren't opened so plz guide sir

Quote:

---

Originally Posted by fixer

both files aren't opened so plz guide sir

---

then open them!

Sir but i need the code should open all that file and after the process completed it should close that file

For example….

Workbooks.Open Filename:="C:\...____.\..___...\sample1.xlsx"
Or
Workbooks.Open "C:\...____.\..___...\sample1.xlsx"
Or as in the given macro , change to

Code:

---

Rem 1 Workbook and worksheets info
Dim Wb1 As Workbook: Set Wb1 = Workbooks.Open("C:\...____.\..___...\sample1.xlsx")

---

Ref:
https://de.lmgtfy.com/?q=vba+open+workbook
https://docs.microsoft.com/en-us/off...workbooks.open
https://www.youtube.com/watch?v=Vau4VrBwrHg

Sir there is one more issue in the code so plz have a look
i am attaching the file and the code

Code:

---

Option Explicit
' file name is sample1.xlsx
' compare column O is greater or column P is greater
' if column O is greater then calculate the 0.50% of column O and after getting the 0.50% of column O multiply the same with column L and paste the result in column Y
' if column P is greater then calculate the 0.50% of column P and after getting the 0.50% of column P multiply the same with column L and paste the result in column Y
' save the changes and close the file
'
Sub Vixer2() ' http://www.excelfox.com/forum/showth...ultiply-by-vba
'1a) Workbook and worksheets info
Dim Wb1 As Workbook: Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\ap.xls") ' Set using workbooks collection object of open files
Dim Ws1 As Worksheet: Set Ws1 = Wb1.Worksheets.Item(1) ' First worksheet, (as worksheet object) in open file "sample1.xlsx"
Dim Lr1 As Long, Lr2 As Long
 Let Lr1 = 4: Lr2 = 4 ' For this example I am using just three rows of data, and a header
Rem 3
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 =====================================================
' save the changes and close the file
 Wb1.Close savechanges:=True
End Sub

---

Quote:

---

Originally Posted by fixer

there is one more issue in the code

---

What is the issue??

---

Quote:

---

Originally Posted by fixer

.....so plz have a look
i am attaching the file and the code

---

I took a look here: http://www.excelfox.com/forum/showth...ll=1#post11417


Alan

Thnx All of u for contributing ur precious time and knowledge for this post
Thnx Alot Have a Great Day

This:

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 =======================================================

---

can be replaced wholesale with:

Code:

---

With Ws1.Range("Y2:Y" & Lr1)
  .FormulaR1C1 = "=MAX(RC[-10]:RC[-9])*0.005*RC[-13]"
  .Value = .Value
End With

---

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 )

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	1295.83	1310	1280.25	1270.2	1310	17388	SHORT	1308.79
3	NSE	ADANIENT	EQ	151.5	154.9	150	148.35	155	100	SHORT	153.015
4	NSE	ADANIPORTS	EQ	329.26	336.35	326	323.8	331	15083	BUY	325.967
5	NSE	ADANIPOWER	EQ	39.6	40	37.75	36.4	39	25	SHORT	39.204
6	NSE	AMARAJABAT	EQ	636.3	655.7	630	614.6	600	22	SHORT	629.937

Worksheet: Sample1 5June

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

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	101010	6	<	12783	A					GTT
2	NSE	22	6	<	12783	A					GTT
3	NSE	17388	6	<	12783	A					GTT
4
5
6

Worksheet: sample2 9June


Run Macro
New macro here: https://excelfox.com/forum/showthrea...ll=1#post13479


After

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

Row\Col	A	B	C	D	E	F	G	H	I	J	K
1	NSE	101010	6	<	12783	A					GTT
2	NSE	22	6	<	12783	A					GTT
3	NSE	17388	6	<	12783	A					GTT
4		100
5		25
6

Worksheet: sample2 9June

---

sample1.xls : https://app.box.com/s/xh58fgjl74w06hvsd53jriqkohdm6a3q
sample2.xlsx : https://app.box.com/s/np7kbvjydnyiu95pzyrgn76qi1uqg0ma
vba.xlsm : https://app.box.com/s/lf6otsrl42m6vxxvycjo04zidya6pd2m

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 )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L
1	NSE	1270	6	<	12536	A					GTT
2	NSE	22	6	>	1517	A					GTT
3	NSE	25	6	<	34425	A					GTT
4	NSE	15083	6	>	3855	A					GTT
5	NSE	17388	6	<	6531	A					GTT
6	NSE	100	6	<	164875	A					GTT
7	NSE	236	6	<	164875	A					GTT
8

Worksheet: Sheet4

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

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	1295.83	1310	1280.25	1270.2	1288	22
3	NSE	ADANIENT	EQ	151.5	154.9	150	148.35	151.4	25
4	NSE	ADANIPORTS	EQ	329.26	336.35	326	323.8	331	15083
5	NSE	ADANIPOWER	EQ	39.6	40	37.75	36.4	39.3	17388
6	NSE	AMARAJABAT	EQ	636.3	655.7	630	614.6	655.2	100
7	NSE	ASIANPAINT	EQ	1689.435	1711.9	1681.5	1683.1	1694.75	236
8	NSE	AMBUJACEM	EQ	189.882	195.65	189.4	191.55	189.7	1270
9

Worksheet: 1-Sheet1 (2)


After running macro here: https://excelfox.com/forum/showthrea...ll=1#post13732

_____ 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	1295.83	1310	1280.25	1270.2	1288	22	SHORT
3	NSE	ADANIENT	EQ	151.5	154.9	150	148.35	151.4	25	BUY
4	NSE	ADANIPORTS	EQ	329.26	336.35	326	323.8	331	15083	SHORT
5	NSE	ADANIPOWER	EQ	39.6	40	37.75	36.4	39.3	17388	BUY
6	NSE	AMARAJABAT	EQ	636.3	655.7	630	614.6	655.2	100	BUY
7	NSE	ASIANPAINT	EQ	1689.435	1711.9	1681.5	1683.1	1694.75	236	BUY
8	NSE	AMBUJACEM	EQ	189.882	195.65	189.4	191.55	189.7	1270	BUY
9

Worksheet: 1-Sheet1 (2)



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

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

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
https://www.excelforum.com/excel-pro...o-nothing.html https://excelfox.com/forum/showthrea...ll=1#post13495

---

Hi
I am not sure if I understand exactly what you want.

If Application.Match does not find a match, then the code line does not error, instead it returns an error string.
We can use IsError( ) to tell us if an error string has been returned. This will be True if an error string is detected in the ( )

Possibly you want this

Code:

---

    Let MtchRes = Application.Match(arrWs1(Cnt, 9), ClmB(), 0)
        If IsError(MtchRes) Then ' No match was found
        Let arrS1(Cnt, 10) = "delete word"
        Else ' A match was found
            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


---

If that is not what you want, then provide a file or files to show me what you want

Alan

---

P.S:
IsError( ) does not tell you if a code line has eroded. If a code line errors, then the macro will stop and you will get an error pop up to tell you about the error.
Application.Match will return either
_ a number telling you the position along where it finds in the second argument what you have in the first argument ( Application.Match( First argument , second argument , 0 ) In other words it returns you the position along of the match
or
_ an error string if it does not find a match

Note also:
WorksheetFunction.Match works a bit differently. If it does not find a match then it will error. I expect your next 50 duplicated cross posted questions will come when a macro you have with that in does not work….
.... Edit: For example didn't take long... https://eileenslounge.com/viewtopic....271335#p271335

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

Code:

---

Sub STEP6()
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Dim Wb1 As Workbook, Wb2 As Workbook
    Dim r2&, lr&, i&
   
    Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
    Set Ws1 = Wb1.Worksheets.Item(1)
    Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xlsx")
    Set Ws2 = Wb2.Worksheets.Item(4)
    With Ws1
        lr = .Cells(.Rows.Count, "I").End(xlUp).Row
        For i = 2 To lr
            ' Reset r2
            r2 = 0
            ' Avoid error messages
            On Error Resume Next
            ' Try to get r2
            r2 = WorksheetFunction.Match(.Cells(i, "I"), Ws2.[B:B], 0)
            ' Restore error handling
            On Error GoTo 0
            ' Only set column K if r2 is valid
            If r2 > 0 Then
                If Ws2.Cells(r2, "D") = ">" Then
                    .Cells(i, "K").Value = .Cells(i, "D").Value - 0.01 * .Cells(i, "D").Value
                Else
                    .Cells(i, "K").Value = .Cells(i, "D").Value + 0.01 * .Cells(i, "D").Value
                End If
            End If
        Next i
    End With
    Wb1.Save
    Wb1.Close
    Wb2.Close
   
End Sub

---

This codes calculate 1% of column of column D of 1.xls
but what i wanted is instead of column D, it should calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx and paste the result to same place where the current macro is putting,rest everything will be same

Kindly note AlertCodes.xlsx doesn't have headers so keep a eye on the same

Thnx for the Help

https://eileenslounge.com/viewtopic....271443#p271443
https://chandoo.org/forum/threads/vb...rection.44637/

You almost had it right here: https://eileenslounge.com/viewtopic....6a1745#p271427
You were close to correct.

I try to explain again what Hans tried to explain ( I think you almost understood ):
__With Ws1
………
……….
…………………. Cells(i, "E") ……… .Cells(i, "E")
__End With
is almost =
.....Ws1.Cells(i, "E") …… Ws1.Cells(i, "E")

You want Ws2 ( for Alertcodes.xlsx ) , not Ws1

Maybe like

Code:

---

          Wtih Ws1
                If Ws2.Cells(r2, "D") = ">" Then 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx
                    .Cells(i, "K").Value = Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
                Else
                    .Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
                End If
          End With

---

This will also be the same

Code:

---

                If Ws2.Cells(r2, "D") = ">" Then 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx
                    Ws1.Cells(i, "K").Value = Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
                Else
                    Ws1.Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
                End If

---

If this does not work, then you must explain again exactly what you want the macro to do.

Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
is 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of AlertCodes.xlsx

---

This was wrong:
Ws2.Cells(i, "E").Value - 0.01 *.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * .Cells(i, "E").Value
It is 'calculate the data 1% with column E of AlertCodes.xlsx & add the calculated result with Column E of 1.xls
It is the same as
Ws2.Cells(i, "E").Value - 0.01 *Ws1.Cells(i, "E").Value
and
Ws2.Cells(i, "E").Value + 0.01 * Ws1.Cells(i, "E").Value

---

( I never use
__ With
__ End With
because it confuses me.
But lots of other people, like Hans do use it. Everyone writes codes a bit differently, because all VBA codes can be written in many ways. )

---

Also
Have you seen this ? : https://excelfox.com/forum/showthrea...4565#post14565
Your macro , Sub STEP6() from here
https://excelfox.com/forum/showthrea...ll=1#post14562
and here
https://eileenslounge.com/viewtopic....271385#p271385
has the wrong Lr2

Thnx Alot Soc Sir for helping me in solving this problem Sir
Problem Solved
Have a Great Day

No Doc Sir this problem is not solved yet
i am sending the sample file with output in next 10 min

Plz run the macro and see the output Doc Sir
I was at job at that time & so that's y mistake happened
macro will be placed in a seperate file

_1) What macro are you using? ( I got confused with all your similar duplicated cross postings )
_2) Remind me again. What exactly should the macro do? ( I see the output in file, but please also explain again in words what is going on, because I got confused with all your similar duplicated cross postings )

( I have time first tomorrow to look again. So take your time and make sure you have correct files, and all infomation correct!! )

If column I of 1.xls matches with column B of Alertcodes.xlsx & column D has < this then calculate the 1% of of column E & add that 1% to column E & the result which will come it will be pasted to column K of 1.xls

If column I of 1.xls matches with column B of Alertcodes.xlsx & column D has > this then calculate the 1% of of column E & subtract that 1% to column E & the result which will come it will be pasted to column K of 1.xls



sheet name can be anything
macro will be placed in macro.xlsm

You didn't answer one of my questions...

Quote:

---

Originally Posted by DocAElstein

_1) What macro are you using?....)

---

post the macro you are using....

---

Quote:

---

Originally Posted by fixer

Plz run the macro and see the output....

---

what macro shall I run??

Code:

---

Sub STEP6()
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Dim Wb1 As Workbook, Wb2 As Workbook
    Dim r2&, lr&, i&
   
    Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
    Set Ws1 = Wb1.Worksheets.Item(1)
    Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xlsx")
    Set Ws2 = Wb2.Worksheets.Item(4)
    With Ws1
        lr = .Cells(.Rows.Count, "I").End(xlUp).Row
        For i = 2 To lr
            ' Reset r2
            r2 = 0
            ' Avoid error messages
            On Error Resume Next
            ' Try to get r2
            r2 = WorksheetFunction.Match(.Cells(i, "I"), Ws2.[B:B], 0)
            ' Restore error handling
            On Error GoTo 0
            ' Only set column K if r2 is valid
            If r2 > 0 Then
                If Ws2.Cells(r2, "D") = ">" Then
                    .Cells(i, "K").Value = Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
                Else
                    .Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
                End If
          End If
        Next i
    End With
    Wb1.Save
    Wb1.Close
    Wb2.Close
   
End Sub

---