Excel：循环使用循环中的问题 [英] Excel: Problems in Looping Through Columns Using While Loop

问题描述

Excel宏VBA
我在Excel文件中有两张数据。我想循环遍历所有列和所有行以检查单元格是否为空。

Excel Macro VBA I have two sheets of data in an excel file. I want to loop through all the columns and all the rows to check if the cells are empty.

我正在尝试从TemplateSheet中替换一个单词。

DataSheet包含要替换的单词。

I am trying to replace a word from the "TemplateSheet".
"DataSheet" contains the words for replacement.

但是，似乎我的while循环在循环中不起作用。你可以帮忙吗？

 While Sheets("DataSheet").Cells(1, iColumn) <> ""

    While Sheets("DataSheet").Cells(iRow, 1) <> ""
       Dim sReplace As String
       sReplace = Sheets("DataSheet").Cells(iRow, 1)
       sTemplate = Sheets("TemplateSheet").Cells(1, 1)
       sFind = Sheets("DataSheet").Cells(1, iColumn)

       sTemplate = Replace(sTemplate, sFind, sReplace)

       MsgBox sTemplate

       iRow = iRow + 1

    Wend

    iColumn = iColumn + 1
Wend

感谢您的帮助。

推荐答案

如果你真的想去直到它变成一个空白，有一个CurrentRegion属性将这样做。

If you really want to go until it gets to a blank, there's a CurrentRegion property that will do just that.

Sub ReplaceCells()

    Dim rCell As Range
    Dim shTemplate As Worksheet
    Dim shData As Worksheet
    Dim sReplace As String, sTemplate As String, sFind As String

    Set shTemplate = Sheets("TemplateSheet")
    Set shData = Sheets("DatatSheet")

    For Each rCell In shData.Range("A1").CurrentRegion.Cells
        If Len(rCell.Value) > 0 Then
            sReplace = shData.Cells(rCell.Row, 1)
            sTemplate = shTemplate.Cells(1, 1)
            sFind = shData.Cells(1, rCell.Column)

            sTemplate = Replace(sTemplate, sFind, sReplace)

            MsgBox sTemplate
        End If
    Next rCell
End Sub

这篇关于Excel：循环使用循环中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！