Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification of Recorded Macro
If I am in the wrong newsgroup I apologize. Maybe someone can direct me to
the correct group. I have an excel workbook with one column of data on sheet 1. I have recorded a Macro that moves certain data from sheet 1 to specified columns on sheet 2 of the workbook . In some cases I needed to record several "find" i.e. EDIT-Find to locate on sheet 1 the information I need to move to sheet 2. My problem occurs certain months when the "find" does not locate the data I am searching for. If I was doing this manually, I would set a default value in the appropriate column of sheet 2 and continue. However when my macro runs and encounters this situation I receive a runtime error message and of course the Macro halts. My question is can I : 1. Detect the runtime error before stopping execution of the Macro by adding code to my Macro 2. Put a default value in the required column of sheet 2 3. Continue with the Macro at the point I received the runtime error I am new at Macros and VBA. Thank you in advance for your assistance. Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification of Recorded Macro
here's a code example:
the main procedure is teh FindDemo procedure. This simply calls the main routine, passing the value to look for. the error you have occurs when there's no cell containing the value sought. Excel raises an error. In this code, the object 'cell' is set to each find, thus initially, if there's at least on cell with the sought after value, then there will be a valid object. However, if no cells are found, the object isn't created , ie it IS NULL Option Explicit Sub FindDemo() FindCells "B" End Sub Sub FindCells(FindText As String) Dim cell As Range Dim startAddress As String Set cell = Sheet1.Cells.Find(FindText) If Not cell Is Nothing Then startAddress = cell.Address Do 'do something cell.Select Set cell = Sheet1.Cells.FindNext(cell) Loop While cell.Address < startAddress Else msgbox "No cells match " & FindText End If ' End Sub "Greg" wrote in message ... If I am in the wrong newsgroup I apologize. Maybe someone can direct me to the correct group. I have an excel workbook with one column of data on sheet 1. I have recorded a Macro that moves certain data from sheet 1 to specified columns on sheet 2 of the workbook . In some cases I needed to record several "find" i.e. EDIT-Find to locate on sheet 1 the information I need to move to sheet 2. My problem occurs certain months when the "find" does not locate the data I am searching for. If I was doing this manually, I would set a default value in the appropriate column of sheet 2 and continue. However when my macro runs and encounters this situation I receive a runtime error message and of course the Macro halts. My question is can I : 1. Detect the runtime error before stopping execution of the Macro by adding code to my Macro 2. Put a default value in the required column of sheet 2 3. Continue with the Macro at the point I received the runtime error I am new at Macros and VBA. Thank you in advance for your assistance. Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification of Recorded Macro
Patrick,
Thank you, I am reviewing the information you sent. Like I said I am new at Macros and VBA and do need to study what you sent to me. Thank you again. Greg "Patrick Molloy" wrote in message ... here's a code example: the main procedure is teh FindDemo procedure. This simply calls the main routine, passing the value to look for. the error you have occurs when there's no cell containing the value sought. Excel raises an error. In this code, the object 'cell' is set to each find, thus initially, if there's at least on cell with the sought after value, then there will be a valid object. However, if no cells are found, the object isn't created , ie it IS NULL Option Explicit Sub FindDemo() FindCells "B" End Sub Sub FindCells(FindText As String) Dim cell As Range Dim startAddress As String Set cell = Sheet1.Cells.Find(FindText) If Not cell Is Nothing Then startAddress = cell.Address Do 'do something cell.Select Set cell = Sheet1.Cells.FindNext(cell) Loop While cell.Address < startAddress Else msgbox "No cells match " & FindText End If ' End Sub "Greg" wrote in message ... If I am in the wrong newsgroup I apologize. Maybe someone can direct me to the correct group. I have an excel workbook with one column of data on sheet 1. I have recorded a Macro that moves certain data from sheet 1 to specified columns on sheet 2 of the workbook . In some cases I needed to record several "find" i.e. EDIT-Find to locate on sheet 1 the information I need to move to sheet 2. My problem occurs certain months when the "find" does not locate the data I am searching for. If I was doing this manually, I would set a default value in the appropriate column of sheet 2 and continue. However when my macro runs and encounters this situation I receive a runtime error message and of course the Macro halts. My question is can I : 1. Detect the runtime error before stopping execution of the Macro by adding code to my Macro 2. Put a default value in the required column of sheet 2 3. Continue with the Macro at the point I received the runtime error I am new at Macros and VBA. Thank you in advance for your assistance. Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification of Recorded Macro
Patrick,
I am sure what you sent me is good but I am having difficulties understanding. Please allow me to give you an example of some of the code generated by by the macro recorder and maybe you can give me some additional input on how to implement your suggestion. Sub CreateLoad() Sheets("source").Select Range("A1").Select Cells.Find(What="Adjustments", After:=ActiveCell, LookIn:=x1Formulas, LookAt:= xlWhole, SearchOrder:=xlByColumns, SearchDirection:=x1Next,_ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1,0).copy Sheet("target").Select Range ("a23").Select ActiveSheet.Paste The above is an example of how I am attempting to move data from my Source sheet to my Target sheet. I added the lines ActiveCell.Select ActiveCell.Offset(1,0).copy My question is how do I implement your suggestion in this code. Thank you for your patience. Greg "Greg" wrote in message ... Patrick, Thank you, I am reviewing the information you sent. Like I said I am new at Macros and VBA and do need to study what you sent to me. Thank you again. Greg "Patrick Molloy" wrote in message ... here's a code example: the main procedure is teh FindDemo procedure. This simply calls the main routine, passing the value to look for. the error you have occurs when there's no cell containing the value sought. Excel raises an error. In this code, the object 'cell' is set to each find, thus initially, if there's at least on cell with the sought after value, then there will be a valid object. However, if no cells are found, the object isn't created , ie it IS NULL Option Explicit Sub FindDemo() FindCells "B" End Sub Sub FindCells(FindText As String) Dim cell As Range Dim startAddress As String Set cell = Sheet1.Cells.Find(FindText) If Not cell Is Nothing Then startAddress = cell.Address Do 'do something cell.Select Set cell = Sheet1.Cells.FindNext(cell) Loop While cell.Address < startAddress Else msgbox "No cells match " & FindText End If ' End Sub "Greg" wrote in message ... If I am in the wrong newsgroup I apologize. Maybe someone can direct me to the correct group. I have an excel workbook with one column of data on sheet 1. I have recorded a Macro that moves certain data from sheet 1 to specified columns on sheet 2 of the workbook . In some cases I needed to record several "find" i.e. EDIT-Find to locate on sheet 1 the information I need to move to sheet 2. My problem occurs certain months when the "find" does not locate the data I am searching for. If I was doing this manually, I would set a default value in the appropriate column of sheet 2 and continue. However when my macro runs and encounters this situation I receive a runtime error message and of course the Macro halts. My question is can I : 1. Detect the runtime error before stopping execution of the Macro by adding code to my Macro 2. Put a default value in the required column of sheet 2 3. Continue with the Macro at the point I received the runtime error I am new at Macros and VBA. Thank you in advance for your assistance. Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification of Recorded Macro
ok I've added some code that drops the addresses of the found cells into
another worksheet FIND is circular - in otherwords it will find the same cells over and over, so in our code we save the address of the first found cell, then we FIND subsequent cells until Excel starts the find again at the first cell that we found Option Explicit ' ensures we don't mistype our variables! Sub FindDemo() ' our demo call to our routine FindCells "B" ' look for cells containg the letter B End Sub ' this is the main procedure for finding cells Sub FindCells(FindText As String) ' FindText will hold the text we're searching for, ie B Dim cell As Range ' for our FIND result Dim ws As Worksheet ' for our results to be saved Dim targetrow As Long ' increments for each found cell Dim startAddress As String ' used to stop excel's FIND looping forever! Set cell = Sheet1.Cells.Find(FindText) ' find the first cell containing a B If Not cell Is Nothing Then ' if there is one startAddress = cell.Address ' then save its address targetrow = 1 ' initialise the row number for the results sheet Set ws = Worksheets.Add ' add the results sheet Do ' this is the start of the FIND loop 'copy to another sheet ws.Cells(targetrow, 1) = cell.Address targetrow = targetrow + 1 Set cell = Sheet1.Cells.FindNext(cell) Loop While cell.Address < startAddress Else MsgBox "No cells match " & FindText End If ' End Sub "Greg" wrote in message .. . Patrick, I am sure what you sent me is good but I am having difficulties understanding. Please allow me to give you an example of some of the code generated by by the macro recorder and maybe you can give me some additional input on how to implement your suggestion. Sub CreateLoad() Sheets("source").Select Range("A1").Select Cells.Find(What="Adjustments", After:=ActiveCell, LookIn:=x1Formulas, LookAt:= xlWhole, SearchOrder:=xlByColumns, SearchDirection:=x1Next,_ MatchCase:=True).Activate ActiveCell.Select ActiveCell.Offset(1,0).copy Sheet("target").Select Range ("a23").Select ActiveSheet.Paste The above is an example of how I am attempting to move data from my Source sheet to my Target sheet. I added the lines ActiveCell.Select ActiveCell.Offset(1,0).copy My question is how do I implement your suggestion in this code. Thank you for your patience. Greg "Greg" wrote in message ... Patrick, Thank you, I am reviewing the information you sent. Like I said I am new at Macros and VBA and do need to study what you sent to me. Thank you again. Greg "Patrick Molloy" wrote in message ... here's a code example: the main procedure is teh FindDemo procedure. This simply calls the main routine, passing the value to look for. the error you have occurs when there's no cell containing the value sought. Excel raises an error. In this code, the object 'cell' is set to each find, thus initially, if there's at least on cell with the sought after value, then there will be a valid object. However, if no cells are found, the object isn't created , ie it IS NULL Option Explicit Sub FindDemo() FindCells "B" End Sub Sub FindCells(FindText As String) Dim cell As Range Dim startAddress As String Set cell = Sheet1.Cells.Find(FindText) If Not cell Is Nothing Then startAddress = cell.Address Do 'do something cell.Select Set cell = Sheet1.Cells.FindNext(cell) Loop While cell.Address < startAddress Else msgbox "No cells match " & FindText End If ' End Sub "Greg" wrote in message ... If I am in the wrong newsgroup I apologize. Maybe someone can direct me to the correct group. I have an excel workbook with one column of data on sheet 1. I have recorded a Macro that moves certain data from sheet 1 to specified columns on sheet 2 of the workbook . In some cases I needed to record several "find" i.e. EDIT-Find to locate on sheet 1 the information I need to move to sheet 2. My problem occurs certain months when the "find" does not locate the data I am searching for. If I was doing this manually, I would set a default value in the appropriate column of sheet 2 and continue. However when my macro runs and encounters this situation I receive a runtime error message and of course the Macro halts. My question is can I : 1. Detect the runtime error before stopping execution of the Macro by adding code to my Macro 2. Put a default value in the required column of sheet 2 3. Continue with the Macro at the point I received the runtime error I am new at Macros and VBA. Thank you in advance for your assistance. Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Modification in Recorded Macro | Excel Programming | |||
Recorded single column insert in macro. Macro inserts two not one | Excel Programming | |||
How to FIX Recorded Macro | Excel Discussion (Misc queries) | |||
Creating a macro which presses a button containing a recorded macro | Excel Programming |