CODEWITHSUNDEEP
vbaexcelloopsfor-loop
GT.
GT.

Reputation: 792

Trying to loops through multiple cells in VBA

I've searched online and found a few solutions, but none of them make sense to me. I'm wondering why this specifically doesn't work:

Dim rng As Range: Set rng = Range("A5:A10")
    For Each cell In rng
        Dim contents As String: contents = ThisWorkbook.Sheets("ROI's").Range("cell").Value
        MsgBox (contents)
    Next cell

(BTW this is within a larger macro which works)

It keep saying that the error is on the third line

Upvotes: 0

Views: 207

Answers (2)

epicUsername
epicUsername

Reputation: 1009

Sub try2()


Dim rng As Range
Dim cell As Range
Dim contents As String
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
Set rng = Range("A1:A10")

For Each cell In rng
    contents = ws.Range(cell.Address(0, 0)).Value
    MsgBox (contents)
    Next cell

End Sub

I've been practicing wtih various problems concerning VBA...the above is just a snippet synthesizing what all the fine people above me have said about making this work. My 2 cents, brackets or not around the contents variable, the result is the same.

Upvotes: 0

Gary Evans
Gary Evans

Reputation: 1880

In addition to Scott Craners answer, take the parenthesis away from around contents in MsgBox (contents), you are not placing it into a variable so it should not be enclosed.

Upvotes: 3

Related Questions