vba to offset an activecell based on cell value

Nurzhan

Board Regular
Joined
Dec 13, 2017
Messages
60
Hallo,
There's a range which filled with corresponding letters, each letter meaning in which direction the offset should take place. for example.
E4 = R
F4 = D
F5 = L
E5 to E8 = D
E9 = R
F9 to F7 = U
F6 = R
G6 = R

R = move right
D = move down
L = move left
U = move up

Basically it's a path that should lead to cell H6. And it strictly should follow the root of cells shown above.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
Try:
Code:
Do
  If ActiveCell.Value ="U" Then
    ActiveCell.Offset(-1,0).Activate
  Else
    If ActiveCell.Value ="D" Then
      ActiveCell.Offset(1,0).Activate
    Else
      If ActiveCell.Value ="L" Then
        ActiveCell.Offset(0,-1).Activate
      Else
        If ActiveCell.Value ="R" Then
          ActiveCell.Offset(0,1).Activate
        Else
          Exit Do
        End If
      End If
    End If
  End If
Loop
It should move the active cell until it is no longer one of those four values.
 
Last edited:

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,257
Office Version
  1. 2016
Platform
  1. Windows
Thanks Trevor_S! Great. I'm not experienced in VBA yet but these codes help me to master it.

Why do you need to do this ?

Here's another way :

Code:
Do
Select Case ActiveCell
    Case "R": ActiveCell(1, 2).Select
    Case "D": ActiveCell(2).Select
    Case "L": ActiveCell(1, 0).Select
    Case "U": ActiveCell(0).Select
    Case Else: Exit Do
End Select
Loop
 

Nurzhan

Board Regular
Joined
Dec 13, 2017
Messages
60

ADVERTISEMENT

Thanks footoo! This is even simpler.
 

Nurzhan

Board Regular
Joined
Dec 13, 2017
Messages
60
Dear footoo ! Can you clarify the steps in the code above? How come your code and the code below performs same things? Actually everyting is the same but offsetting values.
Code:
[/COLOR]DoSelect Case ActiveCell
    Case "R": ActiveCell.Offset(0, 1).Select
    Case "D": ActiveCell.Offset(1, 0).Select
    Case "U": ActiveCell.Offset(-1, 0).Select
    Case "L": ActiveCell.Offset(0, -1).Select
    Case Else: Exit Do
End Select
Loop[COLOR=#574123]

And see my coments besides the lines:
Code:
[/COLOR]Do
Select Case ActiveCell
    Case "R": ActiveCell(1, 2).Select  'active cell = E4. So it should move 1 row down and 2 column right, which is G5. G5 is empty, meaning it should end the loop.
    Case "D": ActiveCell(2).Select
    Case "L": ActiveCell(1, 0).Select
    Case "U": ActiveCell(0).Select
    Case Else: Exit Do
End Select
Loop[COLOR=#574123]

 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,257
Office Version
  1. 2016
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,286
Members
418,185
Latest member
snoogz2

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