Excel: Problems in Looping Through Columns Using While Loop

Question

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.

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

But it seems like my while loop doesn't work on looping through the columns. Can you help out?

 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.

Does it give an error? What are your iRow and iColumn variables initialized to? I also think that your While clauses need a .Value after the .Cells bit, so it looks like While Sheets("DataSheet").Cells(1, iColumn).Value <> "", it may be comparing an actual cell to an empty string. — RianBattle
– RianBattle, Commented Jun 5, 2015 at 18:00
Thanks for the accepted answer but I'm not totally sure if we've completely solved your problem yet. Is there data filled in for every cell in Row1 and Column1 on DataSheet for each column and row you want to check? If so, your while loops could still work. — Tony L.
– Tony L., Commented Jun 5, 2015 at 18:23
I've changed my answer to just add one line of code to your existing code. See below. — Tony L.
– Tony L., Commented Jun 5, 2015 at 18:39
Why do you set sTemplate to the first cell of TemplateSheet only to set it to something else two lines later? — LondonRob
– LondonRob, Commented Jun 5, 2015 at 18:45

Dick Kusleika · Accepted Answer · 2015-06-05 18:16:56Z

1

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

answered Jun 5, 2015 at 18:16

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony L. · Accepted Answer · 2015-06-12 15:28:19Z

0

So you are looping through your columns and then looping through your rows. However, your rows never get set back to 1 when you get to the next column. Does it fix your issue to add iRow = 1 before the rows loop like so?

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

edited Jun 12, 2015 at 15:28

answered Jun 5, 2015 at 18:13

Tony L.

19.7k8 gold badges72 silver badges68 bronze badges

Collectives™ on Stack Overflow

Excel: Problems in Looping Through Columns Using While Loop

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related