0 votes
in VBA by Expert (916 points)
edited by

Hello everyone


I have the numbers 2 - 5 - 8 - 11

How can I store any number in this sequence to the numbers 1 - 2 - 3 - 4?


Dim i&


x = ... >> Should be 1

Debug.Print x

I will put the variable i manually in the code


if i=2 then I want to debug.print x=1

if i=5 then I want to debug.print x=2

if i=8 then I want to debug.print x=3


How can I make a line that calculates that ..? I tried MOD but didn't get a clue

by Super Expert (3.2k points)
I'm not sure I understand the question. Can you edit the question and try to explain it again? I think it'll be helpful for people reading this.
by Expert (916 points)
I used simple english as possible as I can ...

The variable x result is related to the value of the variable i ...>> so if I entered i = 2, I expect the immediate window to display 1

if I entered i = 5, I expect the immediate window to display 2

if I entered i = 8, I expect the immediate window to display 3

if I entered i = 11, I expect the immediate window to display 4

and so on

1 Answer

+1 vote
by Super Expert (3.2k points)
selected by
Best answer

There are 2 ways to do this. 

Using the Mod Operator - Solution 1

If the pattern is always N+3, starting at 2, then you could use the VBA Mod operator. Your macro would look something like this:

Sub ModSequence()
Dim i&
i = 8
If i Mod 3 = 2 Then
    x = (i + 1) / 3
End If
Debug.Print x
End Sub

Because the variable i as manually entered and the mod is always equal to 2, you can determine how far into the sequence you are using some arithmetic expressions.

Storing the Sequence in an Array - Solution 2

You could also create a constant array function to store the sequence if the sequence is short enough. Then you would loop through that sequence searching for a match. Once you find it, store the index of the match in variable x. Here's a neat little VBA macro that does just that:

Sub ArraySequence()
Dim i&
i = 2
For j = LBound(ConstantArray) To UBound(ConstantArray)
    If i = ConstantArray(j) Then x = j + 1
Next j
Debug.Print x
End Sub
Private Function ConstantArray()
    ConstantArray = Array(2, 5, 8, 11)
End Function

Run the ArraySequence macro to see it in action. Notice the ConstantArray is in a standalone function because you can't declare constant arrays using the VBA Const keyword.

by Expert (916 points)
Thank you very much for this soution. The problem is that the ConstantArray would be too long .. so I was searching for a relation between both so as to get the value of x in an easy way
by Super Expert (3.2k points)
I've edited my answer to include a solution using the Mod operator, assuming the pattern is always N+3, starting at 2. This solution will work without requiring you manually enter the full pattern. Take a look at the new macro and let me know if it works for you.
by Expert (916 points)
That's what I am trying to do. That's awesome

Thank you very much Mr. Ryan

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.