Clever Geek Handbook

Excel: problems with cycling columns using for loop

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

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

But it seems my while loop doesn't work when looping through the columns. You can help?

 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

Thanks for the help.

0


source to share


2 answers


If you really want to go until you get to empty, there is 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
+1


source


So you iterate over your columns and then iterate over your rows. However, your rows never go back to 1 when you move to the next column. Does your problem fix iRow = 1

to add before looping lines like this?



While Sheets("DataSheet").Cells(1, iColumn) <> ""
    iRow = 1
    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
0


source






More articles:


All Articles