Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Hi,
I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
This should be close...
Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Jim,
Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
I have always felt, best to teach how to fish, rather than give a fish. What
it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
GB,
thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
So did Jim's solution fix your problem, or are you still fishing? :)
"Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Hi,
yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Could if I knew your copy and paste ranges.
This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Ohh and no problem. I think this area is great for novices and experts
alike. It's amazing the kind of help that can be found and even the kind of errors that can be pointed out by almost anyone, from a misplaced colon to an extra parantheses. At any rate, I often see people go for the code, and like to back it up with some understanding, or vice versa. Sometimes people really know the code but don't understand what they need to do to get where they want. *shrug* There are some very helpful people on here. I sometimes wonder what they do for their day job, that they can be here providing such great help so often. But then again, look at me. :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Sorry, as I said, bit of a novice, so column "A" is searched for a 'date' and
once found cells A5:E5 are copied each time the same 'date' has been found. All this information is then pasted (or shud I say, I want it pased on the same worksheet but from cells G3:K3 can't remember if you have my code , but here it is this far..... Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.EntireRow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub oh and one other thing, when I run this code and its finished, an error stating " err 424 object required" what all that about ?? thanks again for helping, without people like you, I would have to murder the fish !!!! "GB" wrote: Could if I knew your copy and paste ranges. This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
One programming tip that I recommend.
The very first line of any module or code for a Form, or anywhere that you are going to write VBA code: Option Explicit This particular command, will force you to declare each and every variable that you want to use. Now while that may sound like a pain? Think about the example you just copied for me. In your If rngFound/End If section, you call wks3.PrintOut. As far as I can tell from the snippet of code? There is no wks3 variable. If you do call it out somewhere else, then I'll continue looking, but that seems to be the first problem. You are referring to a variable that does not exist, or at least that you haven't assigned any meaningful data to. GB "Anthony" wrote: Sorry, as I said, bit of a novice, so column "A" is searched for a 'date' and once found cells A5:E5 are copied each time the same 'date' has been found. All this information is then pasted (or shud I say, I want it pased on the same worksheet but from cells G3:K3 can't remember if you have my code , but here it is this far..... Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.EntireRow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub oh and one other thing, when I run this code and its finished, an error stating " err 424 object required" what all that about ?? thanks again for helping, without people like you, I would have to murder the fish !!!! "GB" wrote: Could if I knew your copy and paste ranges. This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
So you want:
If the data found in row 5 equals the data searched then copy A5:E5 to G3:K3 else tell the user that it is not found. end if Such that, for every piece of matching data found, the small range is copied two rows up and starting enough to the right to have one "blank" row? Therefore if a match is found at say row 6 then paste to row 4. But following this sequence, if a match is found now in row 25 then paste in row 5. Correct? Because basically this thing is working, except that entire rows are being copied, rather than just ranges. May need to do a for each c in rngAllRecords kind of thing so that you can pull the row and column combination. Let me know if at least what I'm talking about is correct, then I can help you hone the code. "Anthony" wrote: Sorry, as I said, bit of a novice, so column "A" is searched for a 'date' and once found cells A5:E5 are copied each time the same 'date' has been found. All this information is then pasted (or shud I say, I want it pased on the same worksheet but from cells G3:K3 can't remember if you have my code , but here it is this far..... Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.EntireRow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub oh and one other thing, when I run this code and its finished, an error stating " err 424 object required" what all that about ?? thanks again for helping, without people like you, I would have to murder the fish !!!! "GB" wrote: Could if I knew your copy and paste ranges. This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Hi,
yep sorry I ment to take out the wks3.PrintOut bit as this wasn't required. your synopsis (spelling?) of my requirements are almost correct, I'll try and explain. The user inputs a date ie 8 October 2005 into the msg box this 'date' is then searched for in the 'adhoc database' worksheet through column A each time this same date is found I want cells A:G copied from the row and pasted into cells J18:M18 of 'Adhoc' worksheet. if the date is found again, copy the cells A:G and paste into cells J19:M19 of the 'Adhoc' worksheet, if the date is found again paste the data in cells A:G into cells J20:M20, and so on........ However if the input date is not found in the column search a simple message stating so to be shown. Also if at all possible, can this code also be adapted to automaticaly seach for a date that would equal 'TODAY' ????? In theror what I want seems quite simple enough but frustrating to master !! again...appreciate your help with this regards "GB" wrote: So you want: If the data found in row 5 equals the data searched then copy A5:E5 to G3:K3 else tell the user that it is not found. end if Such that, for every piece of matching data found, the small range is copied two rows up and starting enough to the right to have one "blank" row? Therefore if a match is found at say row 6 then paste to row 4. But following this sequence, if a match is found now in row 25 then paste in row 5. Correct? Because basically this thing is working, except that entire rows are being copied, rather than just ranges. May need to do a for each c in rngAllRecords kind of thing so that you can pull the row and column combination. Let me know if at least what I'm talking about is correct, then I can help you hone the code. "Anthony" wrote: Sorry, as I said, bit of a novice, so column "A" is searched for a 'date' and once found cells A5:E5 are copied each time the same 'date' has been found. All this information is then pasted (or shud I say, I want it pased on the same worksheet but from cells G3:K3 can't remember if you have my code , but here it is this far..... Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.EntireRow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub oh and one other thing, when I run this code and its finished, an error stating " err 424 object required" what all that about ?? thanks again for helping, without people like you, I would have to murder the fish !!!! "GB" wrote: Could if I knew your copy and paste ranges. This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
I have included below what I did with your code. The line is a little long,
it works, and perhaps there is something a little less "codey" but it is a programming technique that I have adapted to make control easier. Basically look at the stuff I added in just below your original copy command and just above your wks3.printout line (which is commented out at the moment, so that I wouldn't get prompted to print anything. :) ) Sorry it took me a little bit, I was trying to look for a new cordless phone, and get some paperwork in order to move. Option Explicit Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim lngNextRow As Long Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler lngNextRow = 18 Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address 'rngAllRecords.EntireRow.Copy rngDestination.EntireRow For Each c In rngAllRecords wks1.Range(wks1.Cells(c.Row, "a"), wks1.Cells(c.Row, "g")).Copy wks1.Range(wks1.Cells(lngNextRow, "J"), wks1.Cells(lngNextRow, "M")) lngNextRow = lngNextRow + 1 Next 'wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub "Anthony" wrote: Hi, yep sorry I ment to take out the wks3.PrintOut bit as this wasn't required. your synopsis (spelling?) of my requirements are almost correct, I'll try and explain. The user inputs a date ie 8 October 2005 into the msg box this 'date' is then searched for in the 'adhoc database' worksheet through column A each time this same date is found I want cells A:G copied from the row and pasted into cells J18:M18 of 'Adhoc' worksheet. if the date is found again, copy the cells A:G and paste into cells J19:M19 of the 'Adhoc' worksheet, if the date is found again paste the data in cells A:G into cells J20:M20, and so on........ However if the input date is not found in the column search a simple message stating so to be shown. Also if at all possible, can this code also be adapted to automaticaly seach for a date that would equal 'TODAY' ????? In theror what I want seems quite simple enough but frustrating to master !! again...appreciate your help with this regards "GB" wrote: So you want: If the data found in row 5 equals the data searched then copy A5:E5 to G3:K3 else tell the user that it is not found. end if Such that, for every piece of matching data found, the small range is copied two rows up and starting enough to the right to have one "blank" row? Therefore if a match is found at say row 6 then paste to row 4. But following this sequence, if a match is found now in row 25 then paste in row 5. Correct? Because basically this thing is working, except that entire rows are being copied, rather than just ranges. May need to do a for each c in rngAllRecords kind of thing so that you can pull the row and column combination. Let me know if at least what I'm talking about is correct, then I can help you hone the code. "Anthony" wrote: Sorry, as I said, bit of a novice, so column "A" is searched for a 'date' and once found cells A5:E5 are copied each time the same 'date' has been found. All this information is then pasted (or shud I say, I want it pased on the same worksheet but from cells G3:K3 can't remember if you have my code , but here it is this far..... Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.EntireRow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub oh and one other thing, when I run this code and its finished, an error stating " err 424 object required" what all that about ?? thanks again for helping, without people like you, I would have to murder the fish !!!! "GB" wrote: Could if I knew your copy and paste ranges. This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: Hi, I have the following code (donated) which will take a number input by the user and then seach a whole column for that corresponding number. Once found it will then copy/paste the whole of that rows data onto another worksheet. This works fine as there is never the same number in the column twice, what I now need is for the column to be searched and then each time the same number is found to copy each rows data onto a seperate sheet until all the 'input number' corresponding rows data has been pasted onto seperate rows in a new worksheet. For example, if the number input by the user is 12345 . The seach is made and its found that this number is shown in cells A3, A7, A10 I want all the data in cells A3:G3, A7:G7, and A10:G10 to be pasted into seperate rows in another worksheet. hope that makes sense, and here is the code I have.... Sub print_mon_jobcard() Dim i As Integer Dim iRow As Integer Dim Cel As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set Cel = wks1.Columns("B:B").Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Cel Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Exit Sub End If On Error GoTo err_handler iRow = Cel.Row wks1.Cells(iRow, 1).EntireRow.Copy Destination _ :=wks2.Cells(2, 1) wks3.PrintOut Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Hi,
Thanks for ur efforts, however the code doesn't run, there seems to a problem with this bit:- wks1.Range(wks1.Cells(lngNextRow, "J"), wks1.Cells(lngNextRow, "M")) as the entire row is in red which I think its saying there is something missing?? sorry to be a pain, but any ideas? many thanks "GB" wrote: I have included below what I did with your code. The line is a little long, it works, and perhaps there is something a little less "codey" but it is a programming technique that I have adapted to make control easier. Basically look at the stuff I added in just below your original copy command and just above your wks3.printout line (which is commented out at the moment, so that I wouldn't get prompted to print anything. :) ) Sorry it took me a little bit, I was trying to look for a new cordless phone, and get some paperwork in order to move. Option Explicit Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim lngNextRow As Long Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler lngNextRow = 18 Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address 'rngAllRecords.EntireRow.Copy rngDestination.EntireRow For Each c In rngAllRecords wks1.Range(wks1.Cells(c.Row, "a"), wks1.Cells(c.Row, "g")).Copy wks1.Range(wks1.Cells(lngNextRow, "J"), wks1.Cells(lngNextRow, "M")) lngNextRow = lngNextRow + 1 Next 'wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub "Anthony" wrote: Hi, yep sorry I ment to take out the wks3.PrintOut bit as this wasn't required. your synopsis (spelling?) of my requirements are almost correct, I'll try and explain. The user inputs a date ie 8 October 2005 into the msg box this 'date' is then searched for in the 'adhoc database' worksheet through column A each time this same date is found I want cells A:G copied from the row and pasted into cells J18:M18 of 'Adhoc' worksheet. if the date is found again, copy the cells A:G and paste into cells J19:M19 of the 'Adhoc' worksheet, if the date is found again paste the data in cells A:G into cells J20:M20, and so on........ However if the input date is not found in the column search a simple message stating so to be shown. Also if at all possible, can this code also be adapted to automaticaly seach for a date that would equal 'TODAY' ????? In theror what I want seems quite simple enough but frustrating to master !! again...appreciate your help with this regards "GB" wrote: So you want: If the data found in row 5 equals the data searched then copy A5:E5 to G3:K3 else tell the user that it is not found. end if Such that, for every piece of matching data found, the small range is copied two rows up and starting enough to the right to have one "blank" row? Therefore if a match is found at say row 6 then paste to row 4. But following this sequence, if a match is found now in row 25 then paste in row 5. Correct? Because basically this thing is working, except that entire rows are being copied, rather than just ranges. May need to do a for each c in rngAllRecords kind of thing so that you can pull the row and column combination. Let me know if at least what I'm talking about is correct, then I can help you hone the code. "Anthony" wrote: Sorry, as I said, bit of a novice, so column "A" is searched for a 'date' and once found cells A5:E5 are copied each time the same 'date' has been found. All this information is then pasted (or shud I say, I want it pased on the same worksheet but from cells G3:K3 can't remember if you have my code , but here it is this far..... Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.EntireRow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub oh and one other thing, when I run this code and its finished, an error stating " err 424 object required" what all that about ?? thanks again for helping, without people like you, I would have to murder the fish !!!! "GB" wrote: Could if I knew your copy and paste ranges. This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub -- HTH... Jim Thomlinson "Anthony" wrote: |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
Apparently I did not explain that that line (the copy line portion plus the
code you just highlighed.) must either all be on the same line, or an underscore (with a space before it) has to be at the end of the line that ends with .copy. Those two lines of code are written such that, it is to copy from one location (A:G) and paste to the J:M range. To do that the code must be on one continuous line, basically. GB "Anthony" wrote: Hi, Thanks for ur efforts, however the code doesn't run, there seems to a problem with this bit:- wks1.Range(wks1.Cells(lngNextRow, "J"), wks1.Cells(lngNextRow, "M")) as the entire row is in red which I think its saying there is something missing?? sorry to be a pain, but any ideas? many thanks "GB" wrote: I have included below what I did with your code. The line is a little long, it works, and perhaps there is something a little less "codey" but it is a programming technique that I have adapted to make control easier. Basically look at the stuff I added in just below your original copy command and just above your wks3.printout line (which is commented out at the moment, so that I wouldn't get prompted to print anything. :) ) Sorry it took me a little bit, I was trying to look for a new cordless phone, and get some paperwork in order to move. Option Explicit Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim lngNextRow As Long Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler lngNextRow = 18 Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address 'rngAllRecords.EntireRow.Copy rngDestination.EntireRow For Each c In rngAllRecords wks1.Range(wks1.Cells(c.Row, "a"), wks1.Cells(c.Row, "g")).Copy wks1.Range(wks1.Cells(lngNextRow, "J"), wks1.Cells(lngNextRow, "M")) lngNextRow = lngNextRow + 1 Next 'wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub "Anthony" wrote: Hi, yep sorry I ment to take out the wks3.PrintOut bit as this wasn't required. your synopsis (spelling?) of my requirements are almost correct, I'll try and explain. The user inputs a date ie 8 October 2005 into the msg box this 'date' is then searched for in the 'adhoc database' worksheet through column A each time this same date is found I want cells A:G copied from the row and pasted into cells J18:M18 of 'Adhoc' worksheet. if the date is found again, copy the cells A:G and paste into cells J19:M19 of the 'Adhoc' worksheet, if the date is found again paste the data in cells A:G into cells J20:M20, and so on........ However if the input date is not found in the column search a simple message stating so to be shown. Also if at all possible, can this code also be adapted to automaticaly seach for a date that would equal 'TODAY' ????? In theror what I want seems quite simple enough but frustrating to master !! again...appreciate your help with this regards "GB" wrote: So you want: If the data found in row 5 equals the data searched then copy A5:E5 to G3:K3 else tell the user that it is not found. end if Such that, for every piece of matching data found, the small range is copied two rows up and starting enough to the right to have one "blank" row? Therefore if a match is found at say row 6 then paste to row 4. But following this sequence, if a match is found now in row 25 then paste in row 5. Correct? Because basically this thing is working, except that entire rows are being copied, rather than just ranges. May need to do a for each c in rngAllRecords kind of thing so that you can pull the row and column combination. Let me know if at least what I'm talking about is correct, then I can help you hone the code. "Anthony" wrote: Sorry, as I said, bit of a novice, so column "A" is searched for a 'date' and once found cells A5:E5 are copied each time the same 'date' has been found. All this information is then pasted (or shud I say, I want it pased on the same worksheet but from cells G3:K3 can't remember if you have my code , but here it is this far..... Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.EntireRow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub oh and one other thing, when I run this code and its finished, an error stating " err 424 object required" what all that about ?? thanks again for helping, without people like you, I would have to murder the fish !!!! "GB" wrote: Could if I knew your copy and paste ranges. This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.Entirerow.Copy rngDestination |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help please
well what can I say, FANTASTIC !!!
after a few minor changes, it is just working perfectly! Thank you so much for all your time and effort thanks x10000 Anthony "GB" wrote: Apparently I did not explain that that line (the copy line portion plus the code you just highlighed.) must either all be on the same line, or an underscore (with a space before it) has to be at the end of the line that ends with .copy. Those two lines of code are written such that, it is to copy from one location (A:G) and paste to the J:M range. To do that the code must be on one continuous line, basically. GB "Anthony" wrote: Hi, Thanks for ur efforts, however the code doesn't run, there seems to a problem with this bit:- wks1.Range(wks1.Cells(lngNextRow, "J"), wks1.Cells(lngNextRow, "M")) as the entire row is in red which I think its saying there is something missing?? sorry to be a pain, but any ideas? many thanks "GB" wrote: I have included below what I did with your code. The line is a little long, it works, and perhaps there is something a little less "codey" but it is a programming technique that I have adapted to make control easier. Basically look at the stuff I added in just below your original copy command and just above your wks3.printout line (which is commented out at the moment, so that I wouldn't get prompted to print anything. :) ) Sorry it took me a little bit, I was trying to look for a new cordless phone, and get some paperwork in order to move. Option Explicit Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim lngNextRow As Long Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler lngNextRow = 18 Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address 'rngAllRecords.EntireRow.Copy rngDestination.EntireRow For Each c In rngAllRecords wks1.Range(wks1.Cells(c.Row, "a"), wks1.Cells(c.Row, "g")).Copy wks1.Range(wks1.Cells(lngNextRow, "J"), wks1.Cells(lngNextRow, "M")) lngNextRow = lngNextRow + 1 Next 'wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub "Anthony" wrote: Hi, yep sorry I ment to take out the wks3.PrintOut bit as this wasn't required. your synopsis (spelling?) of my requirements are almost correct, I'll try and explain. The user inputs a date ie 8 October 2005 into the msg box this 'date' is then searched for in the 'adhoc database' worksheet through column A each time this same date is found I want cells A:G copied from the row and pasted into cells J18:M18 of 'Adhoc' worksheet. if the date is found again, copy the cells A:G and paste into cells J19:M19 of the 'Adhoc' worksheet, if the date is found again paste the data in cells A:G into cells J20:M20, and so on........ However if the input date is not found in the column search a simple message stating so to be shown. Also if at all possible, can this code also be adapted to automaticaly seach for a date that would equal 'TODAY' ????? In theror what I want seems quite simple enough but frustrating to master !! again...appreciate your help with this regards "GB" wrote: So you want: If the data found in row 5 equals the data searched then copy A5:E5 to G3:K3 else tell the user that it is not found. end if Such that, for every piece of matching data found, the small range is copied two rows up and starting enough to the right to have one "blank" row? Therefore if a match is found at say row 6 then paste to row 4. But following this sequence, if a match is found now in row 25 then paste in row 5. Correct? Because basically this thing is working, except that entire rows are being copied, rather than just ranges. May need to do a for each c in rngAllRecords kind of thing so that you can pull the row and column combination. Let me know if at least what I'm talking about is correct, then I can help you hone the code. "Anthony" wrote: Sorry, as I said, bit of a novice, so column "A" is searched for a 'date' and once found cells A5:E5 are copied each time the same 'date' has been found. All this information is then pasted (or shud I say, I want it pased on the same worksheet but from cells G3:K3 can't remember if you have my code , but here it is this far..... Sub print_mon_jobcard() Dim i As String Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet Set wks1 = ThisWorkbook.Worksheets("adhoc database") Set wks2 = ThisWorkbook.Worksheets("adhoc database") i = InputBox("Please enter the date you are looking for") On Error Resume Next Set rngToSearch = wks1.Columns("A") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then MsgBox "No job with the number " & i & _ " has been found, please try again! " Else On Error GoTo err_handler Set rngFirst = rngFound Set rngAllRecords = rngFound Do Set rngAllRecords = Union(rngAllRecords, rngFound) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address rngAllRecords.EntireRow.Copy rngDestination wks3.PrintOut End If Exit Sub err_handler: MsgBox Error, , "Err " & Err.Number End Sub oh and one other thing, when I run this code and its finished, an error stating " err 424 object required" what all that about ?? thanks again for helping, without people like you, I would have to murder the fish !!!! "GB" wrote: Could if I knew your copy and paste ranges. This is a similar thing as if you highlighted say a range of five cells wide and one deep, said copy, then went to somewhere else, highlighed only three cells wide and one deep. Excel would tell you that the ranges were not the same size. Something about your selection for paste is a different size/method than your copy. "Anthony" wrote: Hi, yes it kinda works, however I get this error when I run the macro err 1004 The information cannot be pasted because the copy and the paste are are not the same size & shape. Try one of the following *Click a single cell, then paste *Select a rectangle thats the same size and shape then paste. I have made an exact copy of the worksheet where the data is collected from to paste it into but still get this error. Any help........?? many thanks "GB" wrote: So did Jim's solution fix your problem, or are you still fishing? :) "Anthony" wrote: GB, thanks for your 'wise' words, and yes that is exactly what I want. The reason I posted here is becasue I am a novice at VB code, and to write something to do as I requested would take me a lifetime. any donations, therefore wud be very much apreciated! rgds "GB" wrote: I have always felt, best to teach how to fish, rather than give a fish. What it sounds like you want to do is take data from sheet1 and place it on sheet2. For every occurrence of the search data in sheet1, add it to the list of items on sheet2. (Not replace the data on sheet2.) Because the location to store the data on sheet2 will change, you need to keep track of it somehow. (Variable, like 'long DestRowNum') Everytime you make a match, copy the data and increase DestRowNum by one. As for the search, there is an Excel VBA example. The example stores the row of the first search response, then repeats the search until the row returned equals the first response. Course, sometimes it's easier to just to lead a horse near the water. :) "Anthony" wrote: Jim, Thanks - I'll give that a go and let you know many thanks "Jim Thomlinson" wrote: This should be close... Sub print_mon_jobcard() Dim i As Integer Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Dim rngDestination As Range Dim rngAllRecords As Range Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet 'On Error GoTo err_handler Set wks1 = ThisWorkbook.Worksheets("monday's log") Set wks2 = ThisWorkbook.Worksheets("formula") Set wks3 = ThisWorkbook.Worksheets("jobcard") i = InputBox("Please enter the job number you wish to print a job card for") On Error Resume Next Set rngToSearch = wks1.Columns("B") Set rngDestination = wks2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) Set rngFound = rngToSearch.Find _ (What:=i, _ LookIn:=xlValues, _ LookAt:=xlWhole) If rngFound Is Nothing Then |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |