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?
Hi Jungix,
Have a look at Johnske's article here
http://xlvba.3.forumer.com/index.php?showtopic=17
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Don't do that, test the data for validity and then take appropriate action.Originally Posted by jungix
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.
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?
There may be other ways to check. SpecialCells for example, which can select only cells of a certain type, that can then be processed.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
[VBA]
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
[/VBA]
Why so many variables? Maybe ..
[vba]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)[/vba]
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
[VBA]
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
[/VBA]
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.
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.
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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...[vba]
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").[D1200] = .[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
[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Eeeewwww! There's that mixing/matching of regular and SHN!!!
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Posting Permissions
Reply With Quote
