如何为每个循环向下移动1行直到单元格为空 [英] how to move down 1 row for every loop until cell empty
问题描述
如何为每个循环向下移动1行，直到A列中的单元格为空?
How do I move down 1 row for every loop until cell empty in column A?
我需要从第5行复制到另一个工作簿，然后循环到下一行(Row6)，直到内容为空.
I need to start on Row 5 copy to another workbook then loop to the next row (Row6) until contents are empty.
这是我的代码
Sub Macro3()
'''
Do
''GRAB A ROW
Windows("theFILE2.working.xlsm").Activate
Rows("5:5").Select
Selection.Copy
Workbooks.Open "D:\folder1\folder2\Projects\The FILES\New folder\OVERVIEW TEMPLATE(macro edition)(current).xlsm"
Windows("OVERVIEW TEMPLATE(macro edition)(current).xlsm").Activate
Sheets("LISTS").Select
Rows("4:4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Windows("OVERVIEW TEMPLATE(macro edition)(current).xlsm").Activate
Sheets("PLANT OVERVIEW").Select
''SAVE AS
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
FileName1 = Range("N1").Value
FileName2 = Range("A1").Value
Path = "D:\folder1\folder2\Projects\The FILES\theFILES\" & FileName1 & "\"
ActiveWorkbook.SaveAs Filename:=Path & FileName2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
Loop
End Sub
谢谢你！
推荐答案
我看到您是VBA的新手，并且有些概念很快就可以掌握.在Excel中记录宏是找出如何在Excel中执行操作的好方法.但是，Excel的执行方式也存在一些缺点.这里有一些概念会有所帮助:
I see you're new to VBA, and there are some concepts you're picking up pretty quickly. Recording macros in Excel is a great way to find out how you could do something in Excel. However, there are some drawbacks to the way Excel does it also. Here are a few concepts that will help:
-
不要不使用选择， ActiveCell ， ActiveSheet ，选择，激活等，除非绝对必要.我知道这就是Excel中的Macro Recorder所做的，但是如果您做的不正确，它会导致错误，特别是当您开始使用多个工作簿时！
Don't use Selection, ActiveCell, ActiveSheet, Select, Activate, etc. unless you absolutely have to. I know that's what the Macro Recorder in Excel does, but if you don't do it exactly right, it can cause errors, especially when you start working with multiple workbooks!
最好分配一个 object ，然后使用该对象执行您想做的事情.在下面的代码中，我将 Workbooks 和 worksheets 分配给对象，并使用它们来完成工作. 范围也是常用的对象.
Much better to assign an object, and use that object to do what you want to do. In the code below, I assigned the Workbooks and worksheets to objects and used those to get stuff done. Ranges are also common objects to use.
与此相关，请确保始终完全限定您的对象.例如，您可以编写如下代码:Var1 = Cells(1, 1).Value，但是它将从活动工作表中的单元格A1中获取值，而不一定是您想要的工作表或工作簿.更好的方式是这样写:Var1 = wksSource.Cells(1, 1).Value我确实为您的源工作簿指定了一个工作表名称"Sheet1"-将其更改为要从中复制的工作表的实际名称.
Related to that, make sure to always fully qualify your objects. For example, you can write code like this: Var1 = Cells(1, 1).Value but it will get the value from cell A1 in the Active Worksheet, not necesarily the worksheet or workbook you intended. Much better to write it this way: Var1 = wksSource.Cells(1, 1).Value I did specify a sheet name "Sheet1" for your source workbook - change it to the actual name of the sheet you're copying from.
我将最常见的字符串分配给顶部的 Constants .在将每个字符串分配给一个常量与仅使用内联字符串之间存在一种平衡(例如，有些人可能将诸如"LISTS"之类的工作表名称分配给一个常量)，但是如果它们只使用一次并且放在显眼的位置，我不用担心为其分配一个常量.但是，尤其是在多个位置使用该值时，使用常量可以更轻松地将代码重新用于类似的任务.我还在源路径中添加了一个常量，尽管如果工作簿已经打开，则不需要这样做.
I assigned the most common strings to Constants at the top. There's a balance between assigning every string to a constant and using only in-line strings (for example, some might assign the sheet names like "LISTS" to a constant), but if they're only used once and in a prominent place, I don't worry about assigning a constant for it. But especially when the value is used multiple places, a constant makes it easier for when you want to re-use the code for a similar task. I also put a constant in there for the Source Path, although that's not required if the workbook is already open.
我还在顶部声明了所有变量-一些语言和程序员的用法有所不同，但是我希望能够看到开始时使用的是什么.
I also declared all the variables at the top - some languages and programmers do it differently, but I like to be able to see what's being used at the beginning.
在 Do ... Loop 上注意 While 说明符.仅当当前行的第一列中有值时才会循环.
Notice the While specifier on your Do ... Loop. This will only loop while there is a value in the first column of the current row.
这是我为您的任务编写代码的方式:
Here's how I would write the code for your task:
Sub Macro3()
Dim SourceRow As Long
Dim DestRow As Long
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FullFileName As String
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wksDest2 As Worksheet
Const scWkbSourcePath As String = "D:\folder1\folder2\Projects\" ' For example
Const scWkbSourceName As String = "theFILE2.working.xlsm"
Const scWkbDest1Path As String = "D:\folder1\folder2\Projects\The_FILES\New_folder\"
Const scWkbDest1Name As String = "OVERVIEW TEMPLATE_macro edition_current_.xlsm"
Const scWkbDest2Path As String = "D:\folder1\folder2\Projects\The_FILES\theFILES\"
Set wkbSource = Workbooks(scWkbSourceName)
Set wksSource = wkbSource.Sheets("Sheet1") ' Replace Sheet1 with the sheet name
SourceRow = 5
DestRow = 4
Do While wksSource.Cells(SourceRow, 1).Value <> ""
' Open the template workbook
Set wkbDest = Workbooks.Open(scWkbSourcePath & scWkbDest1Name)
Set wksDest = wkbDest.Sheets("LISTS")
''COPY A ROW
wksSource.Rows(SourceRow).Copy Destination:=wksDest.Rows(DestRow)
Application.CutCopyMode = False
With wksDest.Rows(DestRow).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
wkbDest.Activate
Set wksDest2 = wkbDest.Sheets("PLANT OVERVIEW")
''SAVE AS
FileName1 = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( _
Replace(wksDest2.Range("N1").Value _
, ".", "_") _
, "/", "_") _
, "\", "_") _
, "?", "_") _
, "|", "_") _
, "<", "_") _
, ">", "_") _
, ":", "_") _
, "*", "_") _
, """", "_")
FileName2 = wksDest2.Range("A1").Value
Path = scWkbDest2Path & FileName1 & "\"
If Len(Dir(Path, vbDirectory)) = 0 Then
MkDir Path
End If
FullFileName = Path & FileName2 & ".xlsx"
wkbDest.SaveAs Filename:=FullFileName, FileFormat:=xlOpenXMLWorkbook
wkbDest.Close
' Best practice to set objects to Nothing before re-using an object variable
Set wksDest = Nothing
Set wksDest2 = Nothing
Set wkbDest = Nothing
' Move down 1 row for source sheet
SourceRow = SourceRow + 1
Loop
End Sub
编辑
有关文件夹和文件名字符的一些注意事项和我学到的东西:
Edit
Some notes and things I learned regarding Folder and File name characters:
- 尽管可以在文件名中使用括号 ，但我无法保存您的原始文件名-但删除括号可以解决此问题.
- 由于您是根据(可能是肮脏的)数据创建文件和文件夹名称的，因此您应该清理(删除或替换为 _ )那些名称中不能使用的字符: \ / | ? < > >: * "
- 我在Microsoft页面上找到了命名文件，路径和命名空间:
- Although parentheses can be used in filenames, I wasn't able to get your original filename to save - but removing the parentheses solved the problem.
- Since you're creating file and folder names from (potentially dirty) data, you should clean up (remove or replace with _) the characters that can't be used in those names: \ / | ? < > : * "
- I found this on a Microsoft page for Naming Files, Paths, and Namespaces:
请勿在文件名或目录名后加空格或句点.
Do not end a file or directory name with a space or a period.
-
尽管在文件名中允许使用句号(.)，但不能将其作为文件夹名的最后一个字符，通常在文本字符串中找到该文件夹名.此外，它可能会造成混淆，有时会在文件名之内引起问题，因此，我建议将其全部替换.
Although it is allowed inside a file name, a full stop (.) cannot be the last char of a folder name, which is generally where you find it in a text string. Besides, it can be confusing and occasionally cause problems within a file name, so I'd recommend replacing them all.
- Trim()函数可用于删除文件夹名称末尾的空格 .请注意，在字符串中，它还会将一行中的多个空格更改为单个空格.
- The Trim() function can be used to remove spaces at the end of a folder name. Be aware that within the string, it also changes multiple spaces in a row to a single space.
尤其是因为您是根据数据创建文件夹的，因此在将文件保存到该文件夹之前，需要确保该文件夹存在. MkDir 是此命令.
Especially since you're creating folders from data, you need to make sure the folder exists before saving a file to it. MkDir is the command for this.
这篇关于如何为每个循环向下移动1行直到单元格为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
