Macro to find matching value and do automatic action

[kosherboy]

Hello.
Below is an attempted code that should run automatically when a value is entered in any 2nd cell in column L. I would like the code to do the following, after a value is entered in, let's say cell L5, the code should use the value of the cell to the left of L5, which is K5, and copy the value of cell L5 to every cell in column L that has it's neighboring cell under column K matching cell K5. Hope this makes sense

Here is what i have so far but i can't get it to work:

Please Login or Register  to view this content.

Thanks in advance.

[karedog]

Hi,

Use array, it is very very much faster than range.find()
Look here : link
Using find(), you will need 9747ms, while using matrix (array) you will only need 66ms (0.68% of find()).

Please Login or Register  to view this content.

Regards

[kosherboy]

Hats off to you!! Unbelievable code! Thanks!

[karedog]

You are welcome. Many thanks for the reputation point.

Regards

[kosherboy]

Hi karedog,
Code is great but it's not letting the other automatic codes i have work. any ideas?
Here is my entire code, you will see in the middle of the code the one you helped me out with:

Please Login or Register  to view this content.

[karedog]

Hi,

Try to change the blue lines code to this :

Please Login or Register  to view this content.

Regards

[kosherboy]

Thanks.
I changed it but now all other codes work besides for this one

[karedog]

Oops, sorry :

Please Login or Register  to view this content.

[kosherboy]

It worked! I really appreciate your knowledge, thank you.

[karedog]

You are welcome, thanks.

[kosherboy]

Hi again
I am trying to make your code work for the target now being column M instead of L but can't get it to work. I don't fully understand how your code works but every way i tried tweaking it i hit a dead end. Can you help me make the code work for column M?

here's what i have:

Please Login or Register  to view this content.

[karedog]

Hi,

Since you shift one column to right, so should the code

Please Login or Register  to view this content.

Regards

[kosherboy]

Thanks but i meant only the target cell gets shifted one to the right. Meaning column K should remain the same, and instead of using column L as the active cell this time i would like column M to be the active cell.

[karedog]

I see.

This is because two cells left of triggered cell is changed to uppercase by these :

Please Login or Register  to view this content.

while remaining of the column K has not been changed to uppercase yet.

So the code should be changed to :

Please Login or Register  to view this content.

Regards

[kosherboy]

Once again, i am dumbfounded as to how you figured all that out!
Thank you, most appreciated

[kosherboy]

Hi karedog, i hope i'm not being to pushy but i have another request. I tried to tweak the code as much as i could to get the answer but am not being successful.
My question is what if i enter into the target cell a hyperlink, how can i get the other cells to copy that hyperlink and not just the value?
This is what i tried so far, see my tweak in blue:

Please Login or Register  to view this content.

[karedog]

Hi,

In that case, we cannot use array operation anymore, use AutoFilter method instead.

Please Login or Register  to view this content.

Regards

[kosherboy]

It worked like a charm! I can't thank you enough!
All the best to you

[kosherboy]

Hi karedog,
I feel bad i keep bugging you but this is seriously the last time...i hope
I was googling the upper bound function and matrix but i'm still not getting it 100 percent. It would be most appreciated if you can guide me to the right site where i can learn this new concept in vba.
Also, i am trying to incorporate into the code a target cell of column R instead of L and M. So i still want the "leftcell" to remain as column K but i want the target cell to be R. I thought i was understanding your code and that it would be easy for me to incorporate column R as the target cell but after the code executes it write the value in column Q??
Here is what i have so far:

Please Login or Register  to view this content.

Once again i apologize for returning to this post but i am really stuck!
Thank you in advance.

[kosherboy]

Hi karedog,
I got the code to work by playing around with it! But i would really like to know how it works. Here is the code, i highlighted in blue the part i tweaked.

Please Login or Register  to view this content.

[karedog]

Hi kosherboy,

No problem. Unfortunately I don't remember the website(s) that teach about this. I learned and collected this info from so many places, so I don't remember all of
them, sorry. But I am sure that if you googling using keywords like "Excel VBA array operations" you will get many of them.

One thing that could lead into confusion is maybe lbound() and ubound() function.

lbound() <lower bound> is to get index of the lowest member of the matrix while
ubound() <upper bound> is to get index of the higher member of the matrix

For example, a code like this :

Please Login or Register  to view this content.

You will get the lbound of the matrix is 3 and ubound of the matrix is 7.
This is an example for one dimension matrix.


Now the example for 2 dimensions matrix :

Please Login or Register  to view this content.

Since this is a multidimensional matrix, we must tell Excel which dimension we want to examine, by passing the dimension number to second argument of the function.

So for the first the example, it can actually be written as :

Please Login or Register  to view this content.

but since this is a one dimensional matrix, we can neglect the second argument completely.


Now about the lower bound values, if we don't specifically defined the lower bound value, Excel will start this from 0 or 1. By default it is start with 0 except you
use statement Option Base 1, this will start the lower bound value from 1.


And now about index in Excel range. Please fill some values in range A1:B2

Please Login or Register  to view this content.

The first line will produce the value of cell A1 while the second line will produce value of cell B2. So the index rule is different for cells property with offset
property (although both is filled with the same values that is (1,1) ).

When we assign a range to a matrix, the rule that used is cell's rule, so the first member of matrix will get index (1,1) <not offset's rule which is (0,0)>.

So now you have already know, that there is different value (it is exactly 1) when working with cells and working with offset.


Now we apply this to your problem :

Please Login or Register  to view this content.

You assign the LeftCell3 variable, seven cells to left from currenct cell, USING OFFSET METHOD.

Now this one,

Please Login or Register  to view this content.

You try to assign a value to matrix (which is USE CELL's RULE instead of offset's rule), but you pass the "7" value, while actually it must be "8".
Because you have already know the differences between offset's rule and cell's rule, so it must be clear enough for you now.

Sorry for the long post.

Regards

[kosherboy]

Please don't be sorry, you've gone out of your way in explaining Upper bound lower bound concept. I really appreciate it. Thank you so much

[kosherboy]

Hi karedog,
I ran into a little issue while trying to recreate your code into a different scenario. Your code has the target cell, matching cell and out-put range all on the ODD row, what if my target cell is on an even row and the matching cells are on the odd row and the output cells on an even row?

So below is the recreated code that uses the target cell that's in the even row and uses the matching cell which is in an ODD row, however, i am unable to get the output range to be in the EVEN row. Instead, the value gets copied into the ODD row. It would be really great and appreciated if you can help me figure out how to to get the output range to be in the EVEN rows as opposed to the ODD rows.
here is what i have so far:

Please Login or Register  to view this content.

[kosherboy]

Hi karedog, i solved it! See blue font below

Please Login or Register  to view this content.

[karedog]

Hi kosherboy,

Glad you can solve it by yourself, sorry I am late to respond, it has been (and still) busy for the last few days for me.

Regards