know location after search

[obgle]

Hey there,

I have searched the whole internet (at least I feel like I did) and can't figure this out on my own. PLEASE help me!

I have a macro that works well so far but I'm stuck at one point. My macro searches a set of data (partnumbers) in one worksheet for a number (cvalue) out of another worksheet. If it finds the number (fvalue) I want it to copy the related quantity, which it would find in the next column. My code for searching looks like this:

Please Login or Register  to view this content.

And I find what I need but how do I now address the location where the partnumber (fvalue) is found to move the cursor from there? Unfortunately the found cell is not the active cell as I was hoping.

THX, Obgle

[mike7952]

Try this Offset 0 rows 1 Column. This will give the value in Column b where fvalue was found

Please Login or Register  to view this content.

[obgle]

Thanks for your help!
Unfortunately this gives the contend of the cell where fvalue was found.

AND I want to move the cursor from fvalues location. Is there any possibility to know its location. Something lice Address or so?
That would help me so much!

[jeffreybrown]

Try

Please Login or Register  to view this content.

[mike7952]

Im lost is this what you want?

Please Login or Register  to view this content.

[obgle]

Both of them give me errors. For me both of them look like what I want, the position of the cell where my number is found but I'm doing something wrong. My variables are Dim as Range, is that right together with this?

[mike7952]

If you can post your code in question then it would help us help you. Otherwise were just throwing darts blindfolded into the wind

[mike7952]

Better yet upload your workbook

[obgle]

Ok, here is my workbook. It is an endless loop right now because of my issues to find that location :-)

[mike7952]

Ok im looking at it now. So what exactly are you trying to do? I Probably would take the code out of the Workbook_Open Event.

[obgle]

The first sheet contains raw data. There are serial numbers in colum A and the quantity of those IDs in column C.
On the second sheet I want to work with those data and have some of the serial numbers in column A (differen order) and the matching quantity in column D.

Now I want to write a mocro that would start on top of column A (sheet 2) takes the serial number and searches for it in column A of sheet 1. If it finds the number I want it to write the matching quantity out of column c (sheet 1) into column D (sheet 2). If it doesn't find the number in the list I want it to write 0. and then do the same thing with the next serial number until the list on sheet 2 is over.

I guess it looks quite chaotic to you, but please be gentle since this is my first macro ever :-)

[mike7952]

Try this... remove the code you have in the Workbook_Open and Insert this into a Standard Module

Please Login or Register  to view this content.

[obgle]

Oh my, yours looks so much better than mine and it took you just minutes, where I worked days, lol!
Thank you so much, it works. Except one thing:
I writes 7815 into sheet2 D3, that is right and then it writes 13 into sheet2 D4 and that is not correct. It is the quantity of the part number one below the right one.

And would you do me huge favor and write some comments into the code what is happening where? I know that is asked a lot but I would love to understand and learn for the next time...

[obgle]

Ups, I just found out your code works perfectly! It was my mistake, sorry!

[obgle]

Thank you so much again! It works perfectly no matter what I do with the list of numbers, I can't believe you wrote that so quickly!
I appreciate your help already but would be VERY thankful for some comments in the code to understand what I'm doing ;-)

[mike7952]

no problem heres the code with some comments. What part of NC are you from?

Please Login or Register  to view this content.

[obgle]

THX!!! It was sooo frustrating the last days :-)

Originally I'm from Germany but living in Asheville, NC for one year now. I visited Florida last January and loved it (especially the Keys), which area do you live

[mike7952]

Asheville uh? Its really nice over there. I use to ride my motor cycle over that way going to Tenn/Blue Ridge Parkway. I use to live in Pinehurst/Southern Pines NC. I moved down to Florida about 3 years ago. About an hour from Orlando, in Lake Wales.

[mike7952]

Also have a brother living in Germany now. He was in the US Military, married a german girl and stayed over there..

[obgle]

Oh yes! This is the best location I know to ride a motorbike!

By the way, how do I mark a thread as SOLVED? Can't find the button...

[mike7952]

Click Thread Tools above your first post, select "Mark your thread as Solved"

[obgle]

Aha, worked!