I have this code that works, it looks in column A for the number you enter
and puts today's date in column F when it finds a match, I would like to
replace the input box so it will look at a range of numbers, in a different
workbook, and use them instead, I am not sure this can be done but from some
of the things I have seen on this newsgroup I would bet that it can be :) ,
if anybody would like to help on this here are the details.
Using excel 97 and 2000, the workbook that will have the date put in it is
named
work orders 2003.xls and the VBA sheet name is sheet1, the worksheet tab
name is workorders, the name of the workbook that has the numbers I want to
use is Daily Labor Summary.xls and there are two sheets I need to get
numbers from, the VBA sheet names are sheet1 and sheet11, the worksheet tab
names are page 1 and page 2, the range on both of the sheets are the same,
range G29:AD29, this range will have some blank cells without numbers in
them. The macro will be ran from the daily labor summary workbook and both
workbooks will be open, but it would be nice to have some kind of error trap
if the work orders 2003 workbook is not open. If anymore information is
needed I will be more than happy to provide it.
Thanks in advance to all that help, Paul B

Sub Close_workorder()
'puts todays date in Date Completed, column F, for the work order number
you enter
'shortcut ctrl q
Sheets("workorders").Select
Dim searchvalue, Message, Title, Default
Dim c
Do
Message = "What workorder number do you want to close?" ' Set prompt.
Title = "Close Open Work Orders" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
searchvalue = InputBox(Message, Title, Default)
If searchvalue = "" Then
Exit Sub
End If
With Range("A2:A2500")
Set c = .Find(What:=searchvalue, LookAt:=xlWhole, LookIn:=xlValues)
If c Is Nothing Then
MsgBox ("Check Workorder # " & searchvalue), vbInformation, "Match Not
Found"
Else
Range(c.Address).Offset(0, 5).FormulaR1C1 = Date
End If
End With
Loop While searchvalue < ""
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

Paul,

Why not just open the other workbook, store the values in an array, and then
just amend the search code to loop through the array, circa

For i = LBound(myArray,1) To UBound(myArray,1)
With Range("A2:A2500")
Set c = .Find(What:=myArray(i), LookAt:=xlWhole,
LookIn:=xlValues)
If c Is Nothing Then
MsgBox ("Check Workorder # " & searchvalue), vbInformation,
"Match Not Found "
Else
Range(c.Address).Offset(0, 5).FormulaR1C1 = Date
Exit For
End If
End With

You seem to have enough knowledge to do the rest and build on this yourself.
It's not hard.


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Paul B" wrote in message
...
I have this code that works, it looks in column A for the number you enter
and puts today's date in column F when it finds a match, I would like to
replace the input box so it will look at a range of numbers, in a
different
workbook, and use them instead, I am not sure this can be done but from
some
of the things I have seen on this newsgroup I would bet that it can be :)
,
if anybody would like to help on this here are the details.
Using excel 97 and 2000, the workbook that will have the date put in it is
named
work orders 2003.xls and the VBA sheet name is sheet1, the worksheet tab
name is workorders, the name of the workbook that has the numbers I want
to
use is Daily Labor Summary.xls and there are two sheets I need to get
numbers from, the VBA sheet names are sheet1 and sheet11, the worksheet
tab
names are page 1 and page 2, the range on both of the sheets are the same,
range G29:AD29, this range will have some blank cells without numbers in
them. The macro will be ran from the daily labor summary workbook and both
workbooks will be open, but it would be nice to have some kind of error
trap
if the work orders 2003 workbook is not open. If anymore information is
needed I will be more than happy to provide it.
Thanks in advance to all that help, Paul B

Sub Close_workorder()
'puts todays date in Date Completed, column F, for the work order number
you enter
'shortcut ctrl q
Sheets("workorders").Select
Dim searchvalue, Message, Title, Default
Dim c
Do
Message = "What workorder number do you want to close?" ' Set prompt.
Title = "Close Open Work Orders" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
searchvalue = InputBox(Message, Title, Default)
If searchvalue = "" Then
Exit Sub
End If
With Range("A2:A2500")
Set c = .Find(What:=searchvalue, LookAt:=xlWhole, LookIn:=xlValues)
If c Is Nothing Then
MsgBox ("Check Workorder # " & searchvalue), vbInformation, "Match Not
Found"
Else
Range(c.Address).Offset(0, 5).FormulaR1C1 = Date
End If
End With
Loop While searchvalue < ""
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

Bob, that sounds good and thanks for the vote of confidence, but I have
never used and array like this, I am still struggling with VBA so if you don
't mine could you walk me though it? I try to answer the questions I can on
some of the newsgroups but it amazes me how fast some of the people here can
come up with answers to questions that now takes me hours to do, if at all,
but I learn something new every week so I guess I am improving, Thanks.
Paul B

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"Bob Phillips" wrote in message
...
Paul,

Why not just open the other workbook, store the values in an array, and
then
just amend the search code to loop through the array, circa

For i = LBound(myArray,1) To UBound(myArray,1)
With Range("A2:A2500")
Set c = .Find(What:=myArray(i), LookAt:=xlWhole,
LookIn:=xlValues)
If c Is Nothing Then
MsgBox ("Check Workorder # " & searchvalue),
vbInformation,
"Match Not Found "
Else
Range(c.Address).Offset(0, 5).FormulaR1C1 = Date
Exit For
End If
End With

You seem to have enough knowledge to do the rest and build on this
yourself.
It's not hard.


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Paul B" wrote in message
...
I have this code that works, it looks in column A for the number you
enter
and puts today's date in column F when it finds a match, I would like
to
replace the input box so it will look at a range of numbers, in a
different
workbook, and use them instead, I am not sure this can be done but from
some
of the things I have seen on this newsgroup I would bet that it can be
:)
,
if anybody would like to help on this here are the details.
Using excel 97 and 2000, the workbook that will have the date put in it
is
named
work orders 2003.xls and the VBA sheet name is sheet1, the worksheet tab
name is workorders, the name of the workbook that has the numbers I
want
to
use is Daily Labor Summary.xls and there are two sheets I need to get
numbers from, the VBA sheet names are sheet1 and sheet11, the worksheet
tab
names are page 1 and page 2, the range on both of the sheets are the
same,
range G29:AD29, this range will have some blank cells without numbers in
them. The macro will be ran from the daily labor summary workbook and
both
workbooks will be open, but it would be nice to have some kind of error
trap
if the work orders 2003 workbook is not open. If anymore information is
needed I will be more than happy to provide it.
Thanks in advance to all that help, Paul B

Sub Close_workorder()
'puts todays date in Date Completed, column F, for the work order
number
you enter
'shortcut ctrl q
Sheets("workorders").Select
Dim searchvalue, Message, Title, Default
Dim c
Do
Message = "What workorder number do you want to close?" ' Set prompt.
Title = "Close Open Work Orders" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
searchvalue = InputBox(Message, Title, Default)
If searchvalue = "" Then
Exit Sub
End If
With Range("A2:A2500")
Set c = .Find(What:=searchvalue, LookAt:=xlWhole, LookIn:=xlValues)
If c Is Nothing Then
MsgBox ("Check Workorder # " & searchvalue), vbInformation, "Match Not
Found"
Else
Range(c.Address).Offset(0, 5).FormulaR1C1 = Date
End If
End With
Loop While searchvalue < ""
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **