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:
I then have this VBA code:
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
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:
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