PDA

View Full Version : Why cannot run the code



clif
08-14-2013, 07:54 AM
What is the error in below loops




Sub loop()

Dim a As Integer
Dim c As String
Dim d As Integer
Dim e As Integer

For a = 1 To Workbooks("update").Worksheets("update").Range("AY2").Value
c = a
e = a * 3
On Error GoTo label1:

For d = 2 To 1000
Windows(Workbooks("update").Worksheets("update").Range("E7").Value).Activate
Sheets("LSCH2").Select
Columns("A:A").Select


Selection.find(What:=Sheets(c).Cells(d, 1).Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False).Activate

ActiveCell.Select

ActiveCell.Offset(0, 13 + e).Value = Sheets(c).Cells(d, 2).Value
ActiveCell.Offset(0, 14 + e).Value = Sheets(c).Cells(d, 3).Value
ActiveCell.Offset(0, 15 + e).Value = Sheets(c).Cells(d, 4).Value

Next d

label1:

Next a

End Sub

SamT
08-14-2013, 09:03 AM
Sub Myloop() 'Loop is a Keyword and cannot be used otherwise

Dim a As Integer
Dim c As String
Dim d As Integer
Dim e As Integer
Dim Cel As Range

For a = 1 To Workbooks("update").Worksheets("update").Range("AY2").Value
c = a 'c is a String, a is an Integer. If Range("AY2") is not a number
'how is the line above working? Use c = CStr(a)
e = a * 3
On Error GoTo label1:

For d = 2 To 1000
'Windows(Workbooks("update").Worksheets("update").Range("E7").Value).Activate
'The next two lines negates the last part of the above line
'Sheets("LSCH2").Select
'Columns("A:A").Select

'This line replaces the three above
Workbooks("update").Sheets("LSCH2").Range("A:A").Select

'Do you have worksheets whose names are a simple number? c = a = Integer
Set Cel = Selection.Find(What:=Sheets(c).Cells(d, 1).Value)
'Probably not needed:
', After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True _
, SearchFormat:=False

'ActiveCell.Select 'Not needed
If Not Cel Is Nothing Then
Cel.Offset(0, 13 + e).Value = Sheets(c).Cells(d, 2).Value
Cel.Offset(0, 14 + e).Value = Sheets(c).Cells(d, 3).Value
Cel.Offset(0, 15 + e).Value = Sheets(c).Cells(d, 4).Value
End If
Next d

label1:

Next a

End Sub

If c is a Sheets index number, find another way to refer to the sheet, because the index number of any sheet can change without notice.


Sub Myloop() 'Loop is a Keyword and cannot be used otherwise

Dim a As Integer
Dim c As String
Dim d As Integer
Dim e As Integer
Dim Cel As Range

With Workbooks("update")
For a = 1 To .Sheets("update").Range("AY2").Value
c = CStr(a)
e = a * 3

'For d = 2 to last used cell in column "A"
For d = 2 To .Sheets("LSCH2").Cells(Rows.Count, 1).End(xlUp).Row
Set Cel = .Sheets("LSCH2").Range("A:A").Find(What:=Sheets(c).Cells(d, 1).Value)
If Not Cel Is Nothing Then
Cel.Offset(0, 13 + e).Value = Sheets(c).Cells(d, 2).Value
Cel.Offset(0, 14 + e).Value = Sheets(c).Cells(d, 3).Value
Cel.Offset(0, 15 + e).Value = Sheets(c).Cells(d, 4).Value
End If
Next d

Next a

End Sub