VBA CODE EDITING - Search and Match and show the result

Vishaal · Oct 14, 2019

Hi all,

We are using the following VBA code

Code:

Sub Search_and_Match()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr As Long, lc As Long, col As Long, lr2 As Long
  Dim c As Range, f As Range, r As Range
  '
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  Set r = sh1.Range("H4", sh1.Cells(lr, lc))
  r.Offset(r.Rows.Count + 2).ClearContents
  For Each c In r
    If c.Value <> "" Then
      col = c.Column - r.Cells(1, 1).Column + 1
      Set f = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
      If Not f Is Nothing Then
        If f.Offset(, col).Value = "Yes" Then
          sh1.Cells(lr + 3, c.Column).Resize(2).Value = sh1.Cells(1, c.Column).Resize(2).Value
          lr2 = sh1.Cells(Rows.Count, c.Column).End(xlUp).Row + 1
          sh1.Cells(lr2, c.Column).Value = c.Value
        End If
      End If
    End If
  Next
End Sub

for the following sheets

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

[/th][th]

K

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

S.No.

[/td][td]

Age Group

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

1

[/td][td]

20

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Yes

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

2

[/td][td]

30

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

3

[/td][td]

40

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

2

[/td][td]

3

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

4

[/td][td]

50

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Na

[/td][td]

4

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

5

[/td][td]

60

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Na

[/td][td]

Yes

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

6

[/td][td]

70

[/td][td]

Na

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Yes

[/td][td]

1

[/td][td]

5

[/td][td]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Second sheet for compare

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

S.No.

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

2

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

3

[/td][td]

Yes

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

4

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

5

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

6

[/td][td]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

Result Sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

[/th][th]

K

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

S.No.

[/td][td]

Age Group

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

1

[/td][td]

20

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Yes

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

2

[/td][td]

30

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

3

[/td][td]

40

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

2

[/td][td]

3

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

4

[/td][td]

50

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Na

[/td][td]

4

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

5

[/td][td]

60

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Na

[/td][td]

Yes

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

6

[/td][td]

70

[/td][td]

Na

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Yes

[/td][td]

1

[/td][td]

5

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

5

[/td][td]

4

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]

Rocky

[/td][td]

Ricky

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]

Donald

[/td][td]

messy

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

now my query is if we add three more rows data then for the result what modification required and in result sheet, result are starting from "row 12", can we start it after five/six/seven row from last filled coloumn

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

[/th][th]

K

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

S.No.

[/td][td]

Age Group

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

shika

[/td][td]

rghu

[/td][td]

randy

[/td][td]

john

[/td][td]

shika

[/td][td]

rghu

[/td][td]

randy

[/td][td]

john

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

lovely

[/td][td]

rick

[/td][td]

flair

[/td][td]

prave

[/td][td]

lovely

[/td][td]

rick

[/td][td]

flair

[/td][td]

prave

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

rinku

[/td][td]

mone

[/td][td]

rashmi

[/td][td]

peter

[/td][td]

rinku

[/td][td]

mone

[/td][td]

rashmi

[/td][td]

peter

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

1

[/td][td]

20

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Yes

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

2

[/td][td]

30

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

3

[/td][td]

40

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

2

[/td][td]

3

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

4

[/td][td]

50

[/td][td]

Yes

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Na

[/td][td]

4

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

5

[/td][td]

60

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Na

[/td][td]

Yes

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

6

[/td][td]

70

[/td][td]

Na

[/td][td]

Yes

[/td][td]

Na

[/td][td]

Yes

[/td][td]

1

[/td][td]

5

[/td][td]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Vishaal · Oct 20, 2019

Help Pls

DanteAmor · Oct 20, 2019

Try this

Code:

Sub Search_and_Match()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr As Long, lc As Long, col As Long, lr2 As Long
  Dim c As Range, f As Range, r As Range
  '
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  Set r = sh1.Range("H[COLOR=#ff0000][B]7[/B][/COLOR]", sh1.Cells(lr, lc))
  r.Offset(r.Rows.Count + 2).ClearContents
  For Each c In r
    If c.Value <> "" Then
      col = c.Column - r.Cells(1, 1).Column + 1
      Set f = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
      If Not f Is Nothing Then
        If f.Offset(, col).Value = "Yes" Then
          sh1.Cells(lr + 3, c.Column).Resize(2).Value = sh1.Cells(1, c.Column).Resize(2).Value
          lr2 = sh1.Cells(Rows.Count, c.Column).End(xlUp).Row + 1
          sh1.Cells(lr2, c.Column).Value = c.Value
        End If
      End If
    End If
  Next
End Sub

Try and tell me. If the result is different, then you can put the expected result.

Vishaal · Oct 20, 2019

Sure sir,
Can u pls confirm that code provided by u work for only A1:K9 or we can use it for bulk data

Vishaal · Oct 20, 2019

Thanks sir for the solution, pls check the details

First Sheet
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

[/th][th]

K

[/th][th]

L

[/th][th]

M

[/th][th]

N

[/th][th]

O

[/th][th]

P

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][td]

log

[/td][td]

ghun

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][td]

log

[/td][td]

ghun

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][td]

sanju

[/td][td]

safi

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][td]

sanju

[/td][td]

safi

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

shika

[/td][td]

rghu

[/td][td]

randy

[/td][td]

john

[/td][td]

vijju

[/td][td]

rodi

[/td][td]

shika

[/td][td]

rghu

[/td][td]

randy

[/td][td]

john

[/td][td]

vijju

[/td][td]

rodi

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

lovely

[/td][td]

rick

[/td][td]

flair

[/td][td]

prave

[/td][td]

archi

[/td][td]

tina

[/td][td]

lovely

[/td][td]

rick

[/td][td]

flair

[/td][td]

prave

[/td][td]

archi

[/td][td]

tina

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

rinku

[/td][td]

mone

[/td][td]

rashmi

[/td][td]

peter

[/td][td]

novit

[/td][td]

emli

[/td][td]

rinku

[/td][td]

mone

[/td][td]

rashmi

[/td][td]

peter

[/td][td]

novit

[/td][td]

emli

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

2

[/td][td]

17000

[/td][td]

700

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

1

[/td][td]

1

[/td][td]

1

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

3

[/td][td]

83000

[/td][td]

300

[/td][td]

Na

[/td][td]

Na

[/td][td]

2

[/td][td]

2

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

4

[/td][td]

92000

[/td][td]

200

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

5

[/td][td]

42000

[/td][td]

200

[/td][td]

Na

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]

6

[/td][td]

69000

[/td][td]

900

[/td][td]

Na

[/td][td]

Na

[/td][td]

1

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]

7

[/td][td]

93000

[/td][td]

300

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15

[/td][td]

8

[/td][td]

31000

[/td][td]

100

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16

[/td][td]

9

[/td][td]

56000

[/td][td]

600

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17

[/td][td]

10

[/td][td]

58000

[/td][td]

800

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

2

[/td][td]

4

[/td][td]

3

[/td][td]

3

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Comparison Sheet
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][td]

log

[/td][td]

ghun

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][td]

sanju

[/td][td]

safi

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

shika

[/td][td]

rghu

[/td][td]

randy

[/td][td]

john

[/td][td]

vijju

[/td][td]

rodi

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

lovely

[/td][td]

rick

[/td][td]

flair

[/td][td]

prave

[/td][td]

archi

[/td][td]

tina

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

rinku

[/td][td]

mone

[/td][td]

rashmi

[/td][td]

peter

[/td][td]

novit

[/td][td]

emli

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

2

[/td][td]

Yes

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

3

[/td][td]

Yes

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

4

[/td][td]

Yes

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

5

[/td][td]

Yes

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]

6

[/td][td]

Yes

[/td][td]

Yes

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]

7

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15

[/td][td]

8

[/td][td]

Yes

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16

[/td][td]

9

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17

[/td][td]

10

[/td][td]

Yes

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18

[/td][td]

11

[/td][td]

Yes

[/td][td]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

Result Sheet
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

[/th][th]

K

[/th][th]

L

[/th][th]

M

[/th][th]

N

[/th][th]

O

[/th][th]

P

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][td]

log

[/td][td]

ghun

[/td][td]

Danial

[/td][td]

Chrix

[/td][td]

Rocky

[/td][td]

Ricky

[/td][td]

log

[/td][td]

ghun

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][td]

sanju

[/td][td]

safi

[/td][td]

Ronaldo

[/td][td]

Pamela

[/td][td]

Donald

[/td][td]

messy

[/td][td]

sanju

[/td][td]

safi

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]

shika

[/td][td]

rghu

[/td][td]

randy

[/td][td]

john

[/td][td]

vijju

[/td][td]

rodi

[/td][td]

shika

[/td][td]

rghu

[/td][td]

randy

[/td][td]

john

[/td][td]

vijju

[/td][td]

rodi

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]

lovely

[/td][td]

rick

[/td][td]

flair

[/td][td]

prave

[/td][td]

archi

[/td][td]

tina

[/td][td]

lovely

[/td][td]

rick

[/td][td]

flair

[/td][td]

prave

[/td][td]

archi

[/td][td]

tina

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

rinku

[/td][td]

mone

[/td][td]

rashmi

[/td][td]

peter

[/td][td]

novit

[/td][td]

emli

[/td][td]

rinku

[/td][td]

mone

[/td][td]

rashmi

[/td][td]

peter

[/td][td]

novit

[/td][td]

emli

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]

2

[/td][td]

17000

[/td][td]

700

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

1

[/td][td]

1

[/td][td]

1

[/td][td]

1

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]

3

[/td][td]

83000

[/td][td]

300

[/td][td]

Na

[/td][td]

Na

[/td][td]

2

[/td][td]

2

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]

4

[/td][td]

92000

[/td][td]

200

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]

5

[/td][td]

42000

[/td][td]

200

[/td][td]

Na

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]

6

[/td][td]

69000

[/td][td]

900

[/td][td]

Na

[/td][td]

Na

[/td][td]

1

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]

7

[/td][td]

93000

[/td][td]

300

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15

[/td][td]

8

[/td][td]

31000

[/td][td]

100

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16

[/td][td]

9

[/td][td]

56000

[/td][td]

600

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

1

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17

[/td][td]

10

[/td][td]

58000

[/td][td]

800

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

Na

[/td][td]

2

[/td][td]

4

[/td][td]

3

[/td][td]

3

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19

[/td][td]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20

[/td][td]

4

[/td][td]

3

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21

[/td][td]

Chrix

[/td][td]

ghun

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22

[/td][td]

Pamela

[/td][td]

safi

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23

[/td][td]

rghu

[/td][td]

rodi

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24

[/td][td]

rick

[/td][td]

tina

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25

[/td][td]

mone

[/td][td]

emli

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

we have added some data but we have approx total rows 60 and column till IB

result always show after last row (gaping approx 4 to 5 row from last filled row)

in this sheet we will check only last filled row data i mean K17:P17 (For above sheet) to sheet 2
when we add data in K18:P18, code will check only K18:P18 now it will not check K17:P17

all the points are same as previous question, only added the above point

DanteAmor · Oct 21, 2019

Sorry, but I am not understanding how the data is growing.
Try the following, I hope it helps.

Code:

Sub Search_and_Match()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr As Long, lc As Long, col As Long, lr2 As Long
  Dim c As Range, f As Range, r As Range
  '
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  Set r = sh1.Range("H7", sh1.Cells(lr, lc))
  r.Offset(r.Rows.Count + 2).ClearContents
  For Each c In r
    If c.Value <> "" Then
      col = c.Column - r.Cells(1, 1).Column + 1
      Set f = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
      If Not f Is Nothing Then
        If f.Offset(, col).Value = "Yes" Then
          sh1.Cells(lr + 3, c.Column).Resize([B][COLOR=#FF0000]5[/COLOR][/B]).Value = sh1.Cells(1, c.Column).Resize([B][COLOR=#ff0000]5[/COLOR][/B]).Value
          lr2 = sh1.Cells(Rows.Count, c.Column).End(xlUp).Row + 1
          sh1.Cells(lr2, c.Column).Value = c.Value
        End If
      End If
    End If
  Next
End Sub

Vishaal · Oct 21, 2019

Thanks Dante Amor Ji

For your help and providing a very good code

Its make my work easy

Thanks

DanteAmor · Oct 21, 2019

I am really glad to know that it works for you. thanks for the feedback.

VBA CODE EDITING - Search and Match and show the result

Vishaal

Well-known Member

Excel Facts

Vishaal

Well-known Member

DanteAmor

Well-known Member

Vishaal

Well-known Member

Vishaal

Well-known Member

DanteAmor

Well-known Member

Vishaal

Well-known Member

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

VBA CODE EDITING - Search and Match and show the result

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock