Error handling Question.

[davidi]

I am trying to use a find statement that looks in column A for a certain number. If it does find the number, then the program then executes and continues on. If it does not find that number then I want to use another find statement to find another number. This goes on for a few numbers. Code is shown below.
If the program does not find the condition, normally I would get an error message, so to override that I put the Onerror goto ... It seems to me that in the one subroutine you cannot have more than one Onerror goto statement. Is that correct?

Is there a better way to handle error overiding so that the progam does not stop.

This is part of the program:

On Error GoTo 35
Cells.Find(What:="575", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Call HPaste
35 On Error GoTo 36
Cells.Find(What:="576", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Call HPaste
36 On Error GoTo 40
Cells.Find(What:="577", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Call SubHSelect
Call HPaste
GoTo 40



40 end sub

 

[Tom Urtis]

Well I asked for it.

That code is just too much to get through and try to fix with all the minefields in it.

You said you want a row to be inserted above where 575, 576, 577, 614, 615, and 616 are found. This will do that:

Dim strFind$, varFind As Variant
strFind = "575"
Do
Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart)
If Not varFind Is Nothing Then
strFind = Val(strFind) + 1
Rows(varFind.Row).Resize(1).Insert
'Call SubHSelect
'Call HPaste
Else
strFind = Val(strFind) + 1
End If
Loop While Val(strFind) <= 577
 
strFind = "614"
Do
Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart)
If Not varFind Is Nothing Then
strFind = Val(strFind) + 1
Rows(varFind.Row).Resize(1).Insert
'Call SubHSelect
'Call HPaste
Else
strFind = Val(strFind) + 1
End If
Loop While Val(strFind) <= 616

I commented out the 2 Call statements just to keep them visually in the code here. What you can try to do is modify those 2 macros by taking out whatever code they contain that inserts the rows; I did not see any such code. As you are more familiar with the workbook than I am, see how your macros elsewhere can assume to already take into account that the row has been inserted above the found cells.

 

[davidi]

Thanks Tom,

I had a wry chuckle to myself when you asked for the code. I am not the best at coding (self taught). I am just about to finish work so I will look again on Thursday to see how it goes.
Will advise you in due course.

 

[davidi]

Could not wait so I had to try.
The cursor still doe not move to the cell that has code 575 in it. As I said before, if I put the extention .Activate after the end of the find bracket ie

Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart).Activate

the cursor moves to the cell that has 575 but then I get error message 13.

The code you have given me does not seem to find 575 (even though it is there), move to it and activate it so that i can insert a row just before it.
Thanks Tom

 

[Tom Urtis]

I tested it before posting and it did work, example it found 575 if it was in column A and inserted a row above that.

The problem is all your other code, where you are selecting and activating just about everything, which often leads to other problems. And for sure, you cannot Activate a variable such as you are trying to do with
Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart).Activate

Try this in a new workbook, here is the code I posted as a full macro:

Sub Test1()
Dim strFind$, varFind As Variant
strFind = "575"
Do
Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart)
If Not varFind Is Nothing Then
strFind = Val(strFind) + 1
Rows(varFind.Row).Resize(1).Insert
'Call SubHSelect
'Call HPaste
Else
strFind = Val(strFind) + 1
End If
Loop While Val(strFind) <= 577
 
strFind = "614"
Do
Set varFind = Columns(1).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlPart)
If Not varFind Is Nothing Then
strFind = Val(strFind) + 1
Rows(varFind.Row).Resize(1).Insert
'Call SubHSelect
'Call HPaste
Else
strFind = Val(strFind) + 1
End If
Loop While Val(strFind) <= 616
End Sub

Now, in some new test workbook, stick that macro in a standard module.

Go to any worksheet, select range A1:A20, type in "XXX" (without the quotes) and press Ctrl+Enter. That will put XXX in cells A1:A20.

Now, in cell A5 enter 575.
In cell A12 enter 615.

Run the macro.

You will see a new row inserted above 575 (which will now reside in cell A6, meaning the new row is row 5) and a new row will reside above 615 (which will now reside in cell A14 because row 13 will be the new inserted row).

After seeing this, given how much code you have in those other macros, there's no pretty way for me to frame this, what I think you will need to sit back and see how this code you just ran does indeed insert a row where it is supposed to based on what you've said. Now it's the task of applying the concepts to your project.

So, insert the code where you need to in your macros, and then follow it up with whatever code is supposed to work with those newly created empty rows. Remember, your friend is the varFind variable. You don't need to select any cells or know where they are. You just need to know that the cell which contains the found number can be referenced as
Cells(varfind.row, 1)
because 1 is column A.

That means, depending on where exactly you add more code, the cell above the found cell will either be
Cells(varfind.row, 1)
if the next code you append the macro with will be after the row insertion,
or it will be
Cells(varfind.row - 1, 1)

I imagine this seems confusing at first but with what you are seemingly attempting to do, it will require a little more knowledge of how VBA works.

Sorry I could not give a less amorphous direction. You have a good skill set to build upon with VBA and I bet you will get this thing accomplished. However, I recommend at this point you do some trial and error as you pick up better VBA techniques, dissecting your code to rebuild the project piece by piece like you just tested with the insertion macro.

 

[davidi]

Tom

You are so right. When the curser did not move to the cell with the selected number I assumed that no row had been inserted - but it had. Now all I have to do is select newly inserted row so I can put a heading in it. Should not be hard but....

Thanks heaps

David