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

[fixer]

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/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=13607&viewfull=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/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-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/viewtopic.php?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

[DocAElstein]

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

[fixer]

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

[fixer]

Plz see the sample file

[DocAElstein]

Hello Vixer,

A question for you please

[fixer]

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

[fixer]

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 .

[DocAElstein]

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


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.


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.


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.


so plz have a rellok into this
I already did this: I told you:
..I have looked at your files , ( http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11399&viewfull=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

[fixer]

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/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction )

[fixer]

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

[DocAElstein]

This is a similar , shortened version of the question here: ( http://www.excelfox.com/forum/showthread.php/2349-Find-percentage-by-vba )



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

1Column LColumn OColumn PColumn 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

1Column LColumn OColumn PColumn Y


2
3
1
3
0.045


3
3
2
2
0.03


4
3
3
1
0.045


5
Worksheet: Tabelle1




' 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

[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

[DocAElstein]

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

[p45cal]

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 filesThis can only mean that you've got the file name wrong OR that that file is not currently an open file.

[fixer]

both files aren't opened so plz guide sir

[p45cal]

both files aren't opened so plz guide sirthen open them!

[fixer]

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

[DocAElstein]

For example….

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

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/office/vba/api/excel.workbooks.open
https://www.youtube.com/watch?v=Vau4VrBwrHg

[fixer]

Sir there is one more issue in the code so plz have a look
i am attaching the file and the 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
'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

[DocAElstein]

there is one more issue in the code What is the issue??





.....so plz have a look
i am attaching the file and the code
I took a look here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11417&viewfull=1#post11417


Alan

[fixer]

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

[p45cal]

This:
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:

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

[fixer]

Thnx Doc Sir for the Support and for giving ur Great Guidance to me

[fixer]

Thnx p45cal Sir for giving ur precious time and Great Support to this post
Have a Great Day Sir

[fixer]

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

[fixer]

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

[DocAElstein]

Solution is mostly already here:
https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13482&viewfull=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\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ
1295.83
1310
1280.25
1270.2
1310
17388SHORT
1308.79

3NSEADANIENTEQ
151.5
154.9
150
148.35
155
100SHORT
153.015

4NSEADANIPORTSEQ
329.26
336.35
326
323.8
331
15083BUY
325.967

5NSEADANIPOWEREQ
39.6
40
37.75
36.4
39
25SHORT
39.204

6NSEAMARAJABATEQ
636.3
655.7
630
614.6
600
22SHORT
629.937
Worksheet: Sample1 5June

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1NSE
101010
6<
12783AGTT

2NSE
22
6<
12783AGTT

3NSE
17388
6<
12783AGTT

4

5

6
Worksheet: sample2 9June


Run Macro
New macro here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13479&viewfull=1#post13479


After

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1NSE
101010
6<
12783AGTT

2NSE
22
6<
12783AGTT

3NSE
17388
6<
12783AGTT

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

[DocAElstein]

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\ColABCDEFGHIJKL
1NSE
1270
6<
12536AGTT

2NSE
22
6>
1517AGTT

3NSE
25
6<
34425AGTT

4NSE
15083
6>
3855AGTT

5NSE
17388
6<
6531AGTT

6NSE
100
6<
164875AGTT

7NSE
236
6<
164875AGTT

8
Worksheet: Sheet4

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJ
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ
1295.83
1310
1280.25
1270.2
1288
22

3NSEADANIENTEQ
151.5
154.9
150
148.35
151.4
25

4NSEADANIPORTSEQ
329.26
336.35
326
323.8
331
15083

5NSEADANIPOWEREQ
39.6
40
37.75
36.4
39.3
17388

6NSEAMARAJABATEQ
636.3
655.7
630
614.6
655.2
100

7NSEASIANPAINTEQ
1689.435
1711.9
1681.5
1683.1
1694.75
236

8NSEAMBUJACEMEQ
189.882
195.65
189.4
191.55
189.7
1270

9
Worksheet: 1-Sheet1 (2)


After running macro here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13732&viewfull=1#post13732

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ
1295.83
1310
1280.25
1270.2
1288
22SHORT

3NSEADANIENTEQ
151.5
154.9
150
148.35
151.4
25BUY

4NSEADANIPORTSEQ
329.26
336.35
326
323.8
331
15083SHORT

5NSEADANIPOWEREQ
39.6
40
37.75
36.4
39.3
17388BUY

6NSEAMARAJABATEQ
636.3
655.7
630
614.6
655.2
100BUY

7NSEASIANPAINTEQ
1689.435
1711.9
1681.5
1683.1
1694.75
236BUY

8NSEAMBUJACEMEQ
189.882
195.65
189.4
191.55
189.7
1270BUY

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

[fixer]

Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem Sir

[fixer]

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-a-column-has-blank-cell-then-put-any-word.44619/#post-266341
https://eileenslounge.com/viewtopic.php?f=30&t=34937
https://eileenslounge.com/viewtopic.php?f=30&t=34936









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.xl sx")
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

[DocAElstein]

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-a-column-has-blank-cell-then-put-any-word.44619/#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-programming-vba-macros/1321463-if-condition-match-then-delete-entire-row-by-vba-else-do-nothing.html https://excelfox.com/forum/showthread.php/2359-Delete-rows-based-on-match-criteria-in-two-excel-files-1-might-be-csv-file-Opened-in-Excel-Fail-Chaos?p=13495&viewfull=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


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 (http://www.eileenslounge.com/viewtopic.php?p=271300#p271300) 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.php?p=271335#p271335

[fixer]

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

[fixer]

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.xl sx")
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.php?f=30&t=34953&p=271443#p271443
https://chandoo.org/forum/threads/vba-code-for-correction.44637/

[DocAElstein]

You almost had it right here: https://eileenslounge.com/viewtopic.php?p=271427&sid=ccc11b24baced575ceee4002b96a1745#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


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


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/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14565#post14565
Your macro , Sub STEP6() from here
https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14562&viewfull=1#post14562
and here
https://eileenslounge.com/viewtopic.php?p=271385#p271385
has the wrong Lr2

[fixer]

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

[fixer]

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

[DocAElstein]

_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!! )

[fixer]

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

[DocAElstein]

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

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


Plz run the macro and see the output....
what macro shall I run??

[fixer]

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.xl sx")
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

[DocAElstein]

The biggest problem is..
Since a few weeks you have introduced a new problem for yourself which will help to ensure that you fail in everything: You make many cross post and everyone helping you writes there codes slightly differently. So you are getting into a mixed up chaotic mess of different codlings and are beginning to post many wrong files and incorrect or incomplete macros and explanations of what you want.
You are getting into a mixed up mess

I seem to be answering almost the same question over and over again. But it gets harder because you are getting into a mixed up mess and are getting worse and worse at explaining what you want.
Cross posting bad explanations just makes everything worse. It makes it harder for anyone to help you, and more and more people will get fed up with you and stop helping you
You think you are clever by secretly finding new places and registering new account usernames to cross post the same or similar question. But it is just creating a total chaos and you are losing control yourself and mixing up macros and files everywhere.

You can ask the wrong question in a thousand places and still get the wrong answer a thousand times. ( By luck, you might once get the right answer, but you probably would never notice )



The macro you have posted https://excelfox.com/forum/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=14591&viewfull=1#post14591 does the following:
It looks for the data values in column I of Ws1 in column B of Ws2. So the range to be searched is column B in Ws2. The values to be searched for are in column I of Ws1
When a matched value is found we look at the value in column D of Ws2 at the row where the match occurred. Based on whether or not we have “>” in that cell we will add or subtract 1% to the value in E in Ws2 at the row being considered, that is given by loop variable, i
This last bit is not what you want
For example, first we look for 22 (from row I=2) in column B of Ws2. That is found in row 1 of Ws2.
So
R2=WorksheetFunction.Match(.Cells(i, "I"), Ws2., 0)=1
In row 2 of Ws2 in the D column is a < so we take the option of
.Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
That code line takes the value in column E at row 2 of Ws1 , which is 200 and adds 1% which is 2, giving you 202, which is not what you want.
You are pasting in the correct file and correct row. But you are taking the data from the correct file but the wrong row

What you want it to take the value at the row where the match was found in Ws1, which is 1
That will give the output of 100 +1 = 101

The macro does what you asked for:
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

But what you asked for could mean many things and have many different answers. Your explanation was incomplete. You explanation was very bad.




This was error
Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
This is correction
Ws2.Cells(R2, "E").Value - 0.01 * Ws2.Cells(R2, "E").Value

We should be doing the calculations to get the result from the matched row in AlertCodes.xlsx, ( Ws2) , which is given by R2, not i


This is what you want:
Question:
We consider the data values in column I of 1.xls, starting from row 2. Values in column I of 1.xls, starting at row 2, are to be looked for, ( Matched ) in column B of AlertCodes.xlsx
At the row in AlertCodes.xlsx where the match is found, the matched row, the following is to be done:
Consider the value in column D of AlertCodes.xlsx at the matched row in AlertCodes.xlsx
& If column D of AlertCodes.xlsx has this, < , then calculate the 1% of column E of AlertCodes.xlsx & add that 1% to column E of AlertCodes.xlsx in that match row & the result which will come it will be pasted to column K of 1.xls at the row of the considered data value in 1.xls
Or
else if column D of AlertCodes.xlsx has this, > , then calculate the 1% of column E of AlertCodes.xlsx & subtract that 1% from column E of AlertCodes.xlsx in the match row & the result which will come it will be pasted to column K of 1.xls at the row of the considered data value in 1.xls

Solution:
Here https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=14594&viewfull=1#post14594

Before:
_____ Workbook: AlertCodes.xlsx ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1NSE226<100AGTT

[b]2NSE256<200AGTT

3NSE150836<300AGTT
Worksheet: Sheet4 July 13 2020

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ12651282.71246.51275.3124722BUY

3NSEADANIENTEQ151.85165.45151.4151.85152.3525BUY

4NSEADANIPORTSEQ348348338.5346.55338.8515083BUY
Worksheet: 1-Sheet1 13July


After running macro here https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=14594&viewfull=1#post14594

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ12651282.71246.51275.3124722BUY101

3NSEADANIENTEQ151.85165.45151.4151.85152.3525BUY20 2

4NSEADANIPORTSEQ348348338.5346.55338.8515083BUY303
Worksheet: 1-Sheet1 13July




Alan





1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
AlertCodes.xlsx : https://app.box.com/s/jwpjjut9wt3ej7dbns3269ftlpdr7xsm
macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p

Corrected macro, Sub STEP6() : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=14594&viewfull=1#post14594

[fixer]

No Problem Doc Sir
But from now i am only hanging in three forums excelfox,excelforum & eileens lounge
If i provided the sample file earlier to HansV Sir then this problem will not be occured & from next Time i am looking for a change in the macro
then sure i will put the code plus sample file too...
From next time it will not be repeated

[DocAElstein]

Here ( https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=14605&viewfull=1#post14605) is an alternative, just out of interest. Just “for fun”
It is probably only useful for my later reference, that’s all.

The main changes are
_1) I use arrays. ( arr1() , arr2() , arr2B() )
I do this just from personal choice. I do this because arrays work much faster if you are only interested in values with no cell formatting
_2) I changed WorksheetFunction.Match to Application.Match , because I do not like to use On Error Resume Next
I do not need On Error Resume Next for Application.Match , because , if it does not find a match, it does not error. Instead, it returns a VBA error string message, which can be tested for using IsError( __ )
( https://excelfox.com/forum/showthread.php/2349-Copy-and-Paste-based-on-comparisons-Match-and-calculations-of-cells-in-two-workbooks?p=14204&viewfull=1#post14204 )
_3) I do not use _ With _ End With _ because it confuses me

I left the original code lines in , ' commented out for comparison

Here is the alternative: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=14605&viewfull=1#post14605

[fixer]

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

[fixer]

Hi Experts,

I am looking for a macro that will do the below things
Plz see the sample file
there are 3 files ap.xls & BasketOrder.xlsx & macro.xlsm (macro will be placed in macro.xlsm),both files are located in different places so the path will be hardcoded in the macro so that i can change it as per my needs
sheet name can be anything


If column J is BUY of BasketOrder.xlsx then add 1% of column O of ap.xls to column O of ap.xls and compare column O of ap.xls with column L of BasketOrder.xlsx and if column O of ap.xls is smaller than column L of BasketOrder then replace column L of BasketOrder data with column O of ap.xls data(with that added 1% of column O of ap.xls) else do nothing

Or

If column J is SELL of BasketOrder.xlsx then subtract 1% of column P of ap.xls to column P of ap.xls and compare column P of ap.xls with column L of BasketOrder.xlsx and if column P of ap.xls is Greater than column L of BasketOrder then replace column L of BasketOrder data with column P data of ap.xls (with that subtract 1% of column P of ap.xls) else do nothing



Thnx for the Help

macro will be placed in a seperate file




This macro i have for this problem but some issue is there

Sub STEP8()
Application.ScreenUpdating = False
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim TempValue As Double

Dim RowCount1 As Integer
Dim ColumnCount1 As Integer
Dim RowCount2 As Integer
Dim ColumnCount2 As Integer
Dim BOmyArray() As Variant
Dim APmyArray() As Variant

BOExcellFilePath = "C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\ Files\BasketOrder.xlsx"
APExcellFilePath = "C:\Users\WolfieeeStyle\Desktop\ap.xls"

Set wb1 = Workbooks.Open(Filename:=APExcellFilePath)
Set ws1 = wb1.Sheets(1)
RowCount1 = ws1.UsedRange.Rows.Count
ColumnCount1 = ws1.UsedRange.Columns.Count
APmyArray = ws1.Range("A1:U" & ColumnCount1).Value
wb1.Close SaveChanges:=False
Set wb1 = Nothing

Set wb2 = Workbooks.Open(Filename:=BOExcellFilePath)
Set ws2 = wb2.Sheets(1)
RowCount2 = ws2.UsedRange.Rows.Count
ColumnCount2 = ws2.UsedRange.Columns.Count
BOmyArray = ws2.Range("A1:Y" & ColumnCount2).Value

For i = 1 To RowCount2
TempValue = 0
For j = 2 To RowCount1
If (APmyArray(j, 5) = BOmyArray(i, 3)) Then
If (BOmyArray(i, 10) = "BUY") Then
If (APmyArray(i + 1, 15) <> "") Then
TempValue = APmyArray(i + 1, 15) + APmyArray(i + 1, 15) * 0.01
If (TempValue < BOmyArray(i, 12)) Then
ws2.Activate
ws2.Cells(i, 12).Value = TempValue
End If
End If
ElseIf (BOmyArray(i, 10) = "SELL") Then
If (APmyArray(i + 1, 15) <> "") Then
TempValue = APmyArray(i + 1, 15) - APmyArray(i + 1, 15) * 0.01
If (TempValue > BOmyArray(i, 12)) Then
ws2.Activate
ws2.Cells(i, 12).Value = TempValue
End If
End If
End If
End If
Next
Next
Application.ScreenUpdating = True
wb2.Save
wb2.Close
End Sub

[fixer]

Public BOExcellFilePath As String
Public APExcellFilePath As String
Public Sub ApplciationProgram()
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ExcellFilePath As String
Dim TempValue As Double

Dim RowCount1 As Integer
Dim ColumnCount1 As Integer
Dim RowCount2 As Integer
Dim ColumnCount2 As Integer
Dim BOmyArray() As Variant
Dim APmyArray() As Variant

BOExcellFilePath = "C:\Users\WolfieeeStyle\Desktop\BasketOrder.xlsx"
APExcellFilePath = "C:\Users\WolfieeeStyle\Desktop\ap.xls"

Set wb1 = Workbooks.Open(Filename:=APExcellFilePath)
Set ws1 = wb1.Sheets(1)
RowCount1 = ws1.UsedRange.Rows.Count
ColumnCount1 = ws1.UsedRange.Columns.Count
APmyArray = ws1.Range("A1:U" & ColumnCount1).Value
wb1.Close SaveChanges:=False
Set wb1 = Nothing

Set wb2 = Workbooks.Open(Filename:=BOExcellFilePath)
Set ws2 = wb2.Sheets(1)
RowCount2 = ws2.UsedRange.Rows.Count
ColumnCount2 = ws2.UsedRange.Columns.Count
BOmyArray = ws2.Range("A1:Y" & ColumnCount2).Value

For i = 1 To RowCount2
TempValue = 0
For j = 2 To RowCount1
If (APmyArray(j, 5) = BOmyArray(i, 3)) Then
If (BOmyArray(i, 10) = "BUY") Then
If (APmyArray(j, 15) <> "") Then
TempValue = APmyArray(j, 15) + APmyArray(j, 15) * 0.01
If (TempValue < BOmyArray(i, 12)) Then
ws2.Activate
ws2.Cells(i, 12).Value = TempValue
End If
End If
ElseIf (BOmyArray(i, 10) = "SELL") Then
If (APmyArray(j, 16) <> "") Then
TempValue = APmyArray(j, 16) - APmyArray(j, 16) * 0.01
If (TempValue > BOmyArray(i, 12)) Then
ws2.Activate
ws2.Cells(i, 12).Value = TempValue
End If
End If
End If
End If
Next
Next
Application.ScreenUpdating = True

MsgBox "Program successfully completed "
Exit Sub
ErrorHandler:
MsgBox "UNKNOWN ERROR - Error# " & Err.Number & " : " & Err.Description
End Sub





Problem Solved

[fixer]

https://www.excelforum.com/excel-programming-vba-macros/1326813-clear-the-data-if-matches.html


Hi,

I am looking for a macro that doesnt the things mentioned below


If column E of book1.xlsb matches with column Z of ap.xls & column U of ap.xls is postive then match column Z of ap.xls with column B of sheet1 of book1.xlsb & if it matches then clear the data in sheet1 of book1.xlsb
macro will be placed in book1.xlsb so the path should be hardcoded in the macro

clear the data means we have to clear the data from column C of sheet1 of book1.xlsb (we dont have to delete the data of column A & Column B of sheet1 of book1.xlsb )


Thnx For the Help

[fixer]

Problem Solved

[DocAElstein]

Once again you forgot to tell us how and where you got it Solved



“Moderator” Notice

**I am Banning you to prevent you making any more postings here of the type you have been making here and elsewhere under hundreds of different user names at many of the English speaking Excel and Office help forums for the last couple of years.

The type of post that you have been posting suggest that
_ You may be one person or a !!team of many people working at something organised like a Call Centre.
( !! Sometime when you have been “caught” cross posting, you did not know yourself where you cross posted, and asked to be told. ( Or you maybe only wanted to admit to those where you got “caught”) )
_ You have almost no understanding of the English language
_ You may not have a computer and may have no access to Excel
_ You have no interest in Excel or Excel VBA
_ You have almost no knowledge or interest in any of the questions that you are asking
_ You may be simply offering a service of posting other peoples questions and supplying them with any answers you get.
_ You may be part of the development of a question asking and Replying Bot

_ In some cases, something extremely simple to understand, has been explained to you very many times, in great detail , even graphically, such that even a small mentally handicapped child could understand it and remember it. Despite this, you continually ask exactly that same question over and over again: If you are part of a team interested in only posting questions and taking the answer, then you are very badly organised,
Or
There is no real intelligence behind what is producing your questions and posts
_ One of the things you consistently do after receiving a macro is to delete all explanations, explaining 'comments and all files associated, and indeed it appears as if you try to remove almost all record of the coding and the question and answer. This further encourages the posting of the same or similar questions over and over again.

Whatever you are attempting to do, it appears to be extremely, almost insanely, inefficient ,
compared to
a single person with a computer and Excel, and a minimum of basic Excel VBA knowledge trying to achieve the same.

The main reason for the ban is
Whatever you are attempting to do, it is requiring 10-100 times more time than is typically required of helpers at a forum. All indications are that what you are doing will fail to achieve anything, and is therefore a total waste of everyone’s time. At excelfox, the current small number of helpers have only a limited amount of time, but even if we had more members, excelfox would not be the place for you.
I am totally bored shitless with your Threads that are almost always asking the same thing.
## Some of the major forums may be a good place for you to post. There are some senior brain dead morons there who are happy to keep answering the same questions over and over again. Half of them are probably either senile Dement or just plain stupid anyway, and they don’t remember from one day to the next.

These are some suggestions, from me, on how you should continue
_ If you intend to continue, regardless of any of my previous suggestions, in postings of the type as you have done in the past, then you should think about making some changes to your wording, introduce some new canned replies, possibly organise a new set of similar questions and post at the major forums, such as mrexcel.com, excelforum.com, ozgrid.com
_ If you wish to make a career out of posting questions and getting answers without having any real intentions of thinking about anything, then excelfox is not the forum for you to post in. Most of the smaller forums are not the place for you. The larger forums may be able to accommodate you, if you give at least some thought to making it not quite so obvious: Your distinguishing characteristic is that you have been making it much more obvious than others doing the same, do: Many people do the such. At least half the traffic at such forums originates from such. I have passed many people on to such forums and they are making a successful career based on passing on the work done for them by helpers at the major forums. Such is actually encouraged, all be it , not openly, at the major forums.
_ If you have not understood most of this Moderator Notice , then your first priority should be to improve on your English. Indeed, your apparent understanding and ability in communicating in English suggests that you will achieve nothing whatsoever and fail completely in anything at all involving communicating in English.

_ If you are, as you sometimes told me via PM, actively working on an important personal problem requiring VBA , then you are doing it totally wrongly: You have been on the project already for at least two years and have a mixed up set of codings produced by many different people. Some work . Some don’t. You have not the slightest idea or understanding of any of the codings. You will never be able to use them to any effect. You are getting an ever increasingly different set of codings with every post you make, and reply you get, which all just confuses the issues further. You are making negative progress, Bro! You are working and going backwards most of the time.
If , on the other hand, you had a computer, with Excel, and spent a few weeks learning VBA, and then carefully studied all the macros that you have been given, then you would be able to answer most of your further questions, and would have at least a chance of being able to use the codings effectively:-
1 Month learn VBA and 1 month getting answers, partly alone, partly with help from forums = Finished Success
2+ Years posting the same and similar questions and just taking the answers = Never Ending Fail
_ It is unlikely that the macros you have that work will ever be very efficient and will likely be slower than anyone else’s: They will certainly not be the best possible. Giving you better coding has proved to be impossible: It is not possible to pass on better codings because of the ridiculously inefficient way that you are organising whatever it is that you are doing: The person receiving and passing on the coding needs to understand the English language and to understand some basic coding and to understand how to use such better coding. We have tried this a few times, but it proved always completely impossible to do. One example of this is the issue of text files: Because you are mostly dealing with values, the use of text files is almost certainly beneficial and in some cases the only efficient way to proceed. You have completely missed the point on this: You have repeated much work to try to avoid using text files. The problem was, and will never be, the issue of text files themselves. The issue is your total inability or unwillingness to understand anything at all about them.

Another possibility is that you and/ or yous are simply severally mentally handicapped: If that is the case then, then I am , sincerely, sorry for you, but you have no hope of achieving anything with what you are doing at forums , apart from wasting a lot of other people’s time.


##The main purpose of the question section of excelfox is approximately the following:
_1. Promote and improve the understanding of Excel and Excel VBA.
_2. Help people who get stuck on a problem and/or help people who are unsure how to proceed in solving a problem using Excel and Excel VBA.

Your objectives??
I do not know what the true reason is behind your postings. I can’t believe anything you say is your purpose, since you have lied and contradicted yourself in the past. The only thing we know 100% for sure is that your posting types are not for any of the purposes for which the question section of excelfox is intended.
You have had the benefit of the doubt given to you now very many times. You have had lots of chances.
You may be able to continue at some of the major forums, where some people are happy to continue to spend time to answer similar questions from the same source.
I do not think you will get any more replies to the types of postings you have been making at excelfox or at any other of the smaller English speaking Forums. You are wasting your time making any such posts from now on.
**I am Banning you, not as any form of punishment, but purely as in the past , it has proven to be the only way to prevent you wasting yours and other peoples time with your postings.
I do wish you luck and success with what ever it is you are attempting to do. But you should not be doing it at excelfox.
If you are attempting the personal project that you have told me about via PM, then you are going about it in completely the wrong way.
If you are trying to make a career of posting other people’s questions and getting answers for them, then you should post mostly at the major forums and organise yourself better: At least have access to Excel on a computer and learn the basics of VBA. If you are trying to make a career of posting other people’s questions and getting answers for them, as many people do, then you have made the mistake of making it too obvious. Many of the senior helpers at the main forums prefer to think that they are helping people rather than doing their work for them. What they don’t know, does not hurt them. :)


I will leave all your posts in the main forum for a few weeks. Then I will move them all to the test forum. I will probably further merge them. Eventually I may delete them all.


Bro, whatever you are trying to do, its not working. Its never going to work. Its just wasting everybody’s time.
You need first to learn English
Then get a computer with Microsoft Office.
Then learn some basic Excel and Excel VBA
Then start again.
If you ever come to excelfox again you will have to prove to me that
_ You are a real person
_ You are genuinely attempting a personal project and need help
_ You have a computer with Microsoft Office / Excel installed and that you can, and do, use it, and that you understand basic VBA programming.


Ref
https://excelfox.com/forum/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction