Closed

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

This project received 7 bids from talented freelancers with an average bid price of $30 USD.

Get free quotes for a project like this
Employer working
Skills Required
Project Budget
$30 - $250 USD
Total Bids
7
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

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online