时间戳

首页 > 解决方案 > 循环遍历单元格范围

excel - 循环遍历单元格范围

问题描述

该代码旨在遍历指定范围内的所有单元格。下面是部分代码。Rng在 for 循环中使用似乎有错误。当Rng替换为指定范围时，例如Range("A1:B20")，它可以工作，但代码在

Cells(cell2.Row, LastCol1 + 1) = Application.VLookup(Range("A2:A1000"), strFileToOpen.Range("A10:C10000"), 3)

   Dim Rng, cell, cell1, cell2 As Range
   Dim strFileToOpen As Variant

    strFileToOpen = Application.GetOpenFilename _
    (Title:="Please choose the File", _
    FileFilter:="Excel Files *.xls* (*.xls*),")


    If strFileToOpen = False Then
        MsgBox "No file selected.", vbExclamation, "Sorry!"
        Exit Sub
    Else
        Workbooks.Open Filename:=strFileToOpen
    End If

            LastRow = Cells(Rows.Count, 1).End(xlUp).Row
            Rng = Range(Cells(5, 2), Cells(LastRow, LastCol))
            
            
            For Each cell2 In Rng
                If Trim(cell2.Value) <> "" Then
                    Cells(cell2.Row, LastCol1 + 1) = Application.VLookup(Range("A2:A1000"), strFileToOpen.Range("A10:C10000"), 3)
                End If
            Next

标签： excelvbafor-loop

解决方案

请注意，Dim Rng, cell, cell1, cell2 As Range仅声明cell2 As Range，但Rng, cell, cell1与Variant在 VBA 中一样，您需要为每个变量指定一个类型，否则Variant默认情况下是：

Dim Rng As Range, cell As Range, cell1 As Range, cell2 As Range

此外Rng是一个Object，因此必须使用Set

Set Rng = Range(Cells(5, 2), Cells(LastRow, LastCol))

最后确保您使用Option Explicit，因为您的变量LastCol1并LastCol没有定义，因此被视为0但列0不存在计数以1.

确保使用有意义的变量名。cell, , 之类的名字对每个程序员cell1来说都是一场噩梦。cell2每当你摔倒时，你需要给变量名编号，你可以确定你做错了。

最后，您的变量strFileToOpen只是文件的路径，因为Application.GetOpenFilename仅返回String. 而 aString没有 aRange所以strFileToOpen.Range("A10:C10000")不能工作。

因此你需要做类似的事情

Dim OpenedWorkbook As Workbook
Set OpenedWorkbook = Workbooks.Open(Filename:=strFileToOpen)

并使用

OpenedWorkbook.Worksheets("SpecifySheetName").Range("A10:C10000")

所以像下面这样

Dim strFileToOpen As Variant
strFileToOpen = Application.GetOpenFilename( _
                Title:="Please choose the File", _
                FileFilter:="Excel Files *.xls* (*.xls*),")

If strFileToOpen = False Then
    MsgBox "No file selected.", vbExclamation, "Sorry!"
    Exit Sub
Else
    Dim OpenedWorkbook As Workbook
    Set OpenedWorkbook = Workbooks.Open(Filename:=strFileToOpen)
End If

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("SheetName") 'or ThisWorkbook.ActiveSheet if the sheet name is not defined

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Dim WorkingRange As Range
Set WorkingRange = ws.Range(ws.Cells(5, 2), ws.Cells(LastRow, LastCol)) 'LastCol is not defined!
        
Dim Cell As Range        
For Each Cell In WorkingRange 
    If Trim(Cell.Value) <> vbNullString Then
        'LastCol is not defined!
        ws.Cells(Cell.Row, LastCol1 + 1) = Application.VLookup(ws.Range("A2:A1000"), OpenedWorkbook.Worksheet("DefineWorksheetName").Range("A10:C10000"), 3)
    End If
Next Cell

推荐阅读