hata olursa belirtilen komuta gitme

M

musak64

Katılım
2 Nisan 2006
Mesajlar
41
Excel Vers. ve Dili
Excel 2000 - Türkce
arkadaslar asagida bir makrom var. ve hata olustugunda alt satirlardaki komutlardan degil For k = 1 to 5 den yani kalan diger yerden devam etmesini istiyorum. yani
Workbooks.Open Filename:="E:\Program Files\Analizer MSRT\Excel\" & cells(k, 1) & ".xls" bu komut satirinda acilacak dosyayi bulamayinca sonraki dosya isimlerinden devam etmesini istiyorum.
Sub Makro33()

For k = 1 To 5
Workbooks.Open Filename:="E:\Program Files\Analizer MSRT\Excel\" & cells(k, 1) & ".xls"
End If
On Error Resume Next

Sheets.Add
Sheets("Sayfa1").cells(1, 1) = "HISSE"
Sheets("Sayfa1").cells(1, 2) = "LOT"
sat0 = Worksheets.Count
For i = 1 To sat0
Sheets(i).Select
If Range("a11") = "AÇIÐA SATIÞLAR" Then
sat = WorksheetFunction.CountA([a13:a500])

For j = 13 To sat + 12
Sheets("Sayfa1").Select
sat2 = WorksheetFunction.CountA([a1:a1500])
Sheets("Sayfa1").cells(sat2 + 1, 1) = Sheets(i).cells(j, 1)
Sheets("Sayfa1").cells(sat2 + 1, 2) = Sheets(i).cells(j, 2)
Next j
Else
End If
Next i

On Error Resume Next
Sheets.Add
Sheets("Sayfa2").cells(1, 2) = "TICKER"
Sheets("Sayfa2").cells(1, 1) = "HÝSSE SENEDÝNÝN ADI"
Sheets("Sayfa2").cells(1, 3) = "DATE"
Sheets("Sayfa2").cells(1, 4) = "TIME"
Sheets("Sayfa2").cells(1, 5) = "LOW"
Sheets("Sayfa2").cells(1, 6) = "HIGHT"
Sheets("Sayfa2").cells(1, 7) = "CLOSE"
Sheets("Sayfa2").cells(1, 8) = "VOLUME"
sat0 = Worksheets.Count
For l = 4 To sat0
Sheets(l).Select
If Range("c12") = "HÝSSE SENEDÝNÝN ADI" Then
sat = WorksheetFunction.CountA([b14:b500])

For m = 14 To sat + 13
Sheets("Sayfa2").Select
sat2 = WorksheetFunction.CountA([a1:a1500])

Sheets("Sayfa2").cells(sat2 + 1, 1) = Sheets(l).cells(m, 3)
Sheets("Sayfa2").cells(sat2 + 1, 2) = Sheets(l).cells(m, 2)
Sheets("Sayfa2").cells(sat2 + 1, 3) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C3"
Sheets("Sayfa2").cells(sat2 + 1, 4) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C2"
Sheets("Sayfa2").cells(sat2 + 1, 5) = Sheets(l).cells(m, 5)
Sheets("Sayfa2").cells(sat2 + 1, 6) = Sheets(l).cells(m, 6)
Sheets("Sayfa2").cells(sat2 + 1, 7) = Sheets(l).cells(m, 7)
Sheets("Sayfa2").cells(sat2 + 1, 8) = Sheets(l).cells(m, 11)
Sheets("Sayfa2").cells(1, 11) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C1"
Next m
Else
End If
Next l

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sayfa1!a:b").CreatePivotTable TableDestination:="", TableName:= _
"Özet Tablo 1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.cells(3, 2)
ActiveSheet.cells(3, 2).Select
ActiveSheet.PivotTables("Özet Tablo 1").SmallGrid = False
ActiveSheet.PivotTables("Özet Tablo 1").AddFields RowFields:="HISSE"
ActiveSheet.PivotTables("Özet Tablo 1").PivotFields("LOT").Orientation = _
xlDataField
Range("c13").Select
ActiveSheet.PivotTables("Özet Tablo 1").PivotFields("Sayý LOT").Function = _
xlSum
Application.Run ("ACIGASATIS3")
On Error Resume Next
Sheets("Sayfa2").Select
sat = WorksheetFunction.CountA([a1:a1500])
Sheets("Sayfa3").Select
sat0 = WorksheetFunction.CountA([b1:b1500])
For n = 2 To sat
For o = 5 To sat0 + 4
If Sheets("Sayfa2").cells(n, 1) = Sheets("Sayfa3").cells(o, 2) Then
Sheets("Sayfa2").cells(n, 9) = Sheets("Sayfa3").cells(o, 3)
Else
End If
Next o
Next n
Sheets("Sayfa2").Select
sat3 = WorksheetFunction.CountA([b1:b1500])
Range(cells(2, 2), cells(sat3, 8)).Select
Selection.Copy
Windows("CALIS.xls").Activate
Sheets("Sayfa1").Select
sat4 = WorksheetFunction.CountA([f1:f1500])
Range(cells(sat4 + 1, 6), cells(sat4 + 1, 6)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows(cells(k, 1) & ".xls").Activate
Sheets("Sayfa2").Select
ActiveWorkbook.SaveAs Filename:= _
"E:\Documents and Settings\YENI\Desktop\Yeni Klasör (4)\" & cells(1, 11) & ".XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Next k
End Sub
 
B

bedersu

Katılım
2 Mart 2005
Mesajlar
556
Excel Vers. ve Dili
Office 2013 Türkçe
on error goto burası:

deyin

burası: yazılan yere gider
 
M

musak64

Katılım
2 Nisan 2006
Mesajlar
41
Excel Vers. ve Dili
Excel 2000 - Türkce
For k = 1 To 5
on error goto burası:
Workbooks.Open Filename:="E:\Program Files\Analizer MSRT\Excel\" & cells(k, 1) & ".xls"







Sheets.Add
Sheets("Sayfa1").cells(1, 1) = "HISSE"
Sheets("Sayfa1").cells(1, 2) = "LOT"
sat0 = Worksheets.Count
For i = 1 To sat0
Sheets(i).Select
If Range("a11") = "AÇIÐA SATIÞLAR" Then
sat = WorksheetFunction.CountA([a13:a500])

For j = 13 To sat + 12
Sheets("Sayfa1").Select
sat2 = WorksheetFunction.CountA([a1:a1500])
Sheets("Sayfa1").cells(sat2 + 1, 1) = Sheets(i).cells(j, 1)
Sheets("Sayfa1").cells(sat2 + 1, 2) = Sheets(i).cells(j, 2)
Next j
Else
End If
Next i

On Error Resume Next
Sheets.Add
Sheets("Sayfa2").cells(1, 2) = "TICKER"
Sheets("Sayfa2").cells(1, 1) = "HÝSSE SENEDÝNÝN ADI"
Sheets("Sayfa2").cells(1, 3) = "DATE"
Sheets("Sayfa2").cells(1, 4) = "TIME"
Sheets("Sayfa2").cells(1, 5) = "LOW"
Sheets("Sayfa2").cells(1, 6) = "HIGHT"
Sheets("Sayfa2").cells(1, 7) = "CLOSE"
Sheets("Sayfa2").cells(1, 8) = "VOLUME"
sat0 = Worksheets.Count
For l = 4 To sat0
Sheets(l).Select
If Range("c12") = "HÝSSE SENEDÝNÝN ADI" Then
sat = WorksheetFunction.CountA([b14:b500])

For m = 14 To sat + 13
Sheets("Sayfa2").Select
sat2 = WorksheetFunction.CountA([a1:a1500])

Sheets("Sayfa2").cells(sat2 + 1, 1) = Sheets(l).cells(m, 3)
Sheets("Sayfa2").cells(sat2 + 1, 2) = Sheets(l).cells(m, 2)
Sheets("Sayfa2").cells(sat2 + 1, 3) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C3"
Sheets("Sayfa2").cells(sat2 + 1, 4) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C2"
Sheets("Sayfa2").cells(sat2 + 1, 5) = Sheets(l).cells(m, 5)
Sheets("Sayfa2").cells(sat2 + 1, 6) = Sheets(l).cells(m, 6)
Sheets("Sayfa2").cells(sat2 + 1, 7) = Sheets(l).cells(m, 7)
Sheets("Sayfa2").cells(sat2 + 1, 8) = Sheets(l).cells(m, 11)
Sheets("Sayfa2").cells(1, 11) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C1"
Next m
Else
End If
Next l

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sayfa1!a:b").CreatePivotTable TableDestination:="", TableName:= _
"Özet Tablo 1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.cells(3, 2)
ActiveSheet.cells(3, 2).Select
ActiveSheet.PivotTables("Özet Tablo 1").SmallGrid = False
ActiveSheet.PivotTables("Özet Tablo 1").AddFields RowFields:="HISSE"
ActiveSheet.PivotTables("Özet Tablo 1").PivotFields("LOT").Orientation = _
xlDataField
Range("c13").Select
ActiveSheet.PivotTables("Özet Tablo 1").PivotFields("Sayý LOT").Function = _
xlSum
Application.Run ("ACIGASATIS3")
On Error Resume Next
Sheets("Sayfa2").Select
sat = WorksheetFunction.CountA([a1:a1500])
Sheets("Sayfa3").Select
sat0 = WorksheetFunction.CountA([b1:b1500])
For n = 2 To sat
For o = 5 To sat0 + 4
If Sheets("Sayfa2").cells(n, 1) = Sheets("Sayfa3").cells(o, 2) Then
Sheets("Sayfa2").cells(n, 9) = Sheets("Sayfa3").cells(o, 3)
Else
End If
Next o
Next n
Sheets("Sayfa2").Select
sat3 = WorksheetFunction.CountA([b1:b1500])
Range(cells(2, 2), cells(sat3, 8)).Select
Selection.Copy
Windows("CALIS.xls").Activate
Sheets("Sayfa1").Select
sat4 = WorksheetFunction.CountA([f1:f1500])
Range(cells(sat4 + 1, 6), cells(sat4 + 1, 6)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows(cells(k, 1) & ".xls").Activate
Sheets("Sayfa2").Select
ActiveWorkbook.SaveAs Filename:= _
"E:\Documents and Settings\YENI\Desktop\Yeni Klasör (4)\" & cells(1, 11) & ".XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
burası:
Next k
End Sub

ilginize tesekkürler. bir defalik icin calisiyor ama. birdahaki hata da istenilen yere ikinci defa gitmiyor.benim istedigim döngü bitinceye kadar calissin.
 
M

musak64

Katılım
2 Nisan 2006
Mesajlar
41
Excel Vers. ve Dili
Excel 2000 - Türkce
Arkadaslar bunun mutlaka bir yolu vardir. anlayamadigim on error goto Burasi: döngü icersinde neden sadece bir defa calisiyor. yardimlarinizi bekliyorum.
 
B

bedersu

Katılım
2 Mart 2005
Mesajlar
556
Excel Vers. ve Dili
Office 2013 Türkçe
çünkü kodunuzun içerisinde bir de on error resume next var.Yani bir sonraki hata da yeni koda geçiyor.Burayı silin.Artık her hatada burası: yazan yere gider.
 
M

musak64

Katılım
2 Nisan 2006
Mesajlar
41
Excel Vers. ve Dili
Excel 2000 - Türkce
bedersu' Alıntı:
çünkü kodunuzun içerisinde bir de on error resume next var.Yani bir sonraki hata da yeni koda geçiyor.Burayı silin.Artık her hatada burası: yazan yere gider.
Genişletmek için tıkla ...
Evet onuda denedim ama yine ayni sadece bir defalik calisiyor. düzeltilmis son kod asagidaki gibi.
Sub Makro33()
For k = 1 To 5
On Error GoTo hata:

Workbooks.Open Filename:="E:\Program Files\Analizer MSRT\Excel\" & cells(k, 1) & ".xls"

Sheets.Add
Sheets("Sayfa1").cells(1, 1) = "HISSE"
Sheets("Sayfa1").cells(1, 2) = "LOT"
sat0 = Worksheets.Count
For i = 1 To sat0
Sheets(i).Select
If Range("a11") = "AÇIÐA SATIÞLAR" Then
sat = WorksheetFunction.CountA([a13:a500])

For j = 13 To sat + 12
Sheets("Sayfa1").Select
sat2 = WorksheetFunction.CountA([a1:a1500])
Sheets("Sayfa1").cells(sat2 + 1, 1) = Sheets(i).cells(j, 1)
Sheets("Sayfa1").cells(sat2 + 1, 2) = Sheets(i).cells(j, 2)
Next j
Else
End If
Next i

Sheets.Add
Sheets("Sayfa2").cells(1, 2) = "TICKER"
Sheets("Sayfa2").cells(1, 1) = "HÝSSE SENEDÝNÝN ADI"
Sheets("Sayfa2").cells(1, 3) = "DATE"
Sheets("Sayfa2").cells(1, 4) = "TIME"
Sheets("Sayfa2").cells(1, 5) = "LOW"
Sheets("Sayfa2").cells(1, 6) = "HIGHT"
Sheets("Sayfa2").cells(1, 7) = "CLOSE"
Sheets("Sayfa2").cells(1, 8) = "VOLUME"
sat0 = Worksheets.Count
For l = 4 To sat0
Sheets(l).Select
If Range("c12") = "HÝSSE SENEDÝNÝN ADI" Then
sat = WorksheetFunction.CountA([b14:b500])

For m = 14 To sat + 13
Sheets("Sayfa2").Select
sat2 = WorksheetFunction.CountA([a1:a1500])

Sheets("Sayfa2").cells(sat2 + 1, 1) = Sheets(l).cells(m, 3)
Sheets("Sayfa2").cells(sat2 + 1, 2) = Sheets(l).cells(m, 2)
Sheets("Sayfa2").cells(sat2 + 1, 3) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C3"
Sheets("Sayfa2").cells(sat2 + 1, 4) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C2"
Sheets("Sayfa2").cells(sat2 + 1, 5) = Sheets(l).cells(m, 5)
Sheets("Sayfa2").cells(sat2 + 1, 6) = Sheets(l).cells(m, 6)
Sheets("Sayfa2").cells(sat2 + 1, 7) = Sheets(l).cells(m, 7)
Sheets("Sayfa2").cells(sat2 + 1, 8) = Sheets(l).cells(m, 11)
Sheets("Sayfa2").cells(1, 11) = "='E:\Documents and Settings\YENI\Desktop\[CALIS.xls]Sayfa1'!R" & k & "C1"
Next m
Else
End If
Next l

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sayfa1!a:b").CreatePivotTable TableDestination:="", TableName:= _
"Özet Tablo 1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.cells(3, 2)
ActiveSheet.cells(3, 2).Select
ActiveSheet.PivotTables("Özet Tablo 1").SmallGrid = False
ActiveSheet.PivotTables("Özet Tablo 1").AddFields RowFields:="HISSE"
ActiveSheet.PivotTables("Özet Tablo 1").PivotFields("LOT").Orientation = _
xlDataField
Range("c13").Select
ActiveSheet.PivotTables("Özet Tablo 1").PivotFields("Sayý LOT").Function = _
xlSum


Sheets("Sayfa2").Select
sat = WorksheetFunction.CountA([a1:a1500])
Sheets("Sayfa3").Select
sat0 = WorksheetFunction.CountA([b1:b1500])
For n = 2 To sat
For o = 5 To sat0 + 4
If Sheets("Sayfa2").cells(n, 1) = Sheets("Sayfa3").cells(o, 2) Then
Sheets("Sayfa2").cells(n, 9) = Sheets("Sayfa3").cells(o, 3)
Else
End If
Next o
Next n
Sheets("Sayfa2").Select
sat3 = WorksheetFunction.CountA([b1:b1500])
Range(cells(2, 2), cells(sat3, 9)).Select
Selection.Copy
Windows("CALIS.xls").Activate
Sheets("Sayfa1").Select
sat4 = WorksheetFunction.CountA([f:f])
Range(cells(sat4 + 1, 6), cells(sat4 + 1, 6)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows(cells(k, 1) & ".xls").Activate
Sheets("Sayfa2").Select
ActiveWorkbook.SaveAs Filename:= _
"E:\Documents and Settings\YENI\Desktop\Yeni Klasör (4)\" & cells(1, 11) & ".XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
hata:
Next k
End Sub
 
Cevap yazmak için giriş yap yada kayıt ol.
Üst