Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

**DocAElstein** · 05-15-2020, 08:18 PM

Some notes related to these posts
https://excelfox.com/forum/showthrea...ll=1#post13318
http://www.eileenslounge.com/viewtopic.php?f=30&t=34610
http://www.eileenslounge.com/viewtop...267706#p267706

Later

**DocAElstein** · 05-18-2020, 01:19 PM

In support of this post:
https://excelfox.com/forum/showthrea...pplied-over-it

_____ Workbook: address sheet.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G
1	Address	Door#	Direction	street name	roadtype	street name + roadtype	City Name
2	204 6 AVE NW	204	6	AVE	NW
3	2510 5 AVE N	2510	5	AVE	N
4	1 CICADA RD	1		CICADA	RD
5	100 annacis Pkwy	100		annacis	Pkwy
6	100 MAIN ST	100		MAIN	ST
7	10008 107 ST	10008		107	ST
8	1001 110 AVE	1001		110	AVE
9	10010 102A AVE NW	10010		102A AVE	NW
10	10115 110 AVE	10115		110	AVE
11	102 11 AVE S	102	S	11	AVE
12	10205 134 AVE NW	10205		134 AVE	NW
13	10235 101 ST NW	10235		101 ST	NW
14	10365 97 ST NW	10365		97 ST	NW
15	105 MARTIN ST	105		MARTIN	ST
16	10504 100 AVE	10504		100	AVE
17	10600 100 ST	10600		100	ST

Worksheet: Sheet1

**DocAElstein** · 05-18-2020, 03:30 PM

Some notes in support in answering this question: https://excelfox.com/forum/showthrea...ata-if-matches

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)

Before:

If column J has data in actual file.xlsx then match column B of actual file.xlsx
_____ Workbook: Actual File.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	J
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ASHOKLEY	EQ	65	65.35	60.55	63.3	63.3	1
3	NSE	BANKBARODA	EQ	62.1	62.95	56.15	56.65	56.65	1
4	NSE	BEL	EQ	66.15	66.75	62.4	65.65	65.65	1
5	NSE	EQUITAS	EQ	82	82.05	71	73.05	73.05	1
6	NSE	FEDERALBNK	EQ	68	68.45	62.45	63.1	63.1	1
7	NSE	GAIL	EQ	85	88.8	79.1	79.95	79.95	1
8	NSE	IDFCFIRSTB	EQ	32.1	32.35	27.2	27.55	27.55

Worksheet: Sheet1

_.................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
_____ Workbook: 2 18May.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A
1	Stock Name
2	ACC
3	ADANIENT
4	ADANIPORTS
5	ASHOKLEY
6	EQUITAS
7	L&TFH
8

Worksheet: Sheet1

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
_____ Workbook: 2 18May.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	6	7	8	9	10

Worksheet: Sheet2

_.......copy the (only first row)entire row of data from sheet2 of 2.xlsx and paste it to 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)

After:

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Stock Name	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	AMBUJACEM	105	117	125	133	140	746	23	80	96	102	109
8

Worksheet: Sheet3

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

macro for solution to this Thread:
https://excelfox.com/forum/showthrea...ata-if-matches

( Remember to include Public Function CL() )

Code:

Sub CopyPaste20()  '   https://excelfox.com/forum/showthread.php/2494-Copy-and-paste-of-data-if-matches
Rem 1 Worksheets info
' 2.xlsx
Dim Wb2 As Workbook
 Set Wb2 = Workbooks("2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb2.Worksheets.Item(1)
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA() As Variant: Let arrA() = Ws1.Range("A1:A" & Lr1 & "").Value2          '  2.xlsx sheet1 column A
Dim Ws2 As Worksheet: Set Ws2 = Wb2.Worksheets.Item(2)
Dim Rng22 As Range: Set Rng22 = Ws2.Range("A1").CurrentRegion                    ' Row to be copied - (only first row)entire row of data from sheet2 of 2.xlsx

' Actual File.xlsx
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("Actual File.xlsx")
 Set Ws = Wb.Worksheets.Item(1)
Dim Jmax As Long: Let Jmax = Ws.Range("J" & Ws.Rows.Count & "").End(xlUp).Row
Dim arrB() As Variant: Let arrB() = Ws.Range("B1:B" & Jmax & "").Value2          ' Actual File.xlsx sheet1 column B

Rem 2 do it
Dim Cnt '                                               this is for - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
    For Cnt = 2 To Jmax
    Dim MtchRes As Variant
     Let MtchRes = Application.Match(arrA(Cnt, 1), arrB(), 0)  '    - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
        If IsError(MtchRes) Then
        ' no match  do nothing
        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
        End If
    Next Cnt
End Sub

'     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

**DocAElstein** · 05-19-2020, 02:57 AM

Notes for question 2 here
https://excelfox.com/forum/showthrea...ll=1#post13379
https://excelfox.com/forum/showthrea...ll=1#post13387

Before is as here ,
https://excelfox.com/forum/showthrea...ll=1#post13382
, but ignore Sheet2 - no row is to be copied

If column J has data in actual file.xlsx then match column B of actual file.xlsx

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

_Row\^Col	A	B	C	D	E	F	G	H	J
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ASHOKLEY	EQ	65	65.35	60.55	63.3	63.3	1
3	NSE	BANKBARODA	EQ	62.1	62.95	56.15	56.65	56.65	1
4	NSE	BEL	EQ	66.15	66.75	62.4	65.65	65.65	1
5	NSE	EQUITAS	EQ	82	82.05	71	73.05	73.05	1
6	NSE	FEDERALBNK	EQ	68	68.45	62.45	63.1	63.1	1
7	NSE	GAIL	EQ	85	88.8	79.1	79.95	79.95	1
8	NSE	IDFCFIRSTB	EQ	32.1	32.35	27.2	27.55	27.55
9	NSE	IOC	EQ	93	93.65	87.25	87.9	87.9
10	NSE	L&TFH	EQ	90	91.55	80.5	81.65	81.65
11

Worksheet: Sheet1 (2)

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

_____ Workbook: 2 (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

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 (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	22	24	26	28	30	32	34	36	38	40	42	44	46
8

Worksheet: Sheet2

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

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

Macro for last post

Code:

Sub CopyPaste20Q2()  ' Question 2  https://excelfox.com/forum/showthread.php/2494-Copy-and-paste-of-data-if-matches
'    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13388&viewfull=1#post13388
Rem 1 Worksheets info
' 2.xlsx
Dim Wb2 As Workbook
 Set Wb2 = Workbooks("2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb2.Worksheets.Item(1)
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA() As Variant: Let arrA() = Ws1.Range("A1:A" & Lr1 & "").Value2          '  2.xlsx sheet1 column A
 ' Dim Ws2 As Worksheet: Set Ws2 = Wb2.Worksheets.Item(2)
' Dim Rng22 As Range: Set Rng22 =   Ws2.Range("A1").CurrentRegion                    ' Row to be copied - (only first row)entire row of data from sheet2 of 2.xlsx

' Actual File.xlsx
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("Actual File.xlsx")
 Set Ws = Wb.Worksheets.Item(1)
Dim Jmax As Long: Let Jmax = Ws.Range("J" & Ws.Rows.Count & "").End(xlUp).Row
Dim arrB() As Variant: Let arrB() = Ws.Range("B1:B" & Jmax & "").Value2          ' Actual File.xlsx sheet1 column B

Rem 2 do it
Dim Cnt '                                               this is for - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
    For Cnt = 2 To Jmax
    Dim MtchRes As Variant
     Let MtchRes = Application.Match(arrA(Cnt, 1), arrB(), 0)  '    - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
        If IsError(MtchRes) Then
        ' no match  do nothing
        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
        End If
    Next Cnt
End Sub

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

Macro for this post:
https://excelfox.com/forum/showthrea...ll=1#post13397

Code:


Sub ConditionalCalcPaste()   '    https://excelfox.com/forum/showthread.php/2495-Conditional-calculation-and-pasting-of-the-data
Rem 1 Worksheets info
'1a)  2.xlsx
Dim Wb2 As Workbook
 Set Wb2 = Workbooks("2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb2.Worksheets.Item(1)
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA() As Variant: Let arrA() = Ws1.Range("A1:A" & Lr1 & "").Value2          '  2.xlsx sheet1 column A
'Dim Ws2 As Worksheet: Set Ws2 = Wb2.Worksheets.Item(2)
'Dim Rng22 As Range: Set Rng22 = Ws2.Range("A1").CurrentRegion                    ' Row to be copied - (only first row)entire row of data from sheet2 of 2.xlsx

'1b) Actual File.xlsx
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("Actual File.xlsx")
 Set Ws = Wb.Worksheets.Item(1)
Dim Lr As Long: Let Lr = Ws.Range("A" & Ws.Rows.Count & "").End(xlUp).Row                                                 ' Dim Jmax As Long: Let Jmax = Ws.Range("J" & Ws.Rows.Count & "").End(xlUp).Row

Dim rngIn As Range: Set rngIn = Ws.Range("A1:S" & Lr & "")
Dim arrIn() As Variant, arrOut() As Variant: Let arrIn() = rngIn.Value2
Dim arrB() As Variant: Let arrB() = Ws.Range("B1:B" & Lr & "").Value2            '  Ws.Range("B1:B" & Jmax & "").Value2          ' Actual File.xlsx sheet1 column B

'1c '  calculate the total value of column Q of ActualFile.xlsx and if it is Greater than S10 of ActualFile.xlsx then
Dim SomeQ As Double: Let SomeQ = Ws.Evaluate("=SUM(Q2:Q" & Lr & ")") '   total value of column Q of ActualFile.xlsx
 Let SomeQ = Application.WorksheetFunction.Round(SomeQ, 2)
Dim S10Val As Double: Let S10Val = arrIn(10, 19)                     '   S10 of ActualFile.xlsx
    If SomeQ > S10Val Then      '  total value of column Q of ActualFile.xlsx and if it is Greater than S10 of ActualFile.xlsx then do nothing
    ' do nothing
    ElseIf SomeQ < S10Val Then  ' 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
    Dim S10dQ As Double: Let S10dQ = S10Val / SomeQ ' Divide S10 of ActualFile.xlsx with the total value of Column Q of ActualFile.xlsx
      Let S10dQ = Int(S10dQ) ' Application.WorksheetFunction.Round(S10dQ, 4)
    Dim Cnt '                                               this is for - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
        For Cnt = 2 To Lr1 '                               Jmax
        Dim MtchRes As Variant
         Let MtchRes = Application.Match(arrA(Cnt, 1), arrB(), 0)  '    - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
            If IsError(MtchRes) Then
            ' no match  do nothing
            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("=" & S10dQ & "*" & Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Address & "")  ' Ws1.Evaluate("=2*" & Ws1.Range("B" & Cnt & ":" & CL(Lc1Cnt) & Cnt & "").Address & "") '   then double the value of that row of 2.xlsx
            End If
        Next Cnt
    Else
    ' Sum = S10
    End If ' SumQ>S10
End Sub

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

**DocAElstein** · 05-20-2020, 12:12 AM

Test ranges used to answer this post:
https://excelfox.com/forum/showthrea...ll=1#post13401

Before:

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

_Row\^Col	A	B	C	D	E	F	G	H	J	L	M	N	O	P	Q
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ASHOKLEY	EQ	65	65.35	60.55	63.3	63.3	1	1	60	1.055	1.055	54	56.97
3	NSE	BANKBARODA	EQ	62.1	62.95	56.15	56.65	56.65	1	6	60	0.944167	5.665	54	50.985
4	NSE	BEL	EQ	66.15	66.75	62.4	65.65	65.65	1	6	60	1.094167	6.565	54	59.085
5	NSE	EQUITAS	EQ	82	82.05	71	73.05	73.05	1	1	60	1.2175	1.2175	54	65.745
6	NSE	FEDERALBNK	EQ	68	68.45	62.45	63.1	63.1	1	6	60	1.051667	6.31	54	56.79
7	NSE	GAIL	EQ	85	88.8	79.1	79.95	79.95	1	6	60	1.3325	7.995	54	71.955
8	NSE	IDFCFIRSTB	EQ	32.1	32.35	27.2	27.55	27.55		1	60	0.459167	0.459167	54	24.795
9	NSE	IOC	EQ	93	93.65	87.25	87.9	87.9		1	60	1.465	1.465	54	79.11
10	NSE	L&TFH	EQ	90	91.55	80.5	81.65	81.65		6	51	1.60098	9.605882	54	86.45294
11

Worksheet: Sheet1 (2)

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

Worksheet: Sheet1 (5)

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

_Row\^Col	O	P	Q	R	S
6	6.31	54	56.79
7	7.995	54	71.955	Total Fund Amount	8387.320769
8	0.459167	54	24.795	Current Fund Amount	9000
9	1.465	54	79.11	Fund Allocated	8000
10	9.605882	54	86.45294	Profit Amount	1000
11		Sum is	551.8879

Worksheet: Sheet1 (2)

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

_Row\^Col	Q
2	56.97
3	50.985
4	59.085
5	65.745
6	56.79
7	71.955
8	24.795
9	79.11
10	86.45294
11	=SUM(Q2:Q10)

Worksheet: Sheet1 (2)

In this example sum of column Q is less than Range S10 value so nothing is done

**DocAElstein** · 05-20-2020, 12:24 AM

Macro for last post, and to answer this post:
https://excelfox.com/forum/showthrea...ll=1#post13401

Code:

Sub CopyPaste20Q2b()  ' https://excelfox.com/forum/showthread.php/2494-Copy-and-paste-of-data-if-matches?p=13401&viewfull=1#post13401
Rem 1 Worksheets info
' 2.xlsx
Dim Wb2 As Workbook
 Set Wb2 = Workbooks("2.xlsx")
Dim Ws1 As Worksheet: Set Ws1 = Wb2.Worksheets.Item(1)
Dim Lr1 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA() As Variant: Let arrA() = Ws1.Range("A1:A" & Lr1 & "").Value2          '  2.xlsx sheet1 column A
'Dim Ws2 As Worksheet: Set Ws2 = Wb2.Worksheets.Item(2)
'Dim Rng22 As Range: Set Rng22 = Ws2.Range("A1").CurrentRegion                    ' Row to be copied - (only first row)entire row of data from sheet2 of 2.xlsx

' Actual File.xlsx
Dim Wb As Workbook, Ws As Worksheet
 Set Wb = Workbooks("Actual File.xlsx")
 Set Ws = Wb.Worksheets.Item(1)
Dim Jmax As Long: Let Jmax = Ws.Range("J" & Ws.Rows.Count & "").End(xlUp).Row
Dim arrB() As Variant: Let arrB() = Ws.Range("B1:B" & Jmax & "").Value2          ' Actual File.xlsx sheet1 column B
'1c '  calculate the total value of column Q of ActualFile.xlsx and if it is Greater than S10 of ActualFile.xlsx then
Dim Lr As Long: Let Lr = Ws.Range("A" & Ws.Rows.Count & "").End(xlUp).Row
Dim SomeQ As Double: Let SomeQ = Ws.Evaluate("=SUM(Q2:Q" & Lr & ")") '   total value of column Q of ActualFile.xlsx
 Let SomeQ = Application.WorksheetFunction.Round(SomeQ, 2)
Dim S10Val As Double: Let S10Val = Ws.Range("S10").Value                     '   S10 of ActualFile.xlsx
    If SomeQ > S10Val Then      '  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
    Rem 2 do it
    Dim Cnt '                                               this is for - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B but only as far as JMax
        For Cnt = 2 To Lr1 ' Jmax
        Dim MtchRes As Variant
         Let MtchRes = Application.Match(arrA(Cnt, 1), arrB(), 0)  '    - going down column A of 2.xlsx sheet1 looking for a match in  Actual File.xlsx sheet1 column B
            If IsError(MtchRes) Then
            ' no match  do nothing
            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
            End If
        Next Cnt
    Else
    ' else do nothing
    End If
End Sub