Copy certain values (9 digits, numbers only) from range

[rain4u]

Right, I'm not sure where to start. It is probably good idea to check the attached file. I have a spreadsheet where I would like to extract some data. Namely I'm after numeric values that are exactly 9 digits. The information is spread across the sheet and I would like to get a list on numbers in Column A on sheet called "output". What makes it harder is that the numbers are within merged cells and there are rows of irrelevant data between the numbers. Positive side is that there is system or pattern.
The data should be extracted from the columns below (last row as per last used cell of column C):

Please Login or Register  to view this content.

Or just maybe the whole area AH9:IE(last row used) can be looked at in one go, and only the 9 digit numbers to be copied over.

The data should go to sheet "Output" and should be in A1:A (no header). Example sheet Output (After) is included.


if anyone feels up for the challenge and would like to help me out, I would be very thankful. I am getting better at things and I have created some wonderful macros at my work place but there is still so many things that I don't know how to do.



Cheers

[Marcol]

I can't follow your post, maybe arlu1201 and the "Tech Team" can help.

[rain4u]

Yep. Not very easy to explain.

Maybe I should esplained it this way.

If you look sheet Data, namely the area (cells) that is higlhlighted in yellow. The code should look/scan at that area, pick up all values that are numeric (9 digits to be exact) and paste them in a single list as demonstrated on example sheet "Output (after)"


I'm not sure how to explain it better. It doesn't help that I'm not English

But thank you for trying to help.
Anyone else?

[nilem]

Please Login or Register  to view this content.

[rain4u]

THANK YOU SO MUCH!
Absolutely fantastic code. I will be honest with you, I understand the code itself only partially but thats ok.
I did play around with the code and the spreadsheet.
I changed all the numbers so they would be in sequential order like below

100000001
100000002
100000003
100000004
100000005
100000006
100000007
100000008
100000009
100000010
etc
etc

So then after running the code I'm able to check the output to confirm if it is picking up all the data. So far everything is good.
As far as I can understand I can freely change x = .Range("AH9:IE" & .Cells(Rows.Count, "C") to anything else as long as the starting point, end point and the pattern stays the same. So for example I can easily change AH9 to G9 to extend the scan area to the left, or I could change IE to HV to reduce the area to the left. I tested it out, and it works correctly.

I did have to add error handler for the section below. If there are no blanks to be deleted, it errors out. Though this is very unlikely scenario but one can never know

Please Login or Register  to view this content.

I also played around by adding extra row on each section (potentially to add some other information in future etc) and I changed the code as below.

Please Login or Register  to view this content.

This still worked fine. Good stuff.

I do have 2 questions
1) Is it safe to assume that the code would also work if I would add 1 column to each section and change the code as below (providing that I would also change AH9:IE to accommodate the changes)

Please Login or Register  to view this content.

2) Looping through the work area in this sort of fashion got me thinking. By this sort of fashion I meant that there is a working area defined and then the code loops through certain pattern. Is it also possible to populate formulas to these tables to certain cells? If yes, could you help me with this. I will try to do an example spreadsheet.




Thank you again. I hope that one day I can get to a similar level as you are right now. It will probably take me another 100 years
Cheers

[rain4u]

Hi Nilem or anyone else who is interested.

Right, I have done a new spreadsheet. After this post I'm off to bed zzz

The working/looping area would be F9:IE (last row as per col C)
I guess it could be again something along these lines

Please Login or Register  to view this content.

Then it would would loop in following way:

Two cells down, if cell is blank then populate formula

Please Login or Register  to view this content.

loose the formula, keep the value.
If cell is Not blank then move Next
Move 9 cells to right

Rinse and repeat



I have attached the spreadsheet. The green cells are the target cells where the formula should populate the values. I have typed letters "X" on some random cells so its easy to test the macro to check if it overwrites the cells. I also did a example sheet (Data (After). Boy was that long and boring process to copy and then individually and paste the formulas.
At the moment I created a quick Index/Match formula but I intend to Create more complex formulas.


I would also be thankful if you could also advice me what bit of the code I would need to edit so the code will populate the values of the formulas regardless if the target cell is blank or not.


Could you or anyone else help me once more?
Any help is always appreciated.
Cheers.

[nilem]

Hi Rain4u. Try it

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[rain4u]

Amazing stuff!!! Thanks again!!!!!

You blew me away with this:

Please Login or Register  to view this content.

I have never ever thought about clearing contents based on the Interior color. Extremely interesting concept. This might come hande one day! It really shows that there ara almost no limits in excel except your own imagination.



Quick question.
with:

Please Login or Register  to view this content.

Is it possible to edit the code above so it will just populate the values and not the formulas.
Or is it more efficient to run the code above first and run the code below right after it:

Please Login or Register  to view this content.

Thanks again for the help. Spasiba!

[nilem]

Spasiba!

На здоровье

Simply combine these two macros.

Please Login or Register  to view this content.

[rain4u]

I forgot to thank you Nilem and I forgot to change the thread to solved.

Cheers

[rain4u]

Nilem I wonder if you can help me with something or if anyone else want's to help me out. (feel free to jump in).

I have now used quite a lot of the codes/methods that you have teached me, but I'm struggling to achieve something.
How could I edit the code below to use it to change the interior colors of the cells (almost to get it working as the same way as Conditional formating works). I would love to leanr this also for future reference should I want to use more than 3 colors.

Current code:

Please Login or Register  to view this content.

What I would like to achieve is:
If the target cell's value is 0 (exactly 0), then change the background to GREEN
If the target cell's value is 1 to 3 (e.g. 1,2,3), then change the background to AMBER (orange)
If the target cell's value is less than 0 (e.g. -1, -2, etc), then change the background to AMBER (orange)
If the target cell's value is more than 4 (inclusive, e.g. 4,5,6,etc), then change the background to RED

I'm sort of thinking:

Please Login or Register  to view this content.

I have attached spreadsheet as always with sheet "Example (after)"
I would appreciate if you could help me out.


Your friend
Rain

[nilem]

Hi, our friend Rain
Try it

Please Login or Register  to view this content.

[rain4u]

Awesome stuff! Thank you Nilem
How can I edit the code so if the target cell is blank it will have no interior color? Or if the target cell is blank, then it skips it and wont change the interior color?
I'm not sure if this is possible as it looks that blank cell equals 0 (zero), e.g. it picks up green interior.

Thank you for helping. Like I said before these codes are very handy.

[nilem]

Please Login or Register  to view this content.

[rain4u]

Works great!

Thank you my friend!