Closed

Excel VBA - Match, Update, Add - Part II - repost 2

The code below compares data in 2 columns from 2 tabs in excel. If the data from 'Compare' tabs is not found in 'Data' tab, it goes down to row 3665 and writes the record there. Rather than having line 3665 hard-coded, I need the code to find the last row in the array in the ‘Data’ tab, and then move down 10 rows to start populating any records that were not found.

Beside this addition, I want to keep the rest of the code unchanged as it works very well, other than this necessary modification. I know this will be a relatively minor change. Thanks in advance FreeLancers.

'‘************Find the last element in B and C column

RC2 = Sheets("Data").Range("B40000").End(xlUp).Row

RC1 = Sheets("Compare").Range("C40000").End(xlUp).Row

'‘************Go through each elemnt in compare sheet and compare the value with each element in the Data sheet

'‘************If value is matched then set found =1 or found will bet set to 0. If there is a match then populate G,H and I ************columns with relevant data.

For d48 = 2 To RC1

found = 0

For d4 = 3 To RC2

If Sheets("Data").Range("E" & d4) "" Then

If Sheets("Data").Range("E" & d4) = Sheets("Compare").Range("C" & d48) Then

Sheets("Data").Range("C" & d4) = Sheets("Compare").Range("A" & d48)

Sheets("Data").Range("D" & d4) = Sheets("Compare").Range("B" & d48)

Sheets("Data").Range("G" & d4) = Sheets("Compare").Range("D" & d48)

Sheets("Data").Range("H" & d4) = Sheets("Compare").Range("E" & d48)

Sheets("Data").Range("I" & d4) = Sheets("Compare").Range("F" & d48)

found = 1

End If

End If

Next

' ‘************IF found is set to 0 then puplate lines after 3665 with the unmatched data...

If found = 0 Then

found = 0

RC4 = Sheets("Data").Range("E40000").End(xlUp).Row + 1

'‘************Go through each elemnt in compare sheet and compare the value with each element in the Data sheet

'‘************If value is matched then set found =1 or found will bet set to 0. If there is a match then populate G,H and I ************columns with relevant data.

For h4 = 3666 To RC4 - 1

If Sheets("Data").Range("E" & h4) = Sheets("Compare").Range("C" & d48) Then

Sheets("Data").Range("C" & h4) = Sheets("Compare").Range("A" & d48)

Sheets("Data").Range("D" & h4) = Sheets("Compare").Range("B" & d48)

Sheets("Data").Range("E" & h4) = Sheets("Compare").Range("C" & d48)

Sheets("Data").Range("G" & h4) = Sheets("Compare").Range("D" & d48)

Sheets("Data").Range("H" & h4) = Sheets("Compare").Range("E" & d48)

Sheets("Data").Range("I" & h4) = Sheets("Compare").Range("F" & d48)

found = 1

End If

Next

'If found=0 and data already exists below line 3665 then update that section else create new entry.

If found = 0 Then

RC4 = Sheets("Data").Range("E40000").End(xlUp).Row + 1

Sheets("Data").Range("C" & RC4) = Sheets("Compare").Range("A" & d48)

Sheets("Data").Range("D" & RC4) = Sheets("Compare").Range("B" & d48)

Sheets("Data").Range("E" & RC4) = Sheets("Compare").Range("C" & d48)

Sheets("Data").Range("G" & RC4) = Sheets("Compare").Range("D" & d48)

Sheets("Data").Range("H" & RC4) = Sheets("Compare").Range("E" & d48)

Sheets("Data").Range("I" & RC4) = Sheets("Compare").Range("F" & d48)

End If

End If

Next

End Sub

Skills: Excel, SQL, Visual Basic

See more: vba match excel, excel vba match, works need excel, want excel vba freelancers, sheets add, freelancers in h, freelancers excel, freelancers data entry works, excel vba freelancers, advance excel 2013, advance excel+, find in vba, excel vba with, excel vba or, excel vba and, excel 2013 vba, end vba, excel advance, data entry through sql, vba freelancers, vba e, vba compare, sql freelancers, freelancers find, excel/vba

About the Employer:
( 0 reviews ) North York, Canada

Project ID: #5005155

3 freelancers are bidding on average $57 for this job

slavisatosic

Hello, I am offering you to make this change in one working day. As you can see in my ratings, I already made a lot of similar VBA jobs before on this site, so this job will be done fast and in a professional manner More

$30 USD in 3 days
(78 Reviews)
5.7
gillian1607

TEST BID..............................................................................................................................

$111 USD in 30 days
(3 Reviews)
4.3
malikha1969

God willing, with the experience and skills that I have, I am able to complete the work that you entrusted to me.

$30 USD in 3 days
(0 Reviews)
0.0