vlookup sub procedure help
 

Hi,

I have an active workbook named Updates (sheet1) that I'd like to use to
update another closed workbork named NetRev on the C:\Network directory. I'd
like help to develop a sub which for each Id located in Column A of the
Updates workbook sheet1 (which is active) it would look in the closed NetRev
workbook column B and when found it would update Columns C thru G with the
results of the Updates workbook Columns B thru F. The number of rows vary
each day for Updates. I currently have 340 rows now. Also if the item is not
in Updates workbook but in NetRev I'd like to delete it from NetRev. Also if
it's a new item in Updates workbook, I'd like to add it to the last row of
NetRev workbook.

Any help would be greatly appreciated.
--
By persisting in your path, though you forfeit the little, you gain the
great.

vlookup sub procedure help
 

Have a look here, Very useful stuff
http://www.rondebruin.nl/copy7.htm

"DavidH56" wrote:

Hi,

I have an active workbook named Updates (sheet1) that I'd like to use to
update another closed workbork named NetRev on the C:\Network directory. I'd
like help to develop a sub which for each Id located in Column A of the
Updates workbook sheet1 (which is active) it would look in the closed NetRev
workbook column B and when found it would update Columns C thru G with the
results of the Updates workbook Columns B thru F. The number of rows vary
each day for Updates. I currently have 340 rows now. Also if the item is not
in Updates workbook but in NetRev I'd like to delete it from NetRev. Also if
it's a new item in Updates workbook, I'd like to add it to the last row of
NetRev workbook.

Any help would be greatly appreciated.
--
By persisting in your path, though you forfeit the little, you gain the
great.

vlookup sub procedure help
 

Thanks for replying,

I've used some of Ron Debruin's code and it has helped me quite a lot. But
since I have other columns of data in the NetRev workbook I need to do more
than simply retrieve data from closed workbook. I actually need to place
data into the closed workbook using vlookup from the Updates workbook to the
NetRev workbook. I seen similiar instances posted here such as:

Sub comparebooks()

LookFName = "C:\My documents\Lookup.xls"
Set Lookbk = Workbooks.Open(Filename:=LookFName)
Set SearchRange = Lookbk.Sheets("Sheet1").Columns("G")

With Workbooks("A.xls").Sheets("Sheet1")
Sh1RowCount = 1
Sh2RowCount = 1
Do While .Range("A" & Sh1RowCount) < ""
SearchValue = .Range("A" & Sh1RowCount)
Set c = SearchRange.Find(what:=SearchValue, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
With Workbooks("A.xls").Sheets("Sheet2")
.Range("A" & Sh2RowCount) = SearchValue
Sh2RowCount = Sh2RowCount + 1
End With
Else
.Range("B" & Sh1RowCount) = c.Offset(0, 6)
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With
Lookbk.Close savechanges:=False
End Sub

I'd like to be able to adjust such sub procedure for my particular situation.

Thanks

--
By persisting in your path, though you forfeit the little, you gain the
great.



"Office_Novice" wrote:

Have a look here, Very useful stuff
http://www.rondebruin.nl/copy7.htm

"DavidH56" wrote:

Hi,

I have an active workbook named Updates (sheet1) that I'd like to use to
update another closed workbork named NetRev on the C:\Network directory. I'd
like help to develop a sub which for each Id located in Column A of the
Updates workbook sheet1 (which is active) it would look in the closed NetRev
workbook column B and when found it would update Columns C thru G with the
results of the Updates workbook Columns B thru F. The number of rows vary
each day for Updates. I currently have 340 rows now. Also if the item is not
in Updates workbook but in NetRev I'd like to delete it from NetRev. Also if
it's a new item in Updates workbook, I'd like to add it to the last row of
NetRev workbook.

Any help would be greatly appreciated.
--
By persisting in your path, though you forfeit the little, you gain the
great.