0 votes
in VBA by Expert (892 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 (892 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 (892 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 (892 points)
That's what I am trying to do. That's awesome

Thank you very much Mr. Ryan

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


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