Vishaal
Well-known Member
- Joined
- Mar 16, 2019
- Messages
- 533
- Office Version
- 2010
- 2007
- Platform
- Windows
- Web
Hi all,
We are using the following VBA code
for the following sheets
Excel 2010 32 bit
Second sheet for compare
Excel 2010 32 bit
Result Sheet
Excel 2010 32 bit
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
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
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | S.No. | Age Group | Danial | Chrix | Rocky | Ricky | | Danial | Chrix | Rocky | Ricky |
2 | | | Ronaldo | Pamela | Donald | messy | | Ronaldo | Pamela | Donald | messy |
3 | | | | | | | | | | | |
4 | 1 | 20 | Yes | Na | Yes | Na | | | | | |
5 | 2 | 30 | Na | Na | Na | Na | | | | | |
6 | 3 | 40 | Na | Na | Na | Na | | 2 | 3 | | |
7 | 4 | 50 | Yes | Yes | Na | Na | | | | | 4 |
8 | 5 | 60 | Yes | Na | Na | Yes | | | 1 | | |
9 | 6 | 70 | Na | Yes | Na | Yes | | 1 | | 5 | |
Sheet: Sheet1 |
Second sheet for compare
Excel 2010 32 bit
A | B | C | D | E | |
---|---|---|---|---|---|
1 | S.No. | Danial | Chrix | Rocky | Ricky |
2 | | Ronaldo | Pamela | Donald | messy |
3 | | | | | |
4 | 1 | | | | |
5 | 2 | | | | |
6 | 3 | | | Yes | |
7 | 4 | Yes | | | Yes |
8 | 5 | | Yes | Yes | |
9 | 6 | | | | |
Sheet: Sheet2 |
Result Sheet
Excel 2010 32 bit
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | S.No. | Age Group | Danial | Chrix | Rocky | Ricky | | Danial | Chrix | Rocky | Ricky |
2 | | | Ronaldo | Pamela | Donald | messy | | Ronaldo | Pamela | Donald | messy |
3 | | | | | | | | | | | |
4 | 1 | 20 | Yes | Na | Yes | Na | | | | | |
5 | 2 | 30 | Na | Na | Na | Na | | | | | |
6 | 3 | 40 | Na | Na | Na | Na | | 2 | 3 | | |
7 | 4 | 50 | Yes | Yes | Na | Na | | | | | 4 |
8 | 5 | 60 | Yes | Na | Na | Yes | | | 1 | | |
9 | 6 | 70 | Na | Yes | Na | Yes | | 1 | | 5 | |
10 | | | | | | | | | | | |
11 | | | | | | | | | | | |
12 | | | | | | | | | | 5 | 4 |
13 | | | | | | | | | | Rocky | Ricky |
14 | | | | | | | | | | Donald | messy |
Sheet: Sheet1 |
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
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | S.No. | Age Group | Danial | Chrix | Rocky | Ricky | | Danial | Chrix | Rocky | Ricky |
2 | | | Ronaldo | Pamela | Donald | messy | | Ronaldo | Pamela | Donald | messy |
3 | | | shika | rghu | randy | john | | shika | rghu | randy | john |
4 | | | lovely | rick | flair | prave | | lovely | rick | flair | prave |
5 | | | rinku | mone | rashmi | peter | | rinku | mone | rashmi | peter |
6 | | | | | | | | | | | |
7 | 1 | 20 | Yes | Na | Yes | Na | | | | | |
8 | 2 | 30 | Na | Na | Na | Na | | | | | |
9 | 3 | 40 | Na | Na | Na | Na | | 2 | 3 | | |
10 | 4 | 50 | Yes | Yes | Na | Na | | | | | 4 |
11 | 5 | 60 | Yes | Na | Na | Yes | | | 1 | | |
12 | 6 | 70 | Na | Yes | Na | Yes | | 1 | | 5 | |
Sheet: Sheet1 |