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, Visual Basic

See more: excel vba array, vba match records, 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, vba freelancers, vba e, vba compare, freelancers find, excel/vba, compare freelancers, advance excel

About the Employer:
( 2 reviews ) Austin, United States

Project ID: #5000831

7 freelancers are bidding on average $30 for this job

Teloquence

Excel expert at your service. Check my past reviews for your reference. Completed more than 100 [url removed, login to view] waiting for your reply.

$30 USD in 0 days
(143 Reviews)
6.3
elMancha

Hello there. I have high Excel and Visual Basic skills with great professionalism. I study electronics and computer engineering at Oporto university and I'm looking for work to fill the blanks on my schedule. I More

$30 USD in 1 day
(19 Reviews)
4.3
Gooroo

I'm your man for this Sir! I will even optimize your code if you want - just send me some sample sheet so I can try this macro on and see exactly what is happening. Let me help you with this - quality and speed are GUA More

$30 USD in 0 days
(16 Reviews)
4.3
thanhhungqb

Dear sir, I have read project requirement and have a look on code you add. I am interested in your project, and I realize that I can give you perfect solution. Hope you aware project to me. Thank you very muc More

$30 USD in 0 days
(14 Reviews)
3.5
paintthesky14

Hi, It is a very small task for me. I will give you the code within an hour if the bid is approved. If you want, I can do the job for even lesser amount like 20$ as it a very small task for me. Please see my previou More

$30 USD in 1 day
(3 Reviews)
2.6
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 1 day
(0 Reviews)
0.0
shanewild85

This is a very simple task that I can expect to complete within minutes. I can ensure a quick turn around at a minimal cost. I'll be sure to retain your existing coding and only add the elements necessary to facilitate More

$30 USD in 1 day
(0 Reviews)
0.0