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

**fixer** · 08-11-2019, 12:59 PM

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

**p45cal** · 08-11-2019, 03:00 PM

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

**fixer** · 08-26-2019, 10:39 AM

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

**fixer** · 08-30-2019, 07:59 AM

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

**fixer** · 06-09-2020, 10:53 AM

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** · 06-10-2020, 07:52 PM

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** · 06-14-2020, 11:52 PM

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

**DocAElstein** · 06-14-2020, 11:54 PM

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

**fixer** · 07-05-2020, 03:57 PM

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

**fixer** · 07-08-2020, 10:22 PM

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