ElseIf function working from List to find word's pair

[makiwara]

Hi! I need some help with a nested if function.

There is a code which checkes for every word in my list and excutes some command based on the value of the cell. However, if the cell's value is on my list, then the "pair" of the word should be the cell's value which is stored on a list.

Example:
__________________________

If ..... 'some irrelevant code

' and here comes the thing:

ELseIf Cells(i,1).Value ="dog" then Cells(i,5).Value="Pine"
ElseIf Cells(i,1).Value="cake" then Cells(i,5).Value= "Banana"
...

And so on.
So I have a list and I want to insert the code above into 1 "ElseIf" part, instead of writing more hundred lines of code for each word in my list.

// my list:
dog Pine
cake Banana
cacti Apple
pet Fruit
... and so on (106 pairs)
___________________________

Do you have any idea?

It would be great if my list could be stored inside my VBA code as a collection ( ? I don't know much aboit it)
If this is not possible, then I make a new worksheet containing my list in columns A-B)

Thank you for your help!

Have a nice day!

 

[JoeMo]

If you put your list into a range say from A2:B107. That range can be used to quickly create a 2-D array containing all the words and their pairs. Something like this:

Dim WordsAndPairs as Variant, i as long, j as long
WordsAndPairs = Range("A2:B107").Value
For i = 1 to LastRow
For j = 1 to Ubound(WordsAndPairs,1)
If Cells(i,1).Value =WordsAndPairs(j,1) then 
Cells(i,5).Value=WordsAndPairs(j,2)
Exit For
End If
Next j
Next i

 

[makiwara]

THank you for your quick reply! However it doesnt succed to use it properly. Im really sorry for being analfhabet.
I am a beginner, so its easy for me to follow my way of coding and very hard to modify your code, since you are professionals:D This array usage is unfortnately mystery for me, but i will work on it.

I hope that you can help me! Thank you for your patience! Have a nice day!

What I've got:
...
sub xy()

row = Cells(Rows.Count, 1).End(xlUp).row 'active worksheet
For i = 2 To row Step 1

If Cells(i,2).Value="n" then
If Right(Cells(i,1).Value,2)= "sa" then
Cells(i,1).Value = "Orange"

ELseIf Right(Cells(i,1).Value,2) = "xa" then
Cells(i,1).Value = "Zitrone"

ELseIf

AND HERE COMES THE PART I'M LOOKING FOR
(If Cells(i,1).Value is in Worksheets("1").Range("A2:A107)" then find it's pair as mentioned above in ("B2:B107"), and that will be Cells(i,5).Value

Else: .....

End if
End If
Next

End Sub

If you put your list into a range say from A2:B107. That range can be used to quickly create a 2-D array containing all the words and their pairs. Something like this:

Dim WordsAndPairs as Variant, i as long, j as long
WordsAndPairs = Range("A2:B107").Value
For i = 1 to LastRow
For j = 1 to Ubound(WordsAndPairs,1)
If Cells(i,1).Value =WordsAndPairs(j,1) then 
Cells(i,5).Value=WordsAndPairs(j,2)
Exit For
End If
Next j
Next i

 

[makiwara]

Solved thank you working perfect

 

[JoeMo]

Solved thank you working perfect

You are welcome - thanks for the reply.