VBA For Each Loop to Set Variables Using Offset

[gl0]

Hi everyone,

This is my first post and I really hope someone can help.

I'm trying to set some variable names in a For each loop.

I have a sheet called "test" with very simple data:

	A	B	C
1	Dog	Bone	Bark

I then have this VBA code:

Sub MySub()

Worksheets("test").Activate

Dim animal As String
Dim food As String
Dim sound As String

' Set a range
Dim rng As Range, cell As Range
    ActiveSheet.Range("A1").Select
    Set rng = Selection

' Loop thourgh the range assigning the variable names
    For Each cell In rng
    
        animal = Range(cell).Value
        food = Range(cell).Offset(1).Value
        sound = Range(cell).Offset(2).Value
        
    ' Show the variable names in message boxes
        MsgBox animal
        MsgBox food
        MsgBox sound
    
    Next cell

End Sub

I was hoping that three message boxes would pop:

Message Box #1: Dog
Message Box #2: Bone
Message Box #3: Bark

But instead I get this error:

Run-time error: '1004'
Method 'Range' of object'_Global failed

Would somebody be able to point me in the right direction?

Many thanks in advance.

Guy

 

[Fluff]

Hi & welcome to MrExcel.
How about

        animal = cell.Value
        food = cell.Offset(, 1).Value
        sound = cell.Offset(, 2).Value

 

[gl0]

Hi Fluff,

Still the same error unfortunately.

 

[Fluff]

That is not the code I posted
As Cell is declared as a range you just use it on it's own

 

[gl0]

Thanks so much Fluff! That works a treat. Sorry about that I've been seeing 'Range' in my sleep :D

 

[Fluff]

You're welcome & thanks for the feedback.