VBA For Each Loop to Set Variables Using Offset

gl0

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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:

ABC
1DogBoneBark

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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
        animal = cell.Value
        food = cell.Offset(, 1).Value
        sound = cell.Offset(, 2).Value
 

gl0

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff,

Still the same error unfortunately.

Screen Shot 2021-02-17 at 13.15.56.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
That is not the code I posted ;)
As Cell is declared as a range you just use it on it's own
 
  • Like
Reactions: gl0
Solution

gl0

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Thanks so much Fluff! That works a treat. Sorry about that I've been seeing 'Range' in my sleep :D
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,875
Messages
5,627,412
Members
416,245
Latest member
Xterminat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top