vba Copy Paste Conditional to put remark 1 2 3 .. etc

Moderstor notice:
Copy of a similar post in another forum, just for future referrence... ( Post made by Avinash in one of his many forum names ,
https://www.excelforum.com/excel-pro...een-files.html )

Hi there, I am looking for a macro As mentioned below
Plz see the sample file attached below
If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: 1 20June excelforum.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
1200

1200

1173

1194.2

1194.2

22

3
NSE ADANIENT EQ
141.4

143.7

141.4

141.05

141.05

25

4
NSE ADANIPORTS EQ
309

309

309

309.25

309.25

15083

5
NSE ADANIPOWER EQ
33.3

33.3

32.1

32.35

32.35

17388

6
NSE AMARAJABAT EQ
555

578

555

572.85

572.85

100

7
NSE ASIANPAINT EQ
1529

1529

1529

1552.95

1552.95

236

8

Worksheet: 1-Sheet1 20 June excelforum

_____ Workbook: Macro 20June excelforum.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

D

E

F

G

H

I

J

K

L

M

1
Symbol

2
ACC
22

1

2

3

4

3
ADANIENT
25

1

2

3

4
ADANIPORTS
15083

1

5
ADANIPOWER
17388

1

2

3

4

5

6

7

6
AMARAJABAT
100

1

2

3

4

5

6

7

8

9

10

11

7
ASIANPAINT
236

8
AMBUJACEM
1270

9
APOLLOHOSP
157

10
APOLLOPIPE
14361

11
ASHOKLEY
212

12
AUROPHARMA
275

13
AXISBANK
5900

14
BAJAJ-AUTO
16669

Worksheet: Sheet1 20 June excelforum

Original question in this Thread: Post#1
https://excelfox.com/forum/showthrea...mark-1-2-3-etc
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc
If column I is sell then see the value of column K & if column K is Greater than sheet2 of column E then put the remark in sheet3 in the stock name from column B
If column I is buy then see the value of column K & if column K is lower than sheet2 of column F then put the remark in sheet3 in the stock name from column B
remark will be in series like 1,2,3,4,5,6 and so on
vba is palced in a separate file
all files are located in same place
and after putting the remark clear sheet 1 and sheet 2

Post #25:
https://excelfox.com/forum/showthrea...ll=1#post13161
https://excelfox.com/forum/showthread.php/2433-vba-Copy-Paste-Conditional-to-put-remark-1-2-3-etc?p=13161&viewfull=1#post13161
If column B of 2.xlsm match with column B of 1.xls then paste the data from column C of 2.xls as 1,2,3,4,5 and so on....
&
If column B of 2.xlsm doesn't match with column B of 1.xls then delete all the data from column C of that row

macro will be placed in 2.xlsm
all files re located in different path
sheet name can be anything

_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	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

excelforum question 20 June

_____ Workbook: 1 20June excelforum.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
1200

1200

1173

1194.2

1194.2

22

3
NSE ADANIENT EQ
141.4

143.7

141.4

141.05

141.05

25

4
NSE ADANIPORTS EQ
309

309

309

309.25

309.25

15083

5
NSE ADANIPOWER EQ
33.3

33.3

32.1

32.35

32.35

17388

6
NSE AMARAJABAT EQ
555

578

555

572.85

572.85

100

7
NSE ASIANPAINT EQ
1529

1529

1529

1552.95

1552.95

236

8

Worksheet: 1-Sheet1 20 June excelforum
If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: Macro 20June excelforum.xlsm ( Using Excel 2007 32 bit )

_Row\^Col A B C D E F G H I J K L M

1 Symbol

2 ACC 22 1 2 3 4

3 ADANIENT 25 1 2 3

4 ADANIPORTS 15083 1

5 ADANIPOWER 17388 1 2 3 4 5 6 7

6 AMARAJABAT 100 1 2 3 4 5 6 7 8 9 10 11

7 ASIANPAINT 236

Worksheet: Sheet1 20 June excelforum

Code:

Symbol ACC 22 1 2 3 4 ADANIENT 25 1 2 3 ADANIPORTS 15083 1 ADANIPOWER 17388 1 2 3 4 5 6 7 AMARAJABAT 100 1 2 3 4 5 6 7 8 9 10 11 ASIANPAINT 236 AMBUJACEM 1270 APOLLOHOSP 157 APOLLOPIPE 14361 ASHOKLEY 212 AUROPHARMA 275 AXISBANK 5900 BAJAJ-AUTO 16669 BAJAJFINSV 16675 BAJFINANCE 317 BALKRISIND 335 BANKBARODA 4668 BATAINDIA 371 BEL 383 BERGEPAINT 404 BHARATFORG 422 BHARTIARTL 10604 BHEL 438 BIOCON 11373 BOSCHLTD 2181 BPCL 526 BRITANNIA 547 CADILAHC 7929 CANBK 10794 CASTROLIND 1250 CENTURYTEX 625 CESC 628 CHOLAFIN 685 CIPLA 694 COALINDIA 20374 COLPAL 15141 CONCOR 4749 CUMMINSIND 1901 DABUR 772 DIVISLAB 10940 DLF 14732 DRREDDY 881 EICHERMOT 910 EQUITAS 16852 ESCORTS 958 EXIDEIND 676 FEDERALBNK 1023 GAIL 4717 GLENMARK 7406 GMRINFRA 13528 GODREJCP 10099 GRASIM 1232 HAVELLS 9819 HCLTECH 7229 HDFC 1330 HDFCBANK 1333 HEROMOTOCO 1348 HINDALCO 1363 HINDPETRO 1406 HINDUNILVR 1394 IBULHSGFIN 30125 ICICIBANK 4963 ICICIPRULI 18652 IDEA 14366 IDFCFIRSTB 11184 IGL 11262 INDIGO 11195 INDUSINDBK 5258 INFRATEL 29135 INFY 1594 IOC 1624 ITC 1660 JINDALSTEL 6733 JSWSTEEL 11723 JUBLFOOD 18096 JUSTDIAL 29962 KOTAKBANK 1922 L&TFH 24948 LICHSGFIN 1997 LT 11483 LUPIN 10440 M&M 2031 M&MFIN 13285 MANAPPURAM 19061 MARICO 4067 MARUTI 10999 MCDOWELL-N 10447 MFSL 2142 MGL 17534 MINDTREE 14356 MOTHERSUMI 4204 MRF 2277 MUTHOOTFIN 23650 NATIONALUM 6364 NBCC 31415 NCC 2319 NESTLEIND 17963 NIITTECH 11543 NMDC 15332 NTPC 11630 OIL 17438 ONGC 2475 PAGEIND 14413 PEL 2412 PETRONET 11351 PFC 14299 PIDILITIND 2664 PNB 10666 POWERGRID 14977 PVR 13147 RAMCOCEM 2043 RBLBANK 18391 RECLTD 15355 RELIANCE 2885 SAIL 2963 SBIN 3045 SHREECEM 3103 SIEMENS 3150 SRF 3273 SRTRANSFIN 4306 SUNPHARMA 3351 SUNTV 13404 TATACHEM 3405 TATAMOTORS 3456 TATAMTRDVR 16965 TATAPOWER 3426 TATASTEEL 3499 TCS 11536 TECHM 13538 TITAN 3506 TORNTPHARM 3518 TORNTPOWER 13786 TVSMOTOR 8479 UBL 16713 UJJIVAN 17069 ULTRACEMCO 11532 UPL 11287 VEDL 3063 VOLTAS 3718 WIPRO 3787 ZEEL 3812

_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	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

Alternative Solutions from other forums

This and the next few posts are purely done out of interest looking at the alternative solutions supplied elsewhere for the similar question…. vba Copy Paste Conditional to put remark 1 2 3 .. etc…..

Here we analyse the solutions to this….

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

1200

1173

1194.2

1194.2

22

3
NSE ADANIENT EQ
141.4

143.7

141.4

141.05

141.05

25

4
NSE ADANIPORTS EQ
309

309

309

309.25

309.25

15083

5
NSE ADANIPOWER EQ
33.3

33.3

32.1

32.35

32.35

17388

6
NSE AMARAJABAT EQ
555

578

555

572.85

572.85

100

7
NSE ASIANPAINT EQ
1529

1529

1529

1552.95

1552.95

236

8

Worksheet: 1-Sheet1 20 June excelforum

If column D of 1.xls is equal to Column E of 1.xls & column D of 1.xls is not equal to column F of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is not equal to column E of 1.xls then match Column I of 1.xls with column B of macro.xlsm & if it matches the put the remark in series as shown in the sample file(We will put the remark from column C of macro.xlsm & if column C has data then from column D and so on... in series wise)
If column D of 1.xls is equal to Column F of 1.xls & column D of 1.xls is equal to column E of 1.xls then don't do anything
Plz note: Macro will be placed in macro.xlsm which i have attached below & 1.xls & macro.xlsm may be in different path so I will edit the path As per my needs
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col A B C D E F G H I J K L M

1 Symbol

2 ACC 22 1 2 3 4

3 ADANIENT 25 1 2 3

4 ADANIPORTS 15083 1

5 ADANIPOWER 17388 1 2 3 4 5 6 7

6 AMARAJABAT 100 1 2 3 4 5 6 7 8 9 10 11

7 ASIANPAINT 236

Worksheet: Sheet1 20 June excelforum

Code:

Symbol ACC 22 1 2 3 4 ADANIENT 25 1 2 3 ADANIPORTS 15083 1 ADANIPOWER 17388 1 2 3 4 5 6 7 AMARAJABAT 100 1 2 3 4 5 6 7 8 9 10 11 ASIANPAINT 236 AMBUJACEM 1270 APOLLOHOSP 157 APOLLOPIPE 14361 ASHOKLEY 212 AUROPHARMA 275 AXISBANK 5900 BAJAJ-AUTO 16669 BAJAJFINSV 16675 BAJFINANCE 317 BALKRISIND 335 BANKBARODA 4668 BATAINDIA 371 BEL 383 BERGEPAINT 404 BHARATFORG 422 BHARTIARTL 10604 BHEL 438 BIOCON 11373 BOSCHLTD 2181 BPCL 526 BRITANNIA 547 CADILAHC 7929 CANBK 10794 CASTROLIND 1250 CENTURYTEX 625 CESC 628 CHOLAFIN 685 CIPLA 694 COALINDIA 20374 COLPAL 15141 CONCOR 4749 CUMMINSIND 1901 DABUR 772 DIVISLAB 10940 DLF 14732 DRREDDY 881 EICHERMOT 910 EQUITAS 16852 ESCORTS 958 EXIDEIND 676 FEDERALBNK 1023 GAIL 4717 GLENMARK 7406 GMRINFRA 13528 GODREJCP 10099 GRASIM 1232 HAVELLS 9819 HCLTECH 7229 HDFC 1330 HDFCBANK 1333 HEROMOTOCO 1348 HINDALCO 1363 HINDPETRO 1406 HINDUNILVR 1394 IBULHSGFIN 30125 ICICIBANK 4963 ICICIPRULI 18652 IDEA 14366 IDFCFIRSTB 11184 IGL 11262 INDIGO 11195 INDUSINDBK 5258 INFRATEL 29135 INFY 1594 IOC 1624 ITC 1660 JINDALSTEL 6733 JSWSTEEL 11723 JUBLFOOD 18096 JUSTDIAL 29962 KOTAKBANK 1922 L&TFH 24948 LICHSGFIN 1997 LT 11483 LUPIN 10440 M&M 2031 M&MFIN 13285 MANAPPURAM 19061 MARICO 4067 MARUTI 10999 MCDOWELL-N 10447 MFSL 2142 MGL 17534 MINDTREE 14356 MOTHERSUMI 4204 MRF 2277 MUTHOOTFIN 23650 NATIONALUM 6364 NBCC 31415 NCC 2319 NESTLEIND 17963 NIITTECH 11543 NMDC 15332 NTPC 11630 OIL 17438 ONGC 2475 PAGEIND 14413 PEL 2412 PETRONET 11351 PFC 14299 PIDILITIND 2664 PNB 10666 POWERGRID 14977 PVR 13147 RAMCOCEM 2043 RBLBANK 18391 RECLTD 15355 RELIANCE 2885 SAIL 2963 SBIN 3045 SHREECEM 3103 SIEMENS 3150 SRF 3273 SRTRANSFIN 4306 SUNPHARMA 3351 SUNTV 13404 TATACHEM 3405 TATAMOTORS 3456 TATAMTRDVR 16965 TATAPOWER 3426 TATASTEEL 3499 TCS 11536 TECHM 13538 TITAN 3506 TORNTPHARM 3518 TORNTPOWER 13786 TVSMOTOR 8479 UBL 16713 UJJIVAN 17069 ULTRACEMCO 11532 UPL 11287 VEDL 3063 VOLTAS 3718 WIPRO 3787 ZEEL 3812

_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	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

The solution from karmapala https://www.excelforum.com/excel-pro...ml#post5353174

Code:

Sub karmapala() ' https://www.excelforum.com/excel-programming-vba-macros/1319768-if-condition-met-then-put-the-remark-between-files.html#post5353174 Dim arr() As Variant Set wb1 = Workbooks("1.xls") Set sh1 = wb1.Sheets("1-Sheet1") Set Rng = sh1.Range("D2", sh1.Range("D" & Rows.Count).End(xlUp)) Set wb2 = Workbooks("Macro.xlsm") Set sh2 = wb2.Sheets("Sheet1") x = 0 For Each cell In Rng If cell.Value = cell.Offset(0, 1).Value And cell.Value <> cell.Offset(0, 2).Value Then ReDim Preserve arr(x) arr(x) = cell.Offset(0, 5) x = x + 1 End If If cell.Value <> cell.Offset(0, 1) And cell.Value = cell.Offset(0, 2) Then ReDim Preserve arr(x) arr(x) = cell.Offset(0, 5) x = x + 1 End If Next If x = 0 Then Exit Sub For Each el In arr Set c = sh2.Range("B:B").Find(el, lookat:=xlWhole) If Not c Is Nothing Then FirstAddress = c.Address Do If c.Offset(0, 1).Value = "" Then c.Offset(0, 1).Value = 1 Else c.End(xlToRight).Offset(0, 1).Value = c.End(xlToRight).Value + 1 End If Set c = sh2.Range("B:B").FindNext(c) Loop While c.Address <> FirstAddress End If Next End Sub

The logic of the macro from karmapala works on a logic suiting a slightly modified requirement description. It recognises that we have a pair of criteria resulting in having a column I value from 1.xls which needs to be looked for (matched) to a value in column B of macro.xlsm, after which the action ( to put remark 1 2 3 .. etc….. ) is taken to that row in macro.xlsm

So in Rem 2 of my version of karmapala’s macro , a one dimensional array, arr() , is made to hold those values. At the end of that code section we have this arr():
arr() of column I meeting criteria.JPG : https://imgur.com/MhEsfNm

22

25

17388

100

or
arr()={22, 25, 17388, 100}

Rem 3 then uses the .Find ( and .Find Next ) method to find the mating row in macro.xlsm , and do the action ( to put remark 1 2 3 .. etc….. ) is taken to that row in macro.xlsm

here the before
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
B

C

D

E

F

G

H

I

J

K

L

M

N

1

2

22

1

2

3

4

3

25

1

2

3

4

15083

1

5

17388

1

2

3

4

5

6

7

6

100

1

2

3

4

5

6

7

8

9

10

11

7

236

Worksheet: Sheet1 20 June excelforum

here the after
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
B

C

D

E

F

G

H

I

J

K

L

M

N

1

2

22

1

2

3

4

5

3

25

1

2

3

4

4

15083

1

5

17388

1

2

3

4

5

6

7

8

6

100

1

2

3

4

5

6

7

8

9

10

11

12

7

236

Worksheet: Sheet1 20 June excelforum

here is my version of the macro: https://excelfox.com/forum/showthrea...ll=1#post14142

The formula solution from pharmacologist

The formula solution from pharmacologist ( https://www.excelforum.com/excel-pro...ml#post5353103 )

Quote:

Originally Posted by pharmacologist

Put this in the top of column I of 1.xls and drag down and across however many columns you need.

Code:

=IF(OR(AND($D2=$E2,$D2<>$F2),AND($D2=$F2,$D2<>$E2)),IF(ISBLANK(INDEX([Macro.xlsm]Sheet1!C:C,MATCH($B2,[Macro.xlsm]Sheet1!$A:$A))),"",INDEX([Macro.xlsm]Sheet1!C:C,MATCH($B2,[Macro.xlsm]Sheet1!$A:$A))),"")

This is a nice idea, but there are a few problems.

Two minor problems:
_(i) A simple problem possibly a typo : you should not use column I of 1.xls , since that has data in. We can use any column away from the data, or we could start the formula at some convenient row down away from the data

_(ii) The formula will not work in 1.xls , since it will try to reference all the rows in a column in macro.xlsm – there are 1048576 rows in macro.xlsm, but a formula used in 1.xls will give us a problem , saying that it cannot use more than the row number for pre Excel 2007, which is 65536

Before looking further into the formula, lets simplifier/shorten/change a few names and paths to make the analysis and modification of the formula easier to follow.
I will store my files on my desktop. So the full path and file name of my files are:
C:\Users\Elston\Desktop\macro.xlsm
C:\Users\Elston\Desktop\1.xls
I will use a sheet name to help me later navigating the many duplicated / similar / cross posted questions fro the OP , Avinash. I will use
1_xlsSh1efJ20
macro_xlsmSh1efJ20 ( This is just for me to reference the workbooks as that uploaded at excelforum on June 20 ( https://www.excelforum.com/excel-pro...ml#post5352953 )
So before the use of any formula or coding, this is the situation:
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
A

B

C

D

E

F

G

H

I

J

K

L

M

N

1
Symbol

2
ACC 22 1 2 3 4

3
ADANIENT 25 1 2 3

4
ADANIPORTS 15083 1

5
ADANIPOWER 17388 1 2 3 4 5 6 7

6
AMARAJABAT 100 1 2 3 4 5 6 7 8 9 10 11

7
ASIANPAINT 236

Worksheet: macro_xlsmSh1efJ20

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

1200

1173

1194.2

1194.2

22

3
NSE ADANIENT EQ
141.4

143.7

141.4

141.05

141.05

25

4
NSE ADANIPORTS EQ
309

309

309

309.25

309.25

15083

5
NSE ADANIPOWER EQ
33.3

33.3

32.1

32.35

32.35

17388

6
NSE AMARAJABAT EQ
555

578

555

572.85

572.85

100

7
NSE ASIANPAINT EQ
1529

1529

1529

1552.95

1552.95

236

8

Worksheet: 1-Sheet1 20 June excelforum

My version of pharmacologist’s formula to suit the workbook and worksheets name is then initially:

Code:

=IF(OR(AND($D2=$E2;$D2<>$F2);AND($D2=$F2;$D2<>$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A:$A)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A:$A)));"") =IF(OR(AND($D2=$E2,$D2<>$F2),AND($D2=$F2,$D2<>$E2)),IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C,MATCH($B2,[macro.xlsm]macro_xlsmSh1efJ20!$A:$A))),"",INDEX([macro.xlsm]macro_xlsmSh1efJ20!C:C,MATCH($B2,[macro.xlsm]macro_xlsmSh1efJ20!$A:$A))),"")

Note: I have given two forms. 1 is in English Excel, the other is in my German Excel , which I have change the formula default option to English from thee default German, but which still uses the ; separator rather than the English conventional ,
If I try to use this formula, in , for example, cell K2 of 1.xls , then I get the error situation mentioned in (i) associated with the row and column count:
RowsCountProblemInFormula.JPG : https://imgur.com/KZtIJ3h

In the next posts I look at corrections/ adjustments to the formula. ( Note the output required by the OP , is that produced by the working solution from ( https://excelfox.com/forum/showthrea...ll=1#post14130
https://excelfox.com/forum/showthrea...ll=1#post14142 )
This is the solution required

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

_Row\^Col
A

B

C

D

E

F

G

H

I

J

K

L

M

N

1
Symbol

2
ACC 22 1 2 3 4 5

3
ADANIENT 25 1 2 3 4

4
ADANIPORTS 15083 1

5
ADANIPOWER 17388 1 2 3 4 5 6 7 8

6
AMARAJABAT 100 1 2 3 4 5 6 7 8 9 10 11 12

7
ASIANPAINT 236

Worksheet: macro_xlsmSh1efJ20

_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	1200	1200	1173	1194.2	1194.2	22
3	NSE	ADANIENT	EQ	141.4	143.7	141.4	141.05	141.05	25
4	NSE	ADANIPORTS	EQ	309	309	309	309.25	309.25	15083
5	NSE	ADANIPOWER	EQ	33.3	33.3	32.1	32.35	32.35	17388
6	NSE	AMARAJABAT	EQ	555	578	555	572.85	572.85	100
7	NSE	ASIANPAINT	EQ	1529	1529	1529	1552.95	1552.95	236
8

row problem , (ii)
We can overcome the row problem , (ii) , by fixing the ranges used in some parts of the formula. In the practice we might know the maximum rows, or we could estimate the largest most likely. ( If we were using VBA to put the formula in, then we could dynamically determine it ( http://www.excelfox.com/forum/showth...ll=1#post11466 Making Lr dynamic ( using rng.End(XlUp) for a single column. ) )
For simplicity of this analysis we will limit the rows to rows 2 – 7
This formula example solves the row problem , (ii)

Code:

=IF(OR(AND($D2=$E2;$D2<>$F2);AND($D2=$F2;$D2<>$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH($B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"")

Further adjustments/Corrections
The OP actually wants the results in macro.xlsm, not 1.xls. ( As the OP did not produce a before and after, it is easy to get confused…. – half the time, this OP, Avinash doesn’t know himself what he wants )
The formula is already quite flexible in that it can be placed in any cell in 1.xls. We can make it further flexible by changing the cell references for cells in 1.xls to include the workbook and first worksheet of 1.xls, ( '[1.xls]1_xlsSh1efJ20'! ) This will then make the formula work equally well in either 1.xls or macro.xlsm

Code:

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX([macro.xlsm]macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;[macro.xlsm]macro_xlsmSh1efJ20!$A$2:$A$7)));"")

For example, I put this formula in R2 of macro.xlsm and drag it across and down. Here for example just shown 4 of the formulas: ( Note Excel has removed the workbook and worksheet referrence for 1.xls - this is just a strange habit of Excel : - Excel removes the bits of the cell referrence that it does not need ... )
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
R

S

2

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"")

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"")

3

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"")

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"")

Worksheet: macro_xlsmSh1efJ20

I get for the full range giving the following results.

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

_Row\^Col
R

S

T

U

V

W

X

Y

Z

AA

AB

AC

2

1

2

3

4

3

1

2

3

4

5

1

2

3

4

5

6

7

6

1

2

3

4

5

6

7

8

9

10

11

7

Worksheet: macro_xlsmSh1efJ20

So it looks like the formula is “working” … but it is not giving us the correct results :
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
C

D

E

F

G

H

I

J

K

L

M

N

2

1

2

3

4

5

3

1

2

3

4

4

1

5

1

2

3

4

5

6

7

8

6

1

2

3

4

5

6

7

8

9

10

11

12

7

Worksheet: macro_xlsmSh1efJ20

We will investigate further in the next posts

_Row\^Col	R	S
2	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"")	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"")
3	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"")	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$E3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$F3),AND('[1.xls]1_xlsSh1efJ20'!$D3='[1.xls]1_xlsSh1efJ20'!$F3, '[1.xls]1_xlsSh1efJ20'!$D3<>'[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",INDEX(macro_xlsmSh1efJ20!D$2:D$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"")

General formula construction / relation to OP requirement
It is helpful at this stage to make an initial attempt understand the formula.

We can see that we have an “outer” If , pseudo like

If ( criteria met resulting in having a column I value from 1.xls which needs to be looked for (matched) to a value in column B of macro.xlsm ,
____Then do some stuff to get the values 1 , 2 , 3 … etc ,
_______Else "" )

Here we can see the cause of the missing 1 in row 4 . This is the situation where the original data, unchanged should be present. So replacing "" with a reference to the original data , [macro.xlsm]macro_xlsmSh1efJ20!C2, should overcome this problem.
However, we will see a small problem that across the row we will have 0s instead of nothing, since , by default, Excel is evaluating like “nothing” number, which is zero
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
R

S

T

U

4

1

0

0

0

Worksheet: macro_xlsmSh1efJ20

This can be overcome with a trick to make it show “nothing text” by adding a &"" to the cell value, [macro.xlsm]macro_xlsmSh1efJ20!C2&""

So finally we have

Code:

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));[macro.xlsm]macro_xlsmSh1efJ20!C2&"")

We have now solved the problem to put data in unchanged for the unmatched case. We need to modify somehow the first part of the outer If to get the correct results in.
We need to have values of 1 greater than the found values , and offset by one to the right

This part of the formula would normally, for the first cell return us the value of 1
INDEX(macro_xlsmSh1efJ20!C$2:C$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))
This part actually is present twice in an If, pseudo
If ( This is blank , then "" , else This )

I can see a way to do this to get the results for all but the first column, as follows: We would look for a match in the previous column and the result would be added by 1

For example, this formula…_

Code:

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2);AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2; '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));"";INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+1);C2&"")

_.. almost gives us what we want, for example in the second row
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
R

S

T

U

V

W

2

23

2

3

4

5

Worksheet: macro_xlsmSh1efJ20

One way to get the first column correct, could be to have a check on the part giving the result to see if the column first column is under consideration , in which case , we then need again to see if it is empty ( If(C2="";"";1) ) in which case a "" will be returned, else a 1

If(Column(C2)=3;If(C2="";"";1);INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+ 1)

Finally, a working formula seems to be ( For in , for example R2 and copied across and down ) ,
_____ Workbook: macro.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
R

2

=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C2&"")

Worksheet: macro_xlsmSh1efJ20

_Row\^Col	R	S	T	U
4	1	0	0	0

_Row\^Col	R	S	T	U	V	W
2	23	2	3	4	5

_Row\^Col	R
2	=IF(OR(AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$E2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$F2),AND('[1.xls]1_xlsSh1efJ20'!$D2='[1.xls]1_xlsSh1efJ20'!$F2, '[1.xls]1_xlsSh1efJ20'!$D2<>'[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C2&"")

Some other final notes to formula solution

The formula from pharmacologist seems to be matching columns A in macro.xlsm to B in 1.xls. The OP wanted columns B in macro.xlsm to I in 1.xls. But I will ignore that for now.

The final formula version of mine will also work if the workbook, 1.xls , is closed.
To verify and get the formula for that, simply close 1.xls and observe the formula changed automatically by Excel

Code:

=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2; 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2);AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2; 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2));IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7)));"";IF(COLUMN(C2)=3;IF(C2="";"";1);INDEX(macro_xlsmSh1efJ20!B$2:B$7;MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2;macro_xlsmSh1efJ20!$A$2:$A$7))+1));C2&"")

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

_Row\^Col
R

S

2

=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C2&"")

=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(D2)=3,IF(D2="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),D2&"")

3

=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C3)=3,IF(C3="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C3&"")

=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(D3)=3,IF(D3="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),D3&"")

Worksheet: macro_xlsmSh1efJ20

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

_Row\^Col	R	S
2	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C2)=3,IF(C2="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C2&"")	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F2, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D2<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E2)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(D2)=3,IF(D2="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B2,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),D2&"")
3	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(C3)=3,IF(C3="","",1),INDEX(macro_xlsmSh1efJ20!B$2:B$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),C3&"")	=IF(OR(AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3),AND('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3='C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$F3, 'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$D3<>'C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$E3)),IF(ISBLANK(INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))),"",IF(COLUMN(D3)=3,IF(D3="","",1),INDEX(macro_xlsmSh1efJ20!C$2:C$7,MATCH('C:\Users\Elston\Desktop\[1.xls]1_xlsSh1efJ20'!$B3,macro_xlsmSh1efJ20!$A$2:$A$7))+1)),D3&"")

Spare post for later use

The next version.....

Code:

Sub test() ' https://www.excelforum.com/excel-programming-vba-macros/1321581-put-data-in-series-by-vba.html#post5363303 Dim r As Range, x, BK As String, myVal BK = "'" & ThisWorkbook.Path & "\[1(sample).xls]1-Sheet1'!" With Sheets("sheet1") For Each r In .Range("b2", .Range("b" & Rows.Count).End(xlUp)) myVal = r.Value If Not IsNumeric(myVal) Then myVal = Chr(34) & myVal & Chr(34) x = ExecuteExcel4Macro("match(" & r.Value & "," & BK & "r1c9:r10000c9,0)") If IsNumeric(x) Then With .Cells(r.Row, Columns.Count).End(xlToLeft) .Cells(1, 2) = .Value + 1 End With End If Next End With End Sub

_... to be looked at later
( Note Yasser found it intersting ... https://eileenslounge.com/viewtopic.php?f=30&t=34952 )

Share '1(sample) excelforum 11july.xls' : https://app.box.com/s/q44t3dglvoq59ozvyi4c411ekw0aox9s
Share 'H2(SAMPLE) excelforum 11July.xlsb' : https://app.box.com/s/ziudkeb2bdmdosxhcrcx6g243sjn7n7x

Spare post for later use

if you can do it, Akan. so can I .... a spare post, i think I might need later
:)
Molly
x

Macro Modification

Hello, Need help regarding the macro

Code:

Sub STEP29() Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv") Set wb2 = ThisWorkbook Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1) Dim arr1() As Variant, arr2() As Variant, arr3() As Variant Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value Let arr2() = Ws2.Range("A1").CurrentRegion.Value ReDim arr3(0 To UBound(arr2(), 1)) Dim Cnt For Cnt = 2 To UBound(arr2(), 1) Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2 Dim mtchRes As Variant Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0) If IsError(mtchRes) Then ' a match was not found, so we do not need to remove the 1 2 3 etc... Else ' a match was found, so we need to remove the 1 2 3 etc... Dim Empt As Long For Empt = 3 To UBound(arr3(Cnt - 1), 2) Let arr3(Cnt - 1)(1, Empt) = "" Next Empt End If '3c) Paste out row Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1) Next Cnt wb1.Close wb2.Save End Sub Public Function CL(ByVal lclm As Long) As String Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0 End Function

Actually i converted that Alert..csv file to Alert..xls
So I need the changes in the macro according to that
So Plz have a look and help me in solving this problem Sir

Hi,
I am not quite sure what you are asking…
( Your macro above seems similar to some which I did for you ( https://excelfox.com/forum/showthrea...mark-1-2-3-etc ) )

Probably all you would need to do is use the File name of the File that you are using
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..xls")
But that is obvious. So possibly you are trying to ask something else.

( Of course, if you have been .Opening .csv text files with an Excel object, as in your macro above in post #1, ( https://excelfox.com/forum/showthrea...ll=1#post14144 ) then you may have got sometimes problems.
But I am not going to make any attempt to explain that to you, since I can see from your recent posts here and elsewhere that many people have tried very hard to explain that all to you, and you still don’t seem to have understood anything !!! )
Molly

Sorry let me explain once again Molly Mam

This macro is made for alert..csv file

Code:

Sub STEP29() Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv") Set wb2 = ThisWorkbook Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1) Dim arr1() As Variant, arr2() As Variant, arr3() As Variant Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value Let arr2() = Ws2.Range("A1").CurrentRegion.Value ReDim arr3(0 To UBound(arr2(), 1)) Dim Cnt For Cnt = 2 To UBound(arr2(), 1) Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2 Dim mtchRes As Variant Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0) If IsError(mtchRes) Then ' a match was not found, so we do not need to remove the 1 2 3 etc... Else ' a match was found, so we need to remove the 1 2 3 etc... Dim Empt As Long For Empt = 3 To UBound(arr3(Cnt - 1), 2) Let arr3(Cnt - 1)(1, Empt) = "" Next Empt End If '3c) Paste out row Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1) Next Cnt wb1.Close wb2.Save End Sub Public Function CL(ByVal lclm As Long) As String Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0 End Function

But now alert..csv file is replaced with alert.xls
so the macro would be

Code:

Sub STEP29() Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert.xls") Set wb2 = ThisWorkbook Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1) Dim arr1() As Variant, arr2() As Variant, arr3() As Variant Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value Let arr2() = Ws2.Range("A1").CurrentRegion.Value ReDim arr3(0 To UBound(arr2(), 1)) Dim Cnt For Cnt = 2 To UBound(arr2(), 1) Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2 Dim mtchRes As Variant Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0) If IsError(mtchRes) Then ' a match was not found, so we do not need to remove the 1 2 3 etc... Else ' a match was found, so we need to remove the 1 2 3 etc... Dim Empt As Long For Empt = 3 To UBound(arr3(Cnt - 1), 2) Let arr3(Cnt - 1)(1, Empt) = "" Next Empt End If '3c) Paste out row Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1) Next Cnt wb1.Close wb2.Save End Sub Public Function CL(ByVal lclm As Long) As String Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0 End Function

Any more changes is required in this code then plz let me know Molly Mam

I think I understood already what you are asking.
I think you are still confused and mixed up because you have still not understood anything about the difference between Excel files and text files.
You sometimes tell us that you understand. But all your questions suggest to us that you still have not understood anything at all.

If I try to explain again, you will probably just get more confused or, more likely, you will not read or understand anything I tell you. So it will just waste all our time again.

Quote:

Originally Posted by fixer

...This macro is made for alert..csv file...

Are you sure?
I do not think so.
Who made the macro for alert..csv file?

I did not make any macro for you to work on a text ( .csv ) file.
Who made this macro for you?
It looks very similar to macros which I made for you. But all the macros I made for you were for Excel files. I never made any macros for you to work with text files

If you are lucky,

Code:

Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert.xls")

may sometimes do the same as what you want

Code:

Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")

to do.

So if you are lucky, that will be the only change that you need.

But it is very dangerous and stupid to ever do
Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
( That code line is using the Excel object to .Open a text file. Excel is not really supposed to do that. Excel is mostly supposed to open Excel files. But Excel will allow you to try to open a text file. Excel will try to guess what to do. It may try to do what you want. Sometimes it may do different things. Many people have tried very hard to explain that in detail to you for 2 months already).

That code line may sometimes do what you want. But there will probably always be problems.
You seem to have decided not to want to know anything about such problems. So it may be impossible for anyone to really help you anymore.

Remember one last very important thing:
You are not the only person that understands nothing about the difference between Excel files and text files.
Lots of people understand nothing about the difference between Excel files and text files.

So if you continually cross post and ask the same question at different places, you will eventually find someone that will say to you.
…Yes, that is the only change needed .
So you will be happy then. But it is the wrong answer.

But that is your choice. You should do what you want to. You prefer a simple short answer, even if it is the wrong answer.

It is nice to have different people in this world. Stay as you want to be. (You will never achieve your aim, unless your aim is to fail and get the wrong results sometimes).
But you should stay as you want to be. The world would be very boring if we all were the same.
:)

Good luck in your future failures!

Molly

I made a Mistake & I corrected My Mistake
I am not working with any .csv file
& In Future I will never Ask any question related to .csv file
Thnx Alot Molly Mam Doubts & Problem both are solved

Thnx Alot For Providing the Great Info Doc Sir & Molly Mam
Doc Sir Chill
Every Code is working Perfectly
I have no doubts in any Macro

Macro Correction

Moderator Notice: This and the next 4 posts were posted here in the wrong place. I have left the copies here since the linked post has been referrenced already. I have also copied the posts to the correct place:
https://excelfox.com/forum/showthrea...ll=1#post14580

Code:

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.xlsx") 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....271443#p271443
https://chandoo.org/forum/threads/vb...rection.44637/

Moderator Notice: This post was posted here in the wrong place. I have left the copy here since the linked post has been referrenced already. I have also copied this and the related reply posts to the correct place, starting here:
https://excelfox.com/forum/showthrea...ll=1#post14580

You almost had it right here: https://eileenslounge.com/viewtopic....6a1745#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

Code:

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

Code:

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/showthrea...4565#post14565
Your macro , Sub STEP6() from here
https://excelfox.com/forum/showthrea...ll=1#post14562
and here
https://eileenslounge.com/viewtopic....271385#p271385
has the wrong Lr2

Moderator Notice: This post was posted here in the wrong place. I have left the copy here since the linked post has been referrenced already. I have also copied this and the related reply posts to the correct place, starting here:
https://excelfox.com/forum/showthrea...ll=1#post14580

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

Moderator Notice: This post was posted here in the wrong place. I have left the copy here since the linked post has been referrenced already. I have also copied this and the related reply posts to the correct place, starting here:
https://excelfox.com/forum/showthrea...ll=1#post14580

No Doc Sir this problem is not solved yet
i am sending the sample file with output in next 10 min

Quote:

Originally Posted by fixer

.....

No Doc Sir this problem is not solved yet
i am sending the sample file with output in next 10 min

See here https://excelfox.com/forum/showthrea...ll=1#post14584
( Question moved to here: https://excelfox.com/forum/showthrea...ll=1#post14578 )

Test post for later use, to get url now

5 Attachment(s)

Text Files , Excel Files , Excel VBA Array

In Avinash’s world are two types of files. Only two types , Text files and Excel files. There are no other types of file. There will probably never be any other types of files.

All Files , text files, excel files and all other file types , are held in a computer as just a long string of Text. Even an Excel File is just a long piece of text inside a computer. But it is hard to understand. The software that is Excel decodes the long text and tries to make it appear in values and formats that we can see in a worksheet

Excel Files ( Excel Worksheet spreadsheet )
.xls .xlsm .xlsx .xlsb
An Excel file is very complicated. It can have values and lots of cell formatting.
Because of all cell formatting, it can be very slow in working. Excel is not an efficient thing to use if you only have values

We can open an Excel File manually, using Excel or Notepad
ExcelFileOpenInNotepad.JPG : https://imgur.com/bdym9Lc ExcelFileOpenInExcel.JPG : https://imgur.com/gwOtksS
Attachment 3298 Attachment 3296
In Excel it may looks like this
ExcelFile.JPG : https://imgur.com/8xaZihR
Attachment 3297
_____ Workbook: ExcelFile.xlsx ( Using Excel 2007 32 bit )

_Row\^Col A B C

1 A B

2 C D

3

Worksheet: Sheet1

In Notepad it looks like this:
ExcelFileInNotepad.JPG : https://imgur.com/wHTPbO6
Attachment 3295

Code:

PK ! U6»+w ( Ø [Content_Types].xml ¢Ô ( ÌTËNÃ0 ¼#ñ ‘¯(q[ B¨i <ŽP ø co «ŽmyÝÒþ=›¤* Ñ¨¥=pI %;3žÌp¼¬L²€€ÚÙœõ³ KÀJ§´æìíõ1½a Fa•0ÎBÎV€l<:? ¾®<`BÓ sVÆèo9GYB%0s ,½)\¨D¤Ç0å^È™˜ -ôz×\: ÁÆ4Ö l4|& A+H&"Ä'Q _ Úk?#<–Üµƒ5wÎ„÷FK I9_Xõƒ5uE¡%('ç qe ØEÂw b\ À£©Ð K€X™¬ Ý0ßC!æ&& Kr 5=€ÁÃŽ¶63£ÉæøXj ÝÞu{òáÂìÝ¹Ù©]©ÝÉ*¡íF÷Ž Ôü•È›ÛÕ‰…lñ»tPŠ&Áyä”¹£ù¡^½ •z‚„ 5lw•§ƒ£5|è^ Ð ¹ýn½‹Ë¢ãøvø›¬XŠ ê% jË“×ÆWì½²)]€Ã ²é’zú—Dò¦çGŸ ÿÿ PK ! µU0#õ L Î _rels/.rels ¢Ê (

That text above of a simple Excel File is very complicated because it has all the information needed by Excel to make all the cells and formatting

Making an Excel File with Excel VBA
We can make that Excel File using Excel VBA

Code:

Sub MakeExcelFile() Dim Wb As Workbook, Ws As Worksheet Workbooks.Add Set Wb = ActiveWorkbook Set Ws = Wb.Worksheets.Item(1) Let Ws.Range("A1") = "A": Let Ws.Range("B1") = "B" Let Ws.Range("A2") = "C": Let Ws.Range("B2") = "D" Wb.SaveAs Filename:=ThisWorkbook.Path & "\ExcelFile.xlsx" End Sub

We usually open Excel files with Excel. So that is why the files with the extensions of .xls .xlsm .xlsx .xlsb are called Excel Files. Such files were designed to be opened in Excel

If we are using an Excel file to store simple data values, then the values are usually divided up so that when opened in Excel the data is shown in cells in rows and columns

Text Files
.csv .txt
Text files are very simple. They only have values and sometimes , if it is being used to store data values, it may have separators**. ( Sometimes we call the seperators delimiters ).

A B
C D

A,B
C,D

We usually open text files with a text editor. For example Notepad.
TextFileOpenInNotepad.JPG : https://imgur.com/zzRAVIN
Attachment 3299
Because Text files are not complicated, we can see them easily in Notepad. Because we sometimes open files with the extension of .csv .txt in Notepad , they may be called Notepad files, and sometimes files with the extension of .csv may be called a comma separated values text values file or “csv file”. But really they are both Text files

**If we want to store simple data values in a text file, then we have the problem that we have no way to make the data appear in cells, because a text file has no cell information and also no other formatting information.
So we typically separate data on a line with something like, _ ; , vbTab | _ etc…
A Line is separated from the next line by an “invisible” character which tells a computer to make a new line, for example
vbCr & vbLf

Make a Text file using Excel VBA
We can make a text file using Excel VBA

Code:

Sub MakeTextFile() Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Open ThisWorkbook.Path & "\" & "TextFile.txt" For Output As #FileNum ' Will be made if not there Print #FileNum, "A" & vbTab & "B" & vbCr & vbLf & "C" & vbTab & "D" Close #FileNum End Sub

We can try to .Open a text file in Excel. Excel will try to show us the values from it in cells. It may work. It may not work. There will always be problems.
But we may want to see the values in Excel
Because there are always problems .Opening a text file in Excel, we import the values into an Excel worksheet instead
The text file made in that last macro , TextFile.txt , can be seen in Notepad to look like this:
TextFile_txtInNotepad.JPG : https://imgur.com/0B2BQpK

Code:

A B C D

( We can represent that file as a simple string in VBA coding, thus:
"A" & vbTab & "B" & vbCr & vbLf & "C" & vbTab & "D"
( We might sometimes call this a Tab separated values or Tab delimited values text file ) )
The following macro is the best way to put the values from that text file into a worksheet. This may typically be called importing a text file into Excel. It does not convert a text file to an Excel File.

Code:

Sub ImportTextFileValuesIntoExcelWorksheet() Rem 1 Add a workbook to display the values from a Tab delimited values text file Dim Wb As Workbook, Ws As Worksheet Workbooks.Add Set Wb = ActiveWorkbook Set Ws = Wb.Worksheets.Item(1) Rem 2 Put the entire text file into a single string, TotalFile Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function Dim PathAndFileName As String, TotalFile As String Let PathAndFileName = ThisWorkbook.Path & "\" & "TextFile.txt" ' Open PathAndFileName For Binary As #FileNum ' Open Route to data. Binary is a fundemental type data input... TotalFile = Space(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length Get #FileNum, , TotalFile Close #FileNum ' I need to do this, or there may be problems with my computer as I have an open route which may interact badly with something else If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2) ' Sometimes an extra line seperator gets added, so I remove it if that is the case Rem 3 Loop through the lines of the text file and paste each line to a row in the worksheet Dim Rws() As String ' I want to get an array of all my rows Let Rws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare) ' I Split by the row seperator which is usualy vbCr & vbLf but note that it might sometimes be something else Dim Cnt As Long ' I wat to loop for all the lines in the text file For Cnt = 1 To UBound(Rws()) + 1 ' I need to use +1 because the one dimensional array returned by Split starts at 0 Dim Clms() As String ' I want an array of just the values. I can easily achieve this by spliting by the value seperator Let Clms() = Split(Rws(Cnt - 1), vbTab, -1, vbBinaryCompare) ' I now split the row into columns using the value seperator, which in this case is vbTab Let Ws.Range("A" & Cnt & "").Resize(1, UBound(Clms()) + 1).Value = Clms() ' I can assign my 1 dimensional array to a worksheet range, and Excel will accept it conventionally as a row of data Next Cnt End Sub

It is better to use text files and manipulate text files with Excel VBA if we are only looking at values

VBA Arrays
It is better to use text files and manipulate text files with Excel VBA if we are only looking at values.
Excel is very slow and inefficient if we are only looking at values
But we can make Macros for Excel using Excel VBA a little better if we use VBA arrays.
Instead of putting values in an Excel worksheet, one value at a time, we can put all values into an array, and then at the end of the macro we can put all the values into the worksheet in one go. This makes the macro quicker

Code:

Sub MakeExcelFileUsingVBAArrays() Dim Wb As Workbook, Ws As Worksheet Workbooks.Add Set Wb = ActiveWorkbook Set Ws = Wb.Worksheets.Item(1) ' Make array Dim arr1(1 To 2, 1 To 2) As String Let arr1(1, 1) = "A": Let arr1(1, 2) = "B" Let arr1(2, 1) = "C": Let arr1(2, 2) = "D" ' Paste entire array into worksheet in one go Let Ws.Range("A1:B2").Value = arr1() Wb.SaveAs Filename:=ThisWorkbook.Path & "\ExcelFileMadeUsingVBAArrays.xlsx" End Sub

The array, arr1() , can be considered to look like this:

1

2

1

A

B

2

C

D

But we cannot easily see this array, as it is just inside the computer in memory. But we can paste the array into a worksheet in one go using a code line like:
Let1 Ws.Range("A1:B2").Value = arr1()

For Avinash it is better to use as much manipulation of text files using VBA and VBA arrays as possible

You must not learn any VBA coding if you do not want to.
But you must try to understand the difference in text files and excel files
If you cannot or will not learn this, then there is no point in anyone trying to help you further. You will get nowhere. You will waste everybody’s time, including your own

_Row\^Col	A	B
1	A	B
2	C	D
3

Here’s a few of the identical cross posts on a new question.
there are 2 files 1.xls & 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 plz see the sample file
Condition: If column D of 1.xls matches with column E of 1.xls Or If column D of 1.xls matches with column F of 1.xls then match column I of 1.xls with column B of Macro.xlsm & if it matches then clear all the data in that row from column C
Or
If column D of 1.xls does not matches with column E of 1.xls & with Column F of 1.xls too.. then put the remarks in series (numerical series)
https://www.mrexcel.com/board/thread...y-vba.1141539/
https://www.excelguru.ca/forums/show...bers-in-series
https://www.myonlinetraininghub.com/...bers-in-series
https://www.experts-exchange.com/que...es-by-vba.html
https://www.excelforum.com/excel-pro...ml#post5371511 ( Closed by Jeff in error )
http://www.eileenslounge.com/viewtopic.php?f=30&t=35053

posting to get the URL for later use

bmmnbmnb

hlkhkhkh

Old one fucked up by Avinash’s description mess up
https://www.excelforum.com/excel-pro...ml#post5370608
there are 2 files 1.xls & macro.xlsm (macro will be placed in macro.xlsm),both files are loacted in diffrent 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
plz see the sample file
Condition:
If column D of 1.xls equal to column E of 1.xls & column H of 1.xls is lower than column K then match column I of 1.xls with column B of macro.xlsm & if it matches then clear the data in that row ELSE put the remark
If column D of 1.xls equal to column F of 1.xls & column H of 1.xls is greater than column K then match column I of 1.xls with column B of macro.xlsm & if it matches then clear the data in that row ELSE put the remark

Using new ( old ) account "Zaeguzah "
https://www.excelforum.com/excel-pro...-the-data.html

Hello Guys,
Looking for a macro

If column B of 1.xls matches with column A of Book1.xlsb & the difference between Column D of 1.xls & Column E of 1.xls is not greater than 0.50% of column D of 1.xls then put Remarks Else Clear the data from Column B of book1.xlsb

Note:
Macro will be placed in a book1.xlsb
Sheet name can be anything
Remarks means If condition met then finding the last row of book1.xlsb and add +1 to the data in the last row & put th result in next cell in that row
clear data means if condition doesnt met then clear all the data in book1.xlsb in that row
Plz see the output (it is the output of the macro)

hlkhlhh

2 Attachment(s)

Match data between sheet & pasting the output by adding +1

Hi,
I am looking for a macro that will do the things mentioned below

If column E of sheet3 of 3.xlsm matches with sheet4 of Column B of 10.xlsx then match the same with column B of sheet1 of 3.xlsm & if matches then put the numerical data in series by vba in sheet1 of 3.xlsm

Macro will be placed in 3.xlsm file

Thnx Alot
https://www.excelforum.com/excel-pro...dding-1-a.html

Probelm Solved
Thnx Alot

Quote:

Originally Posted by fixer

Probelm Solved..

You forgot ( again ) to tell us all how it was solved it and where you got the solution...