时间戳

首页 > 解决方案 > Excel VBA 对于范围内的每个单元格似乎多次通过同一个单元格

excel - Excel VBA 对于范围内的每个单元格似乎多次通过同一个单元格

问题描述

“for-each cell in range”语句似乎多次通过同一个单元格。

请参阅屏幕截图。

它四次遍历具有单词“Product”的单元格，因为它被合并到四行。

有没有办法让它只运行一次，而不管工作表的设计如何（换句话说，我不喜欢使用它在编码时考虑到它在四行中合并的事实）。

优秀

Public Sub ProcessBeijingFile(Excel_UNC_Path As String)

    Dim src As Workbook

    Dim ProdPushWorkbook As Workbook

    Set ProdPushWorkbook = ActiveWorkbook


    Set src = Workbooks.Open(Excel_UNC_Path, True, True)

    Dim c As Range
    Dim r As Range
    Dim LastRow As Long

    Dim text As String

    src.Sheets("Page 1").Activate

    src.ActiveSheet.Range("A1").Select
    LastRow = src.ActiveSheet.Range("A30000").End(xlUp).Row
    text = LastRow
    text = "A2:BA" + CStr(text)

    Set r = Range(text)

    Dim i As Integer

    For i = 1 To MaxItems
        PONumber(i) = ""
    Next


    Dim PageCounter As Integer
    PageCounter = 0
    RecordCounter = 0

    Dim NextPONumber As String
    NextPONumber = ""


    For Each c In r



        If Left(Trim(c.Value), 5) = "PO No" Then
            NextPONumber = Trim(Replace(c.Value, "PO No.:", ""))
            NextPONumber = Trim(Replace(NextPONumber, "PO No:", ""))
        End If
            ....

标签： excelvbaforeachrangecell

解决方案

如果您不关心性能并且只想要简单的代码，下面将演示如何跳过 MergedCells。它在立即窗口中显示单元格 B1 中非空单元格的地址和值，直到到达空单元格。你需要的那种。

Option Explicit

Sub Sample()
    Dim oRng As Range
    Set oRng = Range("B1")
    Do Until IsEmpty(oRng)
        Debug.Print oRng.Address, oRng.Value
        Set oRng = oRng.Offset(1)
    Loop
    Set oRng = Nothing
End Sub

推荐阅读