Update sheet one if data is not present.
 

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

Update sheet one if data is not present.
 

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

Update sheet one if data is not present.
 

Hi Joel,

Thanks for your prompt reply. I am having a little trouble with the script in
that it enters "Component" at the end of column B and "Version" at the end of
column C instead of updating the component version. I have written an example
of what was expected.

Sheet 1
Column B Column C
Component Version
component 1 version1
component 2 version2
component 3 version3
component 4 version4
component 5 version5

Sheet 2
Component Version
component 2 version2.1
component 3 version3.1
component 5 version5.1
component 6 version 1

Result for sheet 1
Component Version
component 1 version1
component 2 version2.1
component 3 version3.1
component 4 version4
component 5 version5.1
component 6 version 1

Can you help?
Thanks,
Paul.

Joel wrote:
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

Hello,

[quoted text clipped - 19 lines]
Thanks and regards,
Paul.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Update sheet one if data is not present.
 

The code is working the way it is suppose to, the data in your worksheet is
bad. The code will add the new lines to sheet 1 if the Component Names don't
EXACTLY match between sheets1 and sheet2. Check carefully for Extra spaces
(beginning and end in Column B), Compare Capitalization in the two sheets,
check for typo errors. Only the lines that don't match will be added to the
end.

I can modify the code to handle some of the mismatches if I knew what they
were.

"par4724 via OfficeKB.com" wrote:

Hi Joel,

Thanks for your prompt reply. I am having a little trouble with the script in
that it enters "Component" at the end of column B and "Version" at the end of
column C instead of updating the component version. I have written an example
of what was expected.

Sheet 1
Column B Column C
Component Version
component 1 version1
component 2 version2
component 3 version3
component 4 version4
component 5 version5

Sheet 2
Component Version
component 2 version2.1
component 3 version3.1
component 5 version5.1
component 6 version 1

Result for sheet 1
Component Version
component 1 version1
component 2 version2.1
component 3 version3.1
component 4 version4
component 5 version5.1
component 6 version 1

Can you help?
Thanks,
Paul.

Joel wrote:
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

Hello,

[quoted text clipped - 19 lines]
Thanks and regards,
Paul.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Update sheet one if data is not present.
 

Hi again Joel,

I am still struggling on this. I have looked to see if there are any typo's
and there does not appear to be any. The script just puts the word
"component" at the end of column B and the word "version" at the bottom of
column C. Am i missing something here? Would i be able to send you an xls
example?
i.e

Sheet 1
Column B Column C
Component Version
component 1 version1
component 2 version2
component 3 version3
component 4 version4
component 5 version5
component version
component version
component version
component version
component version

Thanks,
Paul.

Joel wrote:
The code is working the way it is suppose to, the data in your worksheet is
bad. The code will add the new lines to sheet 1 if the Component Names don't
EXACTLY match between sheets1 and sheet2. Check carefully for Extra spaces
(beginning and end in Column B), Compare Capitalization in the two sheets,
check for typo errors. Only the lines that don't match will be added to the
end.

I can modify the code to handle some of the mismatches if I knew what they
were.

Hi Joel,

[quoted text clipped - 64 lines]
Thanks and regards,
Paul.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Update sheet one if data is not present.
 

I found the problem. RowCount was being increnmented. sorry. I also change
the first row processed from 1 to 2.

Sub UpdateVersion()

With Sheets("sheet1")
Sht1LastRow = Range("B" & Rows.Count).End(xlUp).Row
Newrow = Sht1LastRow + 1
End With
With Sheets("sheet2")
RowCount = 2
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
RowCount = RowCount + 1
Loop
End With

End Sub




"par4724 via OfficeKB.com" wrote:

Hi again Joel,

I am still struggling on this. I have looked to see if there are any typo's
and there does not appear to be any. The script just puts the word
"component" at the end of column B and the word "version" at the bottom of
column C. Am i missing something here? Would i be able to send you an xls
example?
i.e

Sheet 1
Column B Column C
Component Version
component 1 version1
component 2 version2
component 3 version3
component 4 version4
component 5 version5
component version
component version
component version
component version
component version

Thanks,
Paul.

Joel wrote:
The code is working the way it is suppose to, the data in your worksheet is
bad. The code will add the new lines to sheet 1 if the Component Names don't
EXACTLY match between sheets1 and sheet2. Check carefully for Extra spaces
(beginning and end in Column B), Compare Capitalization in the two sheets,
check for typo errors. Only the lines that don't match will be added to the
end.

I can modify the code to handle some of the mismatches if I knew what they
were.

Hi Joel,

[quoted text clipped - 64 lines]
Thanks and regards,
Paul.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1