Copy and paste of data if matches Conditional calculation and pasting of the data

**fixer** · 05-18-2020, 10:16 AM

First question
If column J has data in actual file.xlsx then match column B of actual file.xlsx with column A of sheet 1 of 2.xlsx and if it matches then copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to sheet 1 of 2.xlsx in the row of the matched value in column A of sheet 1 of 2.xlsx
i have pasted the result in sheet3 of 2.xlsx but the result should be in sheet1(I have pasted the result in sheet3 only for understanding purpose)

Second question:
If column J has data in actual file.xlsx then match column B of actual file.xlsx with column A of sheet 1 of 2.xlsx and if it matches then double the value of that row of 2.xlsx
vba will be placed in macro.xlsm
So plz have a look and help me in solving this problem Sir
there can be highlighted colour in sheet1 of 2.xlsx but ignore it (dont do anything with that, dont remove the highlighted colour )
i have pasted the result in sheet2 of 2.xlsx but the result should be in sheet1(I have pasted the result in sheet2 only for understanding purpose)
I am really Sorry Doc Sir for editing the same but this will be perfect way to do the same

**DocAElstein** · 05-18-2020, 06:28 PM

Before:

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	103	112	123	131	138	1993	12	60	94	100	105	106	110
6	EQUITAS	104	115	124	132	139	8524	20	70	95	101	107	108
7	L&TFH	105	117	125	133	140	746	23	80	96	102	109
8

Worksheet: Sheet1 (3)

( see also here: https://excelfox.com/forum/showthrea...ll=1#post13382 )

After

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	1	2	3	4	5	6	7	8	9	10
6	EQUITAS	1	2	3	4	5	6	7	8	9	10
7	L&TFH	11	12	13	14	15	16	17	18	19	20	21	22	23
8

Worksheet: Sheet1 (3)

Code here: https://excelfox.com/forum/showthrea...ll=1#post13385

Share 'Actual File.xlsx' : https://app.box.com/s/9dfaq1997whyyj0jq7ew30sixcmq9zpm
Share '2.xlsx' : https://app.box.com/s/ij24a4nmnnvi0h4qr13h49ro05aouatk
Share 'macro.xlsm' : https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt

**fixer** · 05-18-2020, 08:10 PM

No Doubt this code is perfect, But I am really Sorry Doc Sir, I edited the question (bcoz that will create a trouble)

**DocAElstein** · 05-19-2020, 03:35 AM

Please do not make major edits to a post after you have had responses because it makes another confusing mess: All the following posts now look stupid because they are answering a different question
But its not a major problem this time as the modification is not too difficult. No big deal this time.

So now, it is similar, but no longer a copy, paste. Instead the row values are doubled. Before and After screenshots are here https://excelfox.com/forum/showthrea...ll=1#post13388
Note: I think your supplied After is wrong! - L&TFH should not be considered from Actual File.xlsx, because J of that row is not 1

Before

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	1	2	3	4	5	16	137	177	10	50	93	99	104
6	EQUITAS	10	50	93	99	5	102	110	122	9	10	11
7	L&TFH	11	12	13	14	15	16	17	18	19	20	21	22	23
8

Worksheet: Sheet1 (4)

If column J has data in actual file.xlsx then match column B of actual file.xlsx with column A of sheet 1 of 2.xlsx and if it matches then double the value of that row of 2.xlsx
After

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	2	4	6	8	10	32	274	354	20	100	186	198	208
6	EQUITAS	20	100	186	198	10	204	220	244	18	20	22
7	L&TFH	11	12	13	14	15	16	17	18	19	20	21	22	23
8

Worksheet: Sheet1 (4)

This is the important code change
Question 1

Code:

        Else ' Cnt is now at the row number of where  2.xlsx sheet1 column A  was found in  Actual File.xlsx sheet1 column B
        Dim Lc1Cnt As Long: Let Lc1Cnt = Ws1.Cells.Item(Cnt, Ws1.Columns.Count).End(xlToLeft).Column
         Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").ClearContents ' clear row Cnt of all data before pasting
         Rng22.Copy Destination:=Ws1.Range("B" & Cnt & "")                ' copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to the row in  sheet 1 of 2.xlsx  at the row number of the matched value of 2.xlsx sheet1

Question 2

Code:

        Else ' Cnt is now at the row number of where  2.xlsx sheet1 column A  was found in  Actual File.xlsx sheet1 column B
        Dim Lc1Cnt As Long: Let Lc1Cnt = Ws1.Cells.Item(Cnt, Ws1.Columns.Count).End(xlToLeft).Column
        ' Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").ClearContents ' clear row Cnt of all data before pasting
        ' Rng22.Copy Destination:=Ws1.Range("B" & Cnt & "")                ' copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to the row in  sheet 1 of 2.xlsx  at the row number of the matched value of 2.xlsx sheet1
         Let Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Value = Ws1.Evaluate("=2*" & Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Address & "") '   then double the value of that row of 2.xlsx

Full macro Here https://excelfox.com/forum/showthrea...ll=1#post13389
( dont forget that you still need the Function Function CL() )

**fixer** · 05-19-2020, 09:41 AM

Thnx Doc Sir for understanding L&TFH mistake which happened from my end (& from now no further edits after getting response)
Thnx Alot for helping me in solving this Problem
Have a Great Day
Problem Solved

**fixer** · 05-19-2020, 11:28 AM

calculate the total value of column Q of ActualFile.xlsx and if it is Greater than S10 of ActualFile.xlsx then do nothing & if it is lower than S10 of ActualFile.xlsx then divide S10 of ActualFile.xlsx with the total value of Column Q of ActualFile.xlsx & match column B of actual file.xlsx with column A of 2.xlsx and if it matches then increased the number that much time of 2.xlsx (Take the roundfigure of the number instead of 79.86 take 79 to increase that much time, see the cell highlighted in green colour ) & copy S10 value and paste it to S9 & S8
I have tried to explain everything in sheet2 of both the files but the actual file is sheet1 of both file
vba will be placed in macro.xlsm
So plz have a look and help me in solving this problem Sir
there can be highlighted colour in sheet1 of 2.xlsx but ignore it (dont do anything with that, dont remove the highlighted colour )

**DocAElstein** · 05-19-2020, 12:33 PM

Originally Posted by fixer

.... from now no further edits after getting response)...

You can make some minor changes, and small edits after someone has replied. That is OK
But don’t make major or big changes if they make the following post look silly or more difficult to understand later what is going on.

Also please don’t remove or change uploaded files.

We ask all this because the Thread is not just for you and me. Excelfox is an open Forum for everybody. Other people may later want to read and understand all the posts. So they may need all the information in the same original order.

If you want to change a file, or ask a follow up question, or ask for a modification, then add a new post in the same Thread is better. This will make the Thread easier later for anyone to read and understand all of what is going on.

If it is a big change, or much different question, then a new Thread is sometimes better. (But this is not too important - you can choose - because if I think that Thread questions are similar, I can merge the Threads into one Thread later )

Alan

**DocAElstein** · 05-19-2020, 03:07 PM

There is a small error in my last macro
This is wrong

Code:

    For Cnt = 2 To Jmax

It should be

Code:

    For Cnt = 2 To Lr1 ' Jmax

**DocAElstein** · 05-19-2020, 03:11 PM

Before

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	1	2	3	4	5	16	137	177
6	ANJALIPHARMA	10	50	93	99	5	102	110	122	9	10	11
7	SUNTECK	11	12	13	14	15	16	17	18	19	20	21	22	23
8
9

Worksheet: Sheet1 (5)

After:

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Stock Name	data	data	data	data	data	data	data	data	data	data	data	data	data	data	data	data	data	data
2	ACC	100	108	120	128	134	151	6534	30	90	97	103
3	ADANIENT	101	109	121	127	135	122	782	40	92	98
4	ADANIPORTS	102	110	122	16	137	177	10	50	93	99	104
5	ASHOKLEY	79	158	237	316	395	1264	10823	13983
6	ANJALIPHARMA	10	50	93	99	5	102	110	122	9	10	11
7	SUNTECK	11	12	13	14	15	16	17	18	19	20	21	22	23
8
9

Worksheet: Sheet1 (5)

Macro here: https://excelfox.com/forum/showthrea...ll=1#post13396

**fixer** · 05-19-2020, 03:30 PM

Code:

'     http://www.excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort
Public Function CL(ByVal lclm As Long) As String '         http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

I used this at the end of the code
But the code is not correcting this see the sample pic which i have attached
copy S10 value and paste it to S9 & S8 after increasing the value