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

[fixer]

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]

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

1Stock Namedatadatadatadatadatadatadatadatadatadatadatada tadatadata


2ACC
100
108
120
128
134
151
6534
30
90
97
103


3ADANIENT
101
109
121
127
135
122
782
40
92
98


4ADANIPORTS
102
110
122
16
137
177
10
50
93
99
104


5ASHOKLEY
103
112
123
131
138
1993
12
60
94
100
105
106
110


6EQUITAS
104
115
124
132
139
8524
20
70
95
101
107
108


7L&TFH
105
117
125
133
140
746
23
80
96
102
109


8
Worksheet: Sheet1 (3)

( see also here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13382&viewfull=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

1Stock Namedatadatadatadatadatadatadatadatadatadatadatada tadatadata


2ACC
100
108
120
128
134
151
6534
30
90
97
103


3ADANIENT
101
109
121
127
135
122
782
40
92
98


4ADANIPORTS
102
110
122
16
137
177
10
50
93
99
104


5ASHOKLEY
1
2
3
4
5
6
7
8
9
10


6EQUITAS
1
2
3
4
5
6
7
8
9
10


7L&TFH
11
12
13
14
15
16
17
18
19
20
21
22
23


8
Worksheet: Sheet1 (3)

Code here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13385&viewfull=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]

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

[DocAElstein]

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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13388&viewfull=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

1Stock Namedatadatadatadatadatadatadatadatadatadatadatada tadatadata


2ACC
100
108
120
128
134
151
6534
30
90
97
103


3ADANIENT
101
109
121
127
135
122
782
40
92
98


4ADANIPORTS
102
110
122
16
137
177
10
50
93
99
104


5ASHOKLEY
1
2
3
4
5
16
137
177
10
50
93
99
104


6EQUITAS
10
50
93
99
5
102
110
122
9
10
11


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

1Stock Namedatadatadatadatadatadatadatadatadatadatadatada tadatadata


2ACC
100
108
120
128
134
151
6534
30
90
97
103


3ADANIENT
101
109
121
127
135
122
782
40
92
98


4ADANIPORTS
102
110
122
16
137
177
10
50
93
99
104


5ASHOKLEY
2
4
6
8
10
32
274
354
20
100
186
198
208


6EQUITAS
20
100
186
198
10
204
220
244
18
20
22


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

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

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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13389&viewfull=1#post13389
( dont forget that you still need the Function Function CL() )

[fixer]

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]

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]

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

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

For Cnt = 2 To Jmax

It should be

For Cnt = 2 To Lr1 ' Jmax

[DocAElstein]

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

1Stock Namedatadatadatadatadatadatadatadatadatadatadatada tadatadatadatadatadatadata


2ACC
100
108
120
128
134
151
6534
30
90
97
103


3ADANIENT
101
109
121
127
135
122
782
40
92
98


4ADANIPORTS
102
110
122
16
137
177
10
50
93
99
104


5ASHOKLEY
1
2
3
4
5
16
137
177


6ANJALIPHARMA
10
50
93
99
5
102
110
122
9
10
11


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

1Stock Namedatadatadatadatadatadatadatadatadatadatadatada tadatadatadatadatadatadata


2ACC
100
108
120
128
134
151
6534
30
90
97
103


3ADANIENT
101
109
121
127
135
122
782
40
92
98


4ADANIPORTS
102
110
122
16
137
177
10
50
93
99
104


5ASHOKLEY
79
158
237
316
395
1264
10823
13983


6ANJALIPHARMA
10
50
93
99
5
102
110
122
9
10
11


7SUNTECK
11
12
13
14
15
16
17
18
19
20
21
22
23


8


9
Worksheet: Sheet1 (5)


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

[fixer]

' 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

[DocAElstein]

I forgot to do that last bit to copy S10 value and paste it to S9 & S8
That is very, very simple - See if you can do it.
If you can't , I will add that bit later for you. I am away for many hours now.

( You are correct, you still need Function CL() )

[fixer]

No Probelm Doc Sir Take ur Time
I am unable to make it u plz have a look once u r free Doc Sir
Have a Awesome Day

[fixer]

Here i want a little change Doc Sir
calculate the total value of column Q of ActualFile.xlsx and if it is Greater than S10 of ActualFile.xlsx then this macro should do the process else do nothing
So plz relook Doc Sir for the same

[DocAElstein]

At end of macro

' In Actual File, copy S10 Value to S9 and S8
Ws.Range("S10").Copy
Ws.Range("S8:S9").PasteSpecial Paste:=xlPasteValues
End Sub

[DocAElstein]

... a little change ..
calculate the total value of column Q of ActualFile.xlsx and if it is Greater than S10 of ActualFile.xlsx then this macro should do the process else do nothing ..
Here is the new macro
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13404&viewfull=1#post13404

( These are the test ranges I used https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13403&viewfull=1#post13403 )

[fixer]

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

[fixer]

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