Sub UpdateVersion()

With Sheets("sheet1")
Sht1LastRow = Range("B" & Rows.Count).End(xlUp).Row
Newrow = Sht1LastRow + 1
End With
With Sheets("sheet2")
RowCount = 1
Do While .Range("B" & RowCount) < ""
Component = .Range("B" & RowCount)
Version = .Range("C" & RowCount)
With Sheets("sheet1")
Set c = .Columns("B").Find(what:=Component, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
c.Offset(0, 1) = Version
Else
.Range("B" & Newrow) = Component
.Range("C" & Newrow) = Version
Newrow = Newrow + 1
End If
End With
Loop
End With

End Sub
"par4724 via OfficeKB.com" wrote:

Hello,

I have a xls sheet which contains "component name" in column B and "component
version" in column C. I have to update both of the columns regularly with new
components (in which case they are added to the bottom of the list) or if the
components are already in the list, then i simply update the component
version in column C. I was wodering if anybody new of a VBA script where i
could: -

paste my component and version list into sheet 2,
run the macro and the script would check if the component name already
existed in sheet 1, column B.
If the component already exists, then update the component version from sheet
2, column C to sheet 1, column C.
If the component does not exist, then add the component to the bottom of the
component list and copy over the component version from column C.
Run down column B and repeat the process in a loop until a blank cell is
reached.

Any help would be appreciated.

Thanks and regards,
Paul.

--
Message posted via http://www.officekb.com