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

CLOSED
Bids
7
Avg Bid (USD)
$30
Project Budget (USD)
$30 - $250

Project Description:
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 required:
Excel, Visual Basic
Qualifications required:
uk_english1 UK English - Level 1
About the employer:
Verified
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.


$ 30
in 0 days
$ 30
in 1 days
$ 30
in 0 days
$ 30
in 0 days
Hire paintthesky14
$ 30
in 1 days
$ 30
in 1 days
Hire shanewild85
$ 30
in 1 days