PDA

View Full Version : On error Resume Next



jungix
08-24-2006, 10:40 AM
What does this do exactly, does it go to the end of the loop?

For i=1 To 100

....


Next i

I do a lot of stuff in a loop, but some times data may lack for some row, and an error than occurs. I would like the code to skip to the end of the loop and start again with the next integer in this case. will it work that way if I put "On Error Resume Next" at the start of the loop?

mdmackillop
08-24-2006, 11:40 AM
Hi Jungix,
Have a look at Johnske's article here
http://xlvba.3.forumer.com/index.php?showtopic=17

Bob Phillips
08-24-2006, 11:44 AM
What does this do exactly, does it go to the end of the loop?

For i=1 To 100

....


Next i

I do a lot of stuff in a loop, but some times data may lack for some row, and an error than occurs. I would like the code to skip to the end of the loop and start again with the next integer in this case. will it work that way if I put "On Error Resume Next" at the start of the loop?

Don't do that, test the data for validity and then take appropriate action.

jungix
08-24-2006, 12:08 PM
That was my idea. The problem is that I copy/paste about 20 cells each time to another worksheet.

If I need to test with a If Isnumber() for each time I'm afraid it will take a lot of time.

Norie
08-24-2006, 12:15 PM
What makes you think that?

Perhaps if you explained what you are doing in the loop and/or post the code we could help further?

mdmackillop
08-24-2006, 12:16 PM
There may be other ways to check. SpecialCells for example, which can select only cells of a certain type, that can then be processed.

jungix
08-24-2006, 12:34 PM
This is my code. I fill my first sheet with imported data first. Then I copy it to the second sheet, but sometimes the data can't be imported and I just have N/A in some cells, and some other have errors (some average can become #DIV/0), and when this happens I have an error of type mismatch when I want to copy.


Worksheets("1").Activate
lastvol3c = Cells(11, 2)
yesterday3c = Cells(13, 2)
oneweekago3c = Cells(17, 2)
percentile3c = Cells(9, 2)
lastvol3p = Cells(11, 3)
yesterday3p = Cells(13, 3)
oneweekago3p = Cells(17, 3)
percentile3p = Cells(9, 3)
lastvol1c = Cells(11, 4)
yesterday1c = Cells(13, 4)
oneweekago1c = Cells(17, 4)
percentile1c = Cells(9, 4)
lastvol1p = Cells(11, 5)
yesterday1p = Cells(13, 5)
oneweekago1p = Cells(17, 5)
percentile1p = Cells(9, 5)
lastvol6c = Cells(11, 6)
yesterday6c = Cells(13, 6)
oneweekago6c = Cells(17, 6)
percentile6c = Cells(9, 6)
lastvol6p = Cells(11, 7)
yesterday6p = Cells(13, 7)
oneweekago6p = Cells(17, 7)
percentile6p = Cells(9, 7)
volumecall = Cells(11, 8)
volumeput = Cells(11, 9)
volumecallaverage = Cells(9, 8)
volumeputaverage = Cells(9, 9)
ratio63 = Cells(11, 12)
percentile63 = Cells(9, 12)
average63 = Cells(3, 12)
nbstdev63 = Cells(1, 12)
dayly1cp = Cells(11, 13)
yesterday1cp = Cells(13, 13)
percentile1cp = Cells(9, 13)
dayly3cp = Cells(11, 11)
yesterday3cp = Cells(13, 11)
percentile3cp = Cells(9, 11)
dayly6cp = Cells(11, 10)
yesterday6cp = Cells(13, 10)
percentile6cp = Cells(9, 10)

Worksheets("2").Activate
Cells(i, 2) = lastvol3c
Cells(i, 3) = yesterday3c
Cells(i, 4) = oneweekago3c
Cells(i, 5) = percentile3c
Cells(i, 7) = lastvol3p
Cells(i, 8) = yesterday3p
Cells(i, 9) = oneweekago3p
Cells(i, 10) = percentile3p
Cells(i, 12) = lastvol1c
Cells(i, 13) = yesterday1c
Cells(i, 14) = oneweekago1c
Cells(i, 15) = percentile1c
Cells(i, 17) = lastvol1p
Cells(i, 18) = yesterday1p
Cells(i, 19) = oneweekago1p
Cells(i, 20) = percentile1p
Cells(i, 22) = lastvol6c
Cells(i, 23) = yesterday6c
Cells(i, 24) = oneweekago6c
Cells(i, 25) = percentile6c
Cells(i, 27) = lastvol6p
Cells(i, 28) = yesterday6p
Cells(i, 29) = oneweekago6p
Cells(i, 30) = percentile6p
Cells(i, 32) = volumecall
Cells(i, 33) = volumecallaverage
Cells(i, 34) = volumeput
Cells(i, 35) = volumeputaverage
Cells(i, 41) = ratio63
Cells(i, 42) = percentile63
Cells(i, 43) = average63
Cells(i, 44) = nbstdev63
Cells(i, 45) = dayly1cp
Cells(i, 46) = yesterday1cp
Cells(i, 47) = percentile1cp
Cells(i, 48) = dayly3cp
Cells(i, 49) = yesterday3cp
Cells(i, 50) = percentile3cp
Cells(i, 51) = dayly6cp
Cells(i, 52) = yesterday6cp
Cells(i, 53) = percentile6cp

Zack Barresse
08-24-2006, 01:40 PM
Why so many variables? Maybe ..

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("1").Sheets("Sheet1")
Set ws2 = Worksheets("2").Sheets("Sheet1")
ws2.Cells(i, 2).Value = ws1.Cells(11, 2).Value
ws2.Cells(i, 3).Value = ws1.Cells(13, 2)
ws2.Cells(i, 4).Value = ws1.Cells(17, 2)
ws2.Cells(i, 5).Value = ws1.Cells(9, 2)
ws2.Cells(i, 7).Value = ws1.Cells(11, 3)
ws2.Cells(i, 8).Value = ws1.Cells(13, 3)
ws2.Cells(i, 9).Value = ws1.Cells(17, 3)
ws2.Cells(i, 10).Value = ws1.Cells(9, 3)
ws2.Cells(i, 12).Value = ws1.Cells(11, 4)
ws2.Cells(i, 13).Value = ws1.Cells(13, 4)
ws2.Cells(i, 14).Value = ws1.Cells(17, 4)
ws2.Cells(i, 15).Value = ws1.Cells(9, 4)
ws2.Cells(i, 17).Value = ws1.Cells(11, 5)
ws2.Cells(i, 18).Value = ws1.Cells(13, 5)
ws2.Cells(i, 19).Value = ws1.Cells(17, 5)
ws2.Cells(i, 20).Value = ws1.Cells(9, 5)
ws2.Cells(i, 22).Value = ws1.Cells(11, 6)
ws2.Cells(i, 23).Value = ws1.Cells(13, 6)
ws2.Cells(i, 24).Value = ws1.Cells(17, 6)
ws2.Cells(i, 25).Value = ws1.Cells(9, 6)
ws2.Cells(i, 27).Value = ws1.Cells(11, 7)
ws2.Cells(i, 28).Value = ws1.Cells(13, 7)
ws2.Cells(i, 29).Value = ws1.Cells(17, 7)
ws2.Cells(i, 30).Value = ws1.Cells(9, 7)
ws2.Cells(i, 32).Value = ws1.Cells(11, 8)
ws2.Cells(i, 34).Value = ws1.Cells(11, 9)
ws2.Cells(i, 33).Value = ws1.Cells(9, 8)
ws2.Cells(i, 35).Value = ws1.Cells(9, 9)
ws2.Cells(i, 41).Value = ws1.Cells(11, 12)
ws2.Cells(i, 42).Value = ws1.Cells(9, 12)
ws2.Cells(i, 43).Value = ws1.Cells(3, 12)
ws2.Cells(i, 44).Value = ws1.Cells(1, 12)
ws2.Cells(i, 45).Value = ws1.Cells(11, 13)
ws2.Cells(i, 46).Value = ws1.Cells(13, 13)
ws2.Cells(i, 47).Value = ws1.Cells(9, 13)
ws2.Cells(i, 48).Value = ws1.Cells(11, 11)
ws2.Cells(i, 49).Value = ws1.Cells(13, 11)
ws2.Cells(i, 50).Value = ws1.Cells(9, 11)
ws2.Cells(i, 51).Value = ws1.Cells(11, 10)
ws2.Cells(i, 52).Value = ws1.Cells(13, 10)
ws2.Cells(i, 53).Value = ws1.Cells(9, 10)

mdmackillop
08-24-2006, 02:06 PM
Sub TestImport()
Dim rng As Range, i As Long, j As Long
Sheets("1").Activate
Set rng = Application.Union(Cells(11, 2), Cells(13, 2), Cells(17, 2), Cells(9, 2), Cells(11, 3), _
Cells(13, 3), Cells(17, 3), Cells(9, 3), Cells(11, 4), Cells(13, 4), Cells(17, 4), _
Cells(9, 4), Cells(11, 5), Cells(13, 5), Cells(17, 5), Cells(9, 5), Cells(11, 6), _
Cells(13, 6), Cells(17, 6), Cells(9, 6), Cells(11, 7), Cells(13, 7), Cells(17, 7))
Set rng = Application.Union(rng, Cells(9, 7), Cells(11, 8), Cells(11, 9), Cells(9, 8), Cells(9, 9), Cells(11, 12), _
Cells(9, 12), Cells(3, 12), Cells(1, 12), Cells(11, 13), Cells(13, 13), Cells(9, 13), _
Cells(11, 11), Cells(13, 11), Cells(9, 11), Cells(11, 10), Cells(13, 10), Cells(9, 10))
'debug check
rng.Interior.ColorIndex = 6
On Error Resume Next
rng.SpecialCells(xlCellTypeFormulas, 16) = "Err"
j = 1
For i = 0 To rng.Cells.Count - 1
If j Mod 5 = 0 Then j = j + 1
j = j + 1
Sheets("2").Cells(2, j) = rng(i).Value
Next
Sheets("2").Activate
End Sub

jungix
08-25-2006, 06:08 AM
Thank you for your help both of you.

Firefytr your solution avoids using variables. But what would happen in case I have a #N/A value? Will it copy the #N/A?

Malcolm your solution look nice but I don't understand everything. I would like to avoid a look because the columns I use may vary, and I will probably add/delete some of them afterwards. Your solution would only work until 30 I think.

Why are you using an array? Do you absolutely need it with "On error resume next?"

Maybe I wasn't clear enough, but I was just looking for something very simple like


For i = 1 To 200
Worksheets("1").Activate
lastvol3c = Cells(11, 2)
yesterday3c = Cells(13, 2)
oneweekago3c = Cells(17, 2)
percentile3c = Cells(9, 2)
lastvol3p = Cells(11, 3)
yesterday3p = Cells(13, 3)
oneweekago3p = Cells(17, 3)
percentile3p = Cells(9, 3)
lastvol1c = Cells(11, 4)
yesterday1c = Cells(13, 4)
oneweekago1c = Cells(17, 4)
percentile1c = Cells(9, 4)
lastvol1p = Cells(11, 5)
yesterday1p = Cells(13, 5)
oneweekago1p = Cells(17, 5)
percentile1p = Cells(9, 5)
lastvol6c = Cells(11, 6)
yesterday6c = Cells(13, 6)
oneweekago6c = Cells(17, 6)
percentile6c = Cells(9, 6)
lastvol6p = Cells(11, 7)
yesterday6p = Cells(13, 7)
oneweekago6p = Cells(17, 7)
percentile6p = Cells(9, 7)
volumecall = Cells(11, 8)
volumeput = Cells(11, 9)
volumecallaverage = Cells(9, 8)
volumeputaverage = Cells(9, 9)
ratio63 = Cells(11, 12)
percentile63 = Cells(9, 12)
average63 = Cells(3, 12)
nbstdev63 = Cells(1, 12)
dayly1cp = Cells(11, 13)
yesterday1cp = Cells(13, 13)
percentile1cp = Cells(9, 13)
dayly3cp = Cells(11, 11)
yesterday3cp = Cells(13, 11)
percentile3cp = Cells(9, 11)
dayly6cp = Cells(11, 10)
yesterday6cp = Cells(13, 10)
percentile6cp = Cells(9, 10)

Worksheets("2").Activate
Cells(i, 2) = lastvol3c
Cells(i, 3) = yesterday3c
Cells(i, 4) = oneweekago3c
Cells(i, 5) = percentile3c
Cells(i, 7) = lastvol3p
Cells(i, 8) = yesterday3p
Cells(i, 9) = oneweekago3p
Cells(i, 10) = percentile3p
Cells(i, 12) = lastvol1c
Cells(i, 13) = yesterday1c
Cells(i, 14) = oneweekago1c
Cells(i, 15) = percentile1c
Cells(i, 17) = lastvol1p
Cells(i, 18) = yesterday1p
Cells(i, 19) = oneweekago1p
Cells(i, 20) = percentile1p
Cells(i, 22) = lastvol6c
Cells(i, 23) = yesterday6c
Cells(i, 24) = oneweekago6c
Cells(i, 25) = percentile6c
Cells(i, 27) = lastvol6p
Cells(i, 28) = yesterday6p
Cells(i, 29) = oneweekago6p
Cells(i, 30) = percentile6p
Cells(i, 32) = volumecall
Cells(i, 33) = volumecallaverage
Cells(i, 34) = volumeput
Cells(i, 35) = volumeputaverage
Cells(i, 41) = ratio63
Cells(i, 42) = percentile63
Cells(i, 43) = average63
Cells(i, 44) = nbstdev63
Cells(i, 45) = dayly1cp
Cells(i, 46) = yesterday1cp
Cells(i, 47) = percentile1cp
Cells(i, 48) = dayly3cp
Cells(i, 49) = yesterday3cp
Cells(i, 50) = percentile3cp
Cells(i, 51) = dayly6cp
Cells(i, 52) = yesterday6cp
Cells(i, 53) = percentile6cp

Next i


My problem is a problem of scope, I don't want the "On error resume next" to apply for the whole code, but just for the loop. I have more code afterwards, and I don't want the code to ignore some errors that might occur as I'm still modifying/debugging my code.

johnske
08-25-2006, 06:27 AM
Putting On Error Resume Next before the loop will cause any errors in the loop to be skipped - this is commonly referred to as 'deferred error handling'. To cancel the On Error Resume Next statement so your code will break on later errors, put On Error Goto 0 immediately after the loop.

johnske
08-25-2006, 08:20 AM
BTW, On Error only refers to Visual Basic errors, it won't error out on a worksheet error. As XLD said, test your data for validity.

After looking thru your code, I see you also don't need to loop, you can do the same thing, save time and also check for worksheet errors in a manner similar to this...
Option Explicit

Sub trythis()
With Sheets("1")
If Not IsError(.[B11]) Then Sheets("2").[B1:B200] = .[B11]
If Not IsError(.[B13]) Then Sheets("2").[C1:C200] = .[B13]
If Not IsError(.[B17]) Then Sheets("2").[D1:D200] = .[B17]
If Not IsError(.[B9]) Then Sheets("2").[E1:E200] = .[B9]
If Not IsError(.[C11]) Then Sheets("2").[G1:G200] = .[C11]
'etc
'etc
'etc
End With
End Sub

Zack Barresse
08-25-2006, 08:28 AM
Eeeewwww! There's that mixing/matching of regular and SHN!!! :devil2: