VBA Lookup
 

I have a worksheet that has work orders and build dates from last week
(occurs weekly by hand presently). I would like to transfer the dates to the
new worksheet in a new workbook for those work orders that are still present.
I have been using a VLOOKUP, is there a way to do this, line by line in VBA?

Thanks.

VBA Lookup
 

Just some more information.

In last weeks worksheet, I have two columns (among others) that contain a
workorder and a date.

Trav BP Week
H623601 11/7/05
H623619 11/7/05
H815003 11/7/05
H815011 11/7/05

This week, I have just the workorder and would like to add the dates through
VBA

Trav BP Week
H815003
H815011
J456809
J456787

Suggestions would be appreciated.




"Byron" wrote:

I have a worksheet that has work orders and build dates from last week
(occurs weekly by hand presently). I would like to transfer the dates to the
new worksheet in a new workbook for those work orders that are still present.
I have been using a VLOOKUP, is there a way to do this, line by line in VBA?

Thanks.

VBA Lookup
 

I would think that the easiest thing to do would be to just use =vlookup().

With both worksheets in the same workbook...

Option Explicit
Sub testme01()

Dim LastWks As Worksheet
Dim ThisWks As Worksheet
Dim DateRng As Range

Set LastWks = Worksheets("lastweek")
Set ThisWks = Worksheets("ThisWeek")

With ThisWks
Set DateRng = .Range("b2:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With DateRng
.FormulaR1C1 = "=vlookup(rc[-1]," _
& LastWks.Range("a:b") _
.Address(ReferenceStyle:=xlR1C1, external:=True) _
& ",2,false)"
.Value = .Value
.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, MatchCase:=False
End With

End Sub

Byron wrote:

Just some more information.

In last weeks worksheet, I have two columns (among others) that contain a
workorder and a date.

Trav BP Week
H623601 11/7/05
H623619 11/7/05
H815003 11/7/05
H815011 11/7/05

This week, I have just the workorder and would like to add the dates through
VBA

Trav BP Week
H815003
H815011
J456809
J456787

Suggestions would be appreciated.



"Byron" wrote:

I have a worksheet that has work orders and build dates from last week
(occurs weekly by hand presently). I would like to transfer the dates to the
new worksheet in a new workbook for those work orders that are still present.
I have been using a VLOOKUP, is there a way to do this, line by line in VBA?

Thanks.

--

Dave Peterson

VBA Lookup
 

Thanks for the solution. It works great, but I have these two worksheets in
seperate workbooks with multiple other worksheets. Is there a way to make
this suggestion work arcross two workbooks. Thanks

"Dave Peterson" wrote:

I would think that the easiest thing to do would be to just use =vlookup().

With both worksheets in the same workbook...

Option Explicit
Sub testme01()

Dim LastWks As Worksheet
Dim ThisWks As Worksheet
Dim DateRng As Range

Set LastWks = Worksheets("lastweek")
Set ThisWks = Worksheets("ThisWeek")

With ThisWks
Set DateRng = .Range("b2:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With DateRng
.FormulaR1C1 = "=vlookup(rc[-1]," _
& LastWks.Range("a:b") _
.Address(ReferenceStyle:=xlR1C1, external:=True) _
& ",2,false)"
.Value = .Value
.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, MatchCase:=False
End With

End Sub

Byron wrote:

Just some more information.

In last weeks worksheet, I have two columns (among others) that contain a
workorder and a date.

Trav BP Week
H623601 11/7/05
H623619 11/7/05
H815003 11/7/05
H815011 11/7/05

This week, I have just the workorder and would like to add the dates through
VBA

Trav BP Week
H815003
H815011
J456809
J456787

Suggestions would be appreciated.



"Byron" wrote:

I have a worksheet that has work orders and build dates from last week
(occurs weekly by hand presently). I would like to transfer the dates to the
new worksheet in a new workbook for those work orders that are still present.
I have been using a VLOOKUP, is there a way to do this, line by line in VBA?

Thanks.

--

Dave Peterson

VBA Lookup
 

Both workbooks need to be open.

Set LastWks = workbooks("Firstworkbookname.xls").Worksheets("las tweek")
Set ThisWks = workbooks("Secondworkbookname.xls").Worksheets("Th isWeek")



Byron wrote:

Thanks for the solution. It works great, but I have these two worksheets in
seperate workbooks with multiple other worksheets. Is there a way to make
this suggestion work arcross two workbooks. Thanks

"Dave Peterson" wrote:

I would think that the easiest thing to do would be to just use =vlookup().

With both worksheets in the same workbook...

Option Explicit
Sub testme01()

Dim LastWks As Worksheet
Dim ThisWks As Worksheet
Dim DateRng As Range

Set LastWks = Worksheets("lastweek")
Set ThisWks = Worksheets("ThisWeek")

With ThisWks
Set DateRng = .Range("b2:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With DateRng
.FormulaR1C1 = "=vlookup(rc[-1]," _
& LastWks.Range("a:b") _
.Address(ReferenceStyle:=xlR1C1, external:=True) _
& ",2,false)"
.Value = .Value
.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, MatchCase:=False
End With

End Sub

Byron wrote:

Just some more information.

In last weeks worksheet, I have two columns (among others) that contain a
workorder and a date.

Trav BP Week
H623601 11/7/05
H623619 11/7/05
H815003 11/7/05
H815011 11/7/05

This week, I have just the workorder and would like to add the dates through
VBA

Trav BP Week
H815003
H815011
J456809
J456787

Suggestions would be appreciated.



"Byron" wrote:

I have a worksheet that has work orders and build dates from last week
(occurs weekly by hand presently). I would like to transfer the dates to the
new worksheet in a new workbook for those work orders that are still present.
I have been using a VLOOKUP, is there a way to do this, line by line in VBA?

Thanks.

--

Dave Peterson


--

Dave Peterson

VBA Lookup
 

Works great. Thank you for your patience.

Byron

"Dave Peterson" wrote:

Both workbooks need to be open.

Set LastWks = workbooks("Firstworkbookname.xls").Worksheets("las tweek")
Set ThisWks = workbooks("Secondworkbookname.xls").Worksheets("Th isWeek")



Byron wrote:

Thanks for the solution. It works great, but I have these two worksheets in
seperate workbooks with multiple other worksheets. Is there a way to make
this suggestion work arcross two workbooks. Thanks

"Dave Peterson" wrote:

I would think that the easiest thing to do would be to just use =vlookup().

With both worksheets in the same workbook...

Option Explicit
Sub testme01()

Dim LastWks As Worksheet
Dim ThisWks As Worksheet
Dim DateRng As Range

Set LastWks = Worksheets("lastweek")
Set ThisWks = Worksheets("ThisWeek")

With ThisWks
Set DateRng = .Range("b2:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With DateRng
.FormulaR1C1 = "=vlookup(rc[-1]," _
& LastWks.Range("a:b") _
.Address(ReferenceStyle:=xlR1C1, external:=True) _
& ",2,false)"
.Value = .Value
.Replace what:="#n/a", replacement:="", _
lookat:=xlWhole, MatchCase:=False
End With

End Sub

Byron wrote:

Just some more information.

In last weeks worksheet, I have two columns (among others) that contain a
workorder and a date.

Trav BP Week
H623601 11/7/05
H623619 11/7/05
H815003 11/7/05
H815011 11/7/05

This week, I have just the workorder and would like to add the dates through
VBA

Trav BP Week
H815003
H815011
J456809
J456787

Suggestions would be appreciated.



"Byron" wrote:

I have a worksheet that has work orders and build dates from last week
(occurs weekly by hand presently). I would like to transfer the dates to the
new worksheet in a new workbook for those work orders that are still present.
I have been using a VLOOKUP, is there a way to do this, line by line in VBA?

Thanks.

--

Dave Peterson


--

Dave Peterson