Modification of Recorded Macro

[Greg]

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

 

[Patrick Molloy]

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]

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

 

[Greg]

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

 

[Patrick Molloy]

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