Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP to modify code (need more automation)
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 ** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP to modify code (need more automation)
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 ** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP to modify code (need more automation)
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 ** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP to modify code (need more automation)
Bob you said "It's not hard", looks like a lot to me :), when I put the
code in the lines below were like this, SearchWorkOrders Workbooks("work orders 2003.xls").Worksheets("workorders"), aryWorkOrders hope it shows up right, the lines were red so I put it all on one line, with a space between orders and 2003, when I ran the close_workorder code I got run time error 9 subscript out of range and the line AddToArray oWbLabor.Worksheets("Page1"), aryWorkOrders highlighted. Any ideas? Thanks -- 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, Here is a solution, I hope I have interpreted it correctly. I have broken the code up into modules, so I hope it is easy to follow. Just fire back if you have any questions. I have broken the lines up as best I could but watch for wrap-around. Option Explicit Dim iWorkOrders As Long Sub Close_workorder() 'puts todays date in Date Completed, column F, 'for the work order number you enter 'shortcut ctrl q Const Message As String = "What workorder number do you want to close?" Const Title As String = "Close Open Work Orders" Const Default As String = "" Dim oWbLabor As Workbook Dim oWsWorkorders As Worksheet Dim aryWorkOrders() iWorkOrders = 0 On Error Resume Next Set oWbLabor = Workbooks("Daily Labor Summary.xls") If oWbLabor Is Nothing Then MsgBox "Daily Labor Summary workbook is not open", vbCritical Else On Error GoTo 0 AddToArray oWbLabor.Worksheets("Page1"), aryWorkOrders AddToArray oWbLabor.Worksheets("Page2"), aryWorkOrders SearchWorkOrders Workbooks("work orders 2003.xls").Worksheets("workorders"), aryWorkOrders End If End Sub Private Sub SearchWorkOrders(oWsWorkorders As Worksheet, source) Dim c As Range Dim iWorkOrders As Long With oWsWorkorders With .Range("A2:A2500") For iWorkOrders = LBound(source) To _ UBound(source) Set c = .Find(What:=source(iWorkOrders), _ LookAt:=xlWhole, _ LookIn:=xlValues) If c Is Nothing Then MsgBox ("Check Workorder #" & source(iWorkOrders)), _ vbInformation, _ "Match Not Found " Else oWsWorkorders.Range(c.Address).Offset(0, 5).Value = Date End If Next iWorkOrders End With End With End Sub Private Sub AddToArray(ByVal source As Worksheet, ByRef target) Dim c As Range For Each c In source.Range("G29:AD29") If Not IsEmpty(c) Then ReDim Preserve target(iWorkOrders) target(iWorkOrders) = c.Value iWorkOrders = iWorkOrders + 1 End If Next c End Sub -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Paul B" wrote in message ... 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 ** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP to modify code (need more automation)
Paul,
First problem is wrap-around, second is me slightly mis-reading the post. I used sheet names of Page1 and Page2 whereas I think from your post that you have a space included. Try this version Option Explicit Dim iWorkOrders As Long Sub Close_workorder() 'puts todays date in Date Completed, column F, 'for the work order number you enter 'shortcut ctrl q Const Message As String = "What workorder number do you want to close?" Const Title As String = "Close Open Work Orders" Const Default As String = "" Dim oWbLabor As Workbook Dim oWsWorkorders As Worksheet Dim aryWorkOrders() iWorkOrders = 0 On Error Resume Next Set oWbLabor = Workbooks("Daily Labor Summary.xls") If oWbLabor Is Nothing Then MsgBox "Daily Labor Summary workbook is not open", vbCritical Else On Error GoTo 0 AddToArray oWbLabor.Worksheets("Page 1"), aryWorkOrders AddToArray oWbLabor.Worksheets("Page 2"), aryWorkOrders SearchWorkOrders Workbooks("work orders 2003.xls") _ .Worksheets("workorders"), aryWorkOrders End If End Sub Private Sub SearchWorkOrders(oWsWorkorders As Worksheet, source) Dim c As Range Dim iWorkOrders As Long With oWsWorkorders With .Range("A2:A2500") For iWorkOrders = LBound(source) To _ UBound(source) Set c = .Find(What:=source(iWorkOrders), _ LookAt:=xlWhole, _ LookIn:=xlValues) If c Is Nothing Then MsgBox ("Check Workorder #" & source(iWorkOrders)), _ vbInformation, _ "Match Not Found " Else oWsWorkorders.Range(c.Address).Offset(0, 5).Value = Date End If Next iWorkOrders End With End With End Sub Private Sub AddToArray(ByVal source As Worksheet, ByRef target) Dim c As Range For Each c In source.Range("G29:AD29") If Not IsEmpty(c) Then ReDim Preserve target(iWorkOrders) target(iWorkOrders) = c.Value iWorkOrders = iWorkOrders + 1 End If Next c End Sub -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Paul B" wrote in message ... Bob you said "It's not hard", looks like a lot to me :), when I put the code in the lines below were like this, SearchWorkOrders Workbooks("work orders 2003.xls").Worksheets("workorders"), aryWorkOrders hope it shows up right, the lines were red so I put it all on one line, with a space between orders and 2003, when I ran the close_workorder code I got run time error 9 subscript out of range and the line AddToArray oWbLabor.Worksheets("Page1"), aryWorkOrders highlighted. Any ideas? Thanks -- 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, Here is a solution, I hope I have interpreted it correctly. I have broken the code up into modules, so I hope it is easy to follow. Just fire back if you have any questions. I have broken the lines up as best I could but watch for wrap-around. Option Explicit Dim iWorkOrders As Long Sub Close_workorder() 'puts todays date in Date Completed, column F, 'for the work order number you enter 'shortcut ctrl q Const Message As String = "What workorder number do you want to close?" Const Title As String = "Close Open Work Orders" Const Default As String = "" Dim oWbLabor As Workbook Dim oWsWorkorders As Worksheet Dim aryWorkOrders() iWorkOrders = 0 On Error Resume Next Set oWbLabor = Workbooks("Daily Labor Summary.xls") If oWbLabor Is Nothing Then MsgBox "Daily Labor Summary workbook is not open", vbCritical Else On Error GoTo 0 AddToArray oWbLabor.Worksheets("Page1"), aryWorkOrders AddToArray oWbLabor.Worksheets("Page2"), aryWorkOrders SearchWorkOrders Workbooks("work orders 2003.xls").Worksheets("workorders"), aryWorkOrders End If End Sub Private Sub SearchWorkOrders(oWsWorkorders As Worksheet, source) Dim c As Range Dim iWorkOrders As Long With oWsWorkorders With .Range("A2:A2500") For iWorkOrders = LBound(source) To _ UBound(source) Set c = .Find(What:=source(iWorkOrders), _ LookAt:=xlWhole, _ LookIn:=xlValues) If c Is Nothing Then MsgBox ("Check Workorder #" & source(iWorkOrders)), _ vbInformation, _ "Match Not Found " Else oWsWorkorders.Range(c.Address).Offset(0, 5).Value = Date End If Next iWorkOrders End With End With End Sub Private Sub AddToArray(ByVal source As Worksheet, ByRef target) Dim c As Range For Each c In source.Range("G29:AD29") If Not IsEmpty(c) Then ReDim Preserve target(iWorkOrders) target(iWorkOrders) = c.Value iWorkOrders = iWorkOrders + 1 End If Next c End Sub -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Paul B" wrote in message ... 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 ** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP to modify code (need more automation)
Bob, that works just like I need, thanks very much, I did not spot the lack
of the space in the worksheet name but you were right there was a space in it, after some thinking I modified it some to take care of the sheet changing names next year and also added a date for the labor sheet so it can be saved each day, the code I used was this, don't know if it's the best way but it works :) , is this a good way to reference the day and year or is there a better way? For the labor sheet Const DATEFORMAT As String = "mm.dd.yyyy" Set oWbLabor = Workbooks("Daily Labor For " & Format(Now, DATEFORMAT) & ".xls") And for the work orders sheet SearchWorkOrders Workbooks("work orders " & Year(Date) & ".xls") _ Now if I can just decipher HOW your code works :). Thanks again 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, First problem is wrap-around, second is me slightly mis-reading the post. I used sheet names of Page1 and Page2 whereas I think from your post that you have a space included. Try this version Option Explicit Dim iWorkOrders As Long Sub Close_workorder() 'puts todays date in Date Completed, column F, 'for the work order number you enter 'shortcut ctrl q Const Message As String = "What workorder number do you want to close?" Const Title As String = "Close Open Work Orders" Const Default As String = "" Dim oWbLabor As Workbook Dim oWsWorkorders As Worksheet Dim aryWorkOrders() iWorkOrders = 0 On Error Resume Next Set oWbLabor = Workbooks("Daily Labor Summary.xls") If oWbLabor Is Nothing Then MsgBox "Daily Labor Summary workbook is not open", vbCritical Else On Error GoTo 0 AddToArray oWbLabor.Worksheets("Page 1"), aryWorkOrders AddToArray oWbLabor.Worksheets("Page 2"), aryWorkOrders SearchWorkOrders Workbooks("work orders 2003.xls") _ .Worksheets("workorders"), aryWorkOrders End If End Sub Private Sub SearchWorkOrders(oWsWorkorders As Worksheet, source) Dim c As Range Dim iWorkOrders As Long With oWsWorkorders With .Range("A2:A2500") For iWorkOrders = LBound(source) To _ UBound(source) Set c = .Find(What:=source(iWorkOrders), _ LookAt:=xlWhole, _ LookIn:=xlValues) If c Is Nothing Then MsgBox ("Check Workorder #" & source(iWorkOrders)), _ vbInformation, _ "Match Not Found " Else oWsWorkorders.Range(c.Address).Offset(0, 5).Value = Date End If Next iWorkOrders End With End With End Sub Private Sub AddToArray(ByVal source As Worksheet, ByRef target) Dim c As Range For Each c In source.Range("G29:AD29") If Not IsEmpty(c) Then ReDim Preserve target(iWorkOrders) target(iWorkOrders) = c.Value iWorkOrders = iWorkOrders + 1 End If Next c End Sub -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Paul B" wrote in message ... Bob you said "It's not hard", looks like a lot to me :), when I put the code in the lines below were like this, SearchWorkOrders Workbooks("work orders 2003.xls").Worksheets("workorders"), aryWorkOrders hope it shows up right, the lines were red so I put it all on one line, with a space between orders and 2003, when I ran the close_workorder code I got run time error 9 subscript out of range and the line AddToArray oWbLabor.Worksheets("Page1"), aryWorkOrders highlighted. Any ideas? Thanks -- 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, Here is a solution, I hope I have interpreted it correctly. I have broken the code up into modules, so I hope it is easy to follow. Just fire back if you have any questions. I have broken the lines up as best I could but watch for wrap-around. Option Explicit Dim iWorkOrders As Long Sub Close_workorder() 'puts todays date in Date Completed, column F, 'for the work order number you enter 'shortcut ctrl q Const Message As String = "What workorder number do you want to close?" Const Title As String = "Close Open Work Orders" Const Default As String = "" Dim oWbLabor As Workbook Dim oWsWorkorders As Worksheet Dim aryWorkOrders() iWorkOrders = 0 On Error Resume Next Set oWbLabor = Workbooks("Daily Labor Summary.xls") If oWbLabor Is Nothing Then MsgBox "Daily Labor Summary workbook is not open", vbCritical Else On Error GoTo 0 AddToArray oWbLabor.Worksheets("Page1"), aryWorkOrders AddToArray oWbLabor.Worksheets("Page2"), aryWorkOrders SearchWorkOrders Workbooks("work orders 2003.xls").Worksheets("workorders"), aryWorkOrders End If End Sub Private Sub SearchWorkOrders(oWsWorkorders As Worksheet, source) Dim c As Range Dim iWorkOrders As Long With oWsWorkorders With .Range("A2:A2500") For iWorkOrders = LBound(source) To _ UBound(source) Set c = .Find(What:=source(iWorkOrders), _ LookAt:=xlWhole, _ LookIn:=xlValues) If c Is Nothing Then MsgBox ("Check Workorder #" & source(iWorkOrders)), _ vbInformation, _ "Match Not Found " Else oWsWorkorders.Range(c.Address).Offset(0, 5).Value = Date End If Next iWorkOrders End With End With End Sub Private Sub AddToArray(ByVal source As Worksheet, ByRef target) Dim c As Range For Each c In source.Range("G29:AD29") If Not IsEmpty(c) Then ReDim Preserve target(iWorkOrders) target(iWorkOrders) = c.Value iWorkOrders = iWorkOrders + 1 End If Next c End Sub -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "Paul B" wrote in message ... 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 ** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need HELP to modify code (need more automation)
"Paul B" wrote in message ... Bob, that works just like I need, thanks very much, That's great, I am pleased. I did not spot the lack of the space in the worksheet name but you were right there was a space in it, after some thinking I modified it some to take care of the sheet changing names next year and also added a date for the labor sheet so it can be saved each day, the code I used was this, don't know if it's the best way but it works :) , is this a good way to reference the day and year or is there a better way? For the labor sheet Const DATEFORMAT As String = "mm.dd.yyyy" Set oWbLabor = Workbooks("Daily Labor For " & Format(Now, DATEFORMAT) & ".xls") That's exactly how I would do it Paul (and I like the use of a constant<vbg) Now if I can just decipher HOW your code works :). Thanks again Paul B There is nothing cunning or particularly clever there, it is just highly structured code (cough, cough, splutter !"£$%^^), and I have seen many of your posting replies so I don't think you will have any trouble. Just sit down when you have a quiet period with a nice stiff gin martini (one glass full of gin, vermouth in the same room but not the same glass, preferably Bombay Sapphire or Plymouth gin), or a margarita,, or whatever your poison is, and work your way slowly through it. The alcohol won't help at all, but it will be enjoyable. And by the way, I have XL97 and XL2000 (even got XP now) on my home desktop machine so I have tried it with 97 as well as with 2000. Only a fairly cursory test, but I didn't see any problems. There is no XL2000 specific code that I am aware of (not ever sure what is XL2000 specific, SPLIT maybe but not much), so I didn't anticipate it being a problem and I don't think it is. Anyway, see you around some more. Best Regards Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify code | Excel Discussion (Misc queries) | |||
modify a line code | Excel Discussion (Misc queries) | |||
Modify Code | Excel Worksheet Functions | |||
automation code | Excel Discussion (Misc queries) | |||
How to modify the code for different type of input? | Excel Worksheet Functions |