Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help me Review Code - LOOP doesn't stop
Hello- Could you please help me review what I am writing wrong in the code
below? The problem I want to solve is that after the data is pasted, the program keeps on looping and it ends up deleting the correct information that it had initially pasted. I would like the program to stop after it pastes the information once. What do I need to change in the code to accomplish that? Thank you. Filo ------------------------------------------ Sub InsertDTBinMFR() Dim DeptName As String, wb As String Dim z As Long, x As Long wb = Workbook.Name Workbooks(wb).Activate Sheets(1).Select Worksheets.Add Count:=1, After:=Sheets(Sheets.Count) ActiveSheet.Name = "MTD-DTB" DeptName = Left(Sheets(1).Range("F1").Text, 5) Workbooks("drill testing.xls").Activate Sheets("DTB").Select For z = 1 To 20000 If Cells(z, "a").Value = DeptName Then Firstrow = Range("A" & z).Address For x = 20000 To 1 Step -1 If Range("A" & x).Value = DeptName Then lastrow = Range("I" & x).Address Range(Firstrow, lastrow).Select Selection.Copy Workbooks(wb).Activate Sheets(2).Select Range("A1").Select Selection.PasteSpecial Paste:=xlValues End If Next x End If Next z End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help me Review Code - LOOP doesn't stop
Filo,
Exit out of the For loop on the line after the paste operation. i.e. Selection.PasteSpecial Paste:=xlValues Exit For '<---insert this line to exit out of the current for loop -- Hope that helps. Vergel Adriano "Filo" wrote: Hello- Could you please help me review what I am writing wrong in the code below? The problem I want to solve is that after the data is pasted, the program keeps on looping and it ends up deleting the correct information that it had initially pasted. I would like the program to stop after it pastes the information once. What do I need to change in the code to accomplish that? Thank you. Filo ------------------------------------------ Sub InsertDTBinMFR() Dim DeptName As String, wb As String Dim z As Long, x As Long wb = Workbook.Name Workbooks(wb).Activate Sheets(1).Select Worksheets.Add Count:=1, After:=Sheets(Sheets.Count) ActiveSheet.Name = "MTD-DTB" DeptName = Left(Sheets(1).Range("F1").Text, 5) Workbooks("drill testing.xls").Activate Sheets("DTB").Select For z = 1 To 20000 If Cells(z, "a").Value = DeptName Then Firstrow = Range("A" & z).Address For x = 20000 To 1 Step -1 If Range("A" & x).Value = DeptName Then lastrow = Range("I" & x).Address Range(Firstrow, lastrow).Select Selection.Copy Workbooks(wb).Activate Sheets(2).Select Range("A1").Select Selection.PasteSpecial Paste:=xlValues End If Next x End If Next z End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help me Review Code - LOOP doesn't stop
Thank you!
"Vergel Adriano" wrote: Filo, Exit out of the For loop on the line after the paste operation. i.e. Selection.PasteSpecial Paste:=xlValues Exit For '<---insert this line to exit out of the current for loop -- Hope that helps. Vergel Adriano "Filo" wrote: Hello- Could you please help me review what I am writing wrong in the code below? The problem I want to solve is that after the data is pasted, the program keeps on looping and it ends up deleting the correct information that it had initially pasted. I would like the program to stop after it pastes the information once. What do I need to change in the code to accomplish that? Thank you. Filo ------------------------------------------ Sub InsertDTBinMFR() Dim DeptName As String, wb As String Dim z As Long, x As Long wb = Workbook.Name Workbooks(wb).Activate Sheets(1).Select Worksheets.Add Count:=1, After:=Sheets(Sheets.Count) ActiveSheet.Name = "MTD-DTB" DeptName = Left(Sheets(1).Range("F1").Text, 5) Workbooks("drill testing.xls").Activate Sheets("DTB").Select For z = 1 To 20000 If Cells(z, "a").Value = DeptName Then Firstrow = Range("A" & z).Address For x = 20000 To 1 Step -1 If Range("A" & x).Value = DeptName Then lastrow = Range("I" & x).Address Range(Firstrow, lastrow).Select Selection.Copy Workbooks(wb).Activate Sheets(2).Select Range("A1").Select Selection.PasteSpecial Paste:=xlValues End If Next x End If Next z End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how stop review tool bar always showing up | Excel Discussion (Misc queries) | |||
vba code to block review toolbar | Excel Programming | |||
How can I stop the Web and Review toolbar opening up randomly? | Excel Discussion (Misc queries) | |||
Code Review | Excel Programming | |||
VB6 with Excel Objects: Please review my code | Excel Programming |