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

Closed

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: 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, match update data vba, vba update excel

Project ID: #5000822

4 freelancers are bidding on average $24 for this job

adim

Hello there, This can be done in maximum one hour. I'm ready to start immediately. It would help to send the file, if you have it. Regards, Adrian

$20 USD in 1 day
(13 Reviews)
4.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
paintthesky14

Hi, It is a very small task for me. I will give you the code within an hour if the bid is approved. Please see my previous track records on Freelancer to verify the veracity of my claims. Looking forward to work More

$20 USD in 1 day
(3 Reviews)
2.6
saktulgasaktulga

Hello, I will be pleased to make the necessary changes for you. Just send me the excel file please. The code will be ready in the same day.

$25 USD in 1 day
(0 Reviews)
0.0