CODEWITHSUNDEEP
excelvbaloopsfor-loop
user1541645
user1541645

Reputation: 13

Visual Basic Excel For Loop In Multiple Cells How To Use The Counter In The Range("")

This is what I have, so the H needs to be followed by the number of the cell, I want to use the counter i here, but it doesn't work. What am I doing wrong? :)

For i = 60 To 63

    Range("Hi").AddComment
    Range("Hi").Comment.Visible = False
    Range("Hi").Comment.Text Text:=""
    i = i + 1
    Range("Hi").Select
    i = i - 1
    Next
End Sub

Upvotes: 0

Views: 3981

Answers (4)

KFleschner
KFleschner

Reputation: 499

Would this work better for you? I've always had issues when the value of i starts chaging inside the loop code

For i = 60 To 63      
    With Range("H" & i)
        .Select 
        .AddComment     
        .Comment.Visible = False     
        .Comment.Text Text:=""  
    end with
Next 
End Sub

Upvotes: 0

danielpiestrak
danielpiestrak

Reputation: 5439

You should format it like this:

    For i = 60 To 63

        Range("H" & i).AddComment
        Range("H" & i).Comment.Visible = False
        Range("H" & i).Comment.Text Text:=""
        i = i + 1
        Range("H" & i).Select
        i = i - 1
    Next i
End Sub

The reason is that the letter H is a character and i is a variable. anything inside of double quotes "Hi" like that Excel will read as just a string of text.

When Excel reads a word or letter outside of quotes i it will assume it is a variable. The & character joins the two together as text.

This means that each time the loop runs Excel will read it as "H" and i and translate it to "H1", "H2", "H3", .... "H60" and input it into the Range() like you are looking for.

Upvotes: 0

Daniel
Daniel

Reputation: 13122

Use this: 

Range("H" & i)

As you wrote it, "Hi" does not use the variable i because you put it in quotes.

Upvotes: 1

jrad
jrad

Reputation: 3190

You need to do this instead:

For i = 60 To 63

    Range("H" & i).AddComment
    Range("H" & i).Comment.Visible = False
    Range("H" & i).Comment.Text Text:=""
    i = i + 1
    Range("H" & i).Select
    i = i - 1
    Next
End Sub

The & operator does concatenation in VBA.

Upvotes: 0

Related Questions