# Get position of a number within a pattern sequence using VBA

in VBA
edited

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?

Example:

``````Dim i&

i=2

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 vote
by Super Expert (3.2k points)
selected

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)
+1
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)
+1
That's what I am trying to do. That's awesome

Thank you very much Mr. Ryan