For in VBA Matrix

Asked

Viewed 633 times

3

I have a database composed of two vectors: u=(a,b,c) and v=(1,2,3) In another spreadsheet, I have two empty vectors x and y. I need you to complete an entry of x (coming from u), the code return me the corresponding entry of v in x.

For example, if x = (b,c,b), then y =(2,3,2)

Thank you!

array vba matrix for

  • Hi Mariana. Have you tried using the functions vlookup or hlookup?

  • Hello Luiz, actually my doubt is on how to get a return with the data of the second vector. All vectors are already defined. In vector x, I will type a value, and if this value is equal to one of the inputs of vector u, then I want it to return me the corresponding input of vector y in vector v.

  • I understood later, so I removed the comment and posted another. Add one screenshot how is your spreadsheet. But an idea is you use one of the functions of lookup that I mentioned to search for the letter and return the value. You probably don’t need VBA. One more example: http://blog.luz.vc/excel/como-usar-funcao-procv-vlookup-excel/

  • I am opting for VBA because the vectors will be very large

  • If Excel can store/represent, it makes no difference. Excel’s native functions work well enough. VBA is only necessary when you need to build something that you can’t do only using native Excel resources. :)

  • 1

    Okay, I’ll try, thanks for the help!

  • Not at all. If you can solve it, please post an answer yourself with the detailed solution. So you also help other people who may have the same doubt in the future. :)

  • P.S.: I’m imagining that the values of each component of the vectors are in separate columns or rows, and so you can use the functions of lookup quietly. If it’s just a string, you’ll need to separate everything (and then, in this case, you might even need VBA). That’s why I said, post more details on how the contents of your file are.

Show 3 more comments

1 answer

1

Create an ex database.:

A1 B1
x   1
y   2
z   3

In A1 create a list-type data validation:

=$A$2:$A$4

Go to the Formulas tab, Name manager:

new: x refers to B2

new: y refers to B3

new: z refers to B4

in B1 create Data validation list type:

=Indireto(substituir(A1;"_";" ")

Ready, what you choose in A1, will be corresponding option in B1. but not necessarily A1 should be suspended list, just in case to ensure that the value of A1 will have corresponding in B1.

If you’re gonna do it for vba will be very extensive.

Browser other questions tagged array vba matrix for

You are not signed in. Login or sign up in order to post.