Look for Text and Copy Value to another Sheet

[moosmahna]

Hi everyone

First of all I have to say that this is a GREAT Forum. Lots of Information and material to learn
I have tried to find out how to make my idea possible but I had no Chance
So I ask you to help me
I upload a workbook with the description what I want to do
I hope i did a good explanation because my english is not the best one

Kind regards

[AB33]

"The file is corrupted, can not open it"

[moosmahna]

Ok, i saved it again and upload it

[AB33]

Sorry! The same problem.

[moosmahna]

Hello

I opened my workbook on two machines with no Problems. I have no idea what happened. But no Problem
Maybe you can help me with the code i wrote

Please Login or Register  to view this content.

This code works but i want to use this code for the range 10:2 to 10:16
But when i write the code for each cell then itīs a very big code
I tried to find out how to get this code a little smaller with the "for_each_cell_in_range" function, but I had no luck
Can you give me a little help please?

Thanks!!!

[AB33]

No, please save it with save as a new name and in new location. In the mean time, I am working on your code.

[moosmahna]

OK, here is the new saved workbook

[AB33]

Same problem!
Could you please contact the admin if the issue is with my PC, or your data? Could you ask someone else if they have the same problem.
I have adjusted the first part of your code, but I need to test it on the sample data as you have numbers not letters on your cells range.

[moosmahna]

Ok, I uploaded it now from another machine
There are two workbooks. One for the code and one with the my first question

Thanks

[AB33]

I managed to open the first attachment "workbook with codes". Are all the data on this book?

[moosmahna]

Hi,
there are some data in this workbook. I would be very happy if you can help me with making the code a Little smaller for the range 10:12 to 10:16. If i have a starting Point then i can write the code by myself (I hope so :-) )

[AB33]

This is my intake of your data.
You are testing for column "AJ". If one is true you are returning some times rows 9 column 2, other times row 9 and 4.
You are copying in to sheet DN column 5 which is column E, but not sure which is the first row in column E. Is it the next empty row? Please explain in more details.

[moosmahna]

Hi,

If in one of the cells in sheet "Tabelle 1" range B10 to S10 contains a value of the list in AJ then

if "B1" then go up 1 column (because there are the values to copy), same row, and go right to cells. copy These two values an paste it into DN 1 cells B5 and D5
if "B1A" then go up 1 column, same row and copy to DN 1 cell B5
Target for searching "B1A" is DN 1 cell B5
Target searching "B1E" is DN 1 cell C5
Target searching "P1" is DN 1 cell D5 and E5 (like "B1")
Target searching "P1A" is DN 1 cell D5
Target searching "P1E" is Dn 1 cell E5
Target searching "B2" is DN 1 cells F5 and G5 (like "B1")
Target searching "B2A is DN 1 cell F5
Target searching "B2E" is DN 1 cell G5

The Command Button called "Übernehmen" is to start the macro
The other CommandButton called "Dienstnehmer 1" is to Change the sheet
The CommandButton called "Command" was just a try from me to copy another timeline to cell B9 to S9

I hope my Explanation was a Little better

Thanks a lot for spending your time

[AB33]

Sorry! It is not clear to me.
The code you have referrers to rows 10, but row 10 is blank.
"If in one of the cells in sheet "Tabelle 1" range B10 to S10 contains a value of the list in AJ then"
But rows B10-S10 are blank. You mean row 9. Row 9 has time data, but column AJ has letters, so you can not find dates in column AJ.
How can you find time 7:00 in column AJ?
It would be easier for you to attach your desired result on spread sheet, as this might be easier than to explain in words.

[moosmahna]

Hi
the values for the cells are from the dropdownlist in each cell. the list is on AJ

[AB33]

Okay! Will let you know if I have further issues.

[moosmahna]

Thanks a lot

[AB33]

It took me a while to understand your request. The attached gives you a good start.

[moosmahna]

Thanks a lot! I will try it tomorrow as fast as possible!

[moosmahna]

WOOOWWWWWW! Thank you soooooooo much! What a great help!!!!!

[moosmahna]

Hi, itīs me again

I worked on the code and it works great. But i have a Problem. If i run the macro then the data are shown in the sheet "DN 1". BUT when i do some changes and run the code again, then the data are not in the right column and row. Can you please help me modify the code that changes on sheet "Tabelle1" row (B)10 only effects on the sheet "DN 1" range B5 to G5? "Tabelle 1" row (B)11 only effects on sheet "DN 1" range B6 to G6 and so on.
Thanks again

[moosmahna]

Can I do something like
Dim blabla as Range
Range = (B10:S10)

for example

[moosmahna]

Can I do something like
Dim blabla as Range
Range = (B10:S10)

for example

[moosmahna]

This did not work :-(

Please Login or Register  to view this content.

[moosmahna]

Please help

[AB33]

You need to post the sample where you are using the code, showing where the code fails.

[moosmahna]

Hi,

I upload the file where i wanted to change the code. As you can see the macro works now for all changes in
range B5 to G5 ("DN 1") when i put in same data in "Tabelle 1" range B10 to S10.
But how can i make this code better that it will do the changes on sheet "Tabelle 1" range B16 to S16
only on "DN 1" range B6 and G6 and so on.
Thanks a lot

[AB33]

You have change my original code which includes a case for the next row 16. You are now looping through a single range Select Case .Cells(10, i)
I did another case for row 16, but has now been removed.

[moosmahna]

I did this because when i made changes then the data has not been on the right place in "DN 1". they where on the next empty rows in "DN 1".
That is not good when I want to make some changes.

[AB33]

If you want to paste in to range B6 and G6, how can you do it with out next empty row? Is not it B6 is the next row after B5?You have removed the next empty row from the code.

[moosmahna]

What I mean is
i put in some data in "Tabelle 1" and run the macro. The data are shown in "DN 1" on the right place (B5 to G5 and B6 to G6)
Then i change the data in "Tabelle 1" and run the macro again. And now the data are not in range B5 to G6 and so on.
They start at B7.

[AB33]

They start at B7.
This is then the next empty row once B5 and B6 are filled.

[moosmahna]

Yes, BUT i want have the data from "Tabelle 1" range B10 to S10 only in "DN 1" range B5 to G5
The data from "Tabelle 1" range B16 to S16 only in "DN 1" range B6 to G6
The data from "Tabelle 1" range B22 to S22 only in "DN 1" range B7 to G7
because when i want make changes in "Tabelle 1" then the data should be at the right day in DN 1

Something happened now. I copied the code for 6 days ("Tabelle 1" B10 B16 B22 B28 B34)
Then i deleted the data manually in DN 1 so that all cells are clear and run the macro again
Then i got an error 1004 Method range for object _worksheet failed
What does this means?

[AB33]

I run the code again on the attached I sent you and did not get any error. You need to use the same code and show me which line does give you an error. Since we are using select case if your rows are out 1, you will get a different result. You can use if statement instead, but looks cumbersome and still need to loop through again.

[moosmahna]

Here is what I did.
I copied the code and wrote it for "Tabelle 1" B16, B22, B28 and B34
The changes from B16 B22 and B28 where placed right in "DN 1" when i run the macro multiple times.
But when i did the code for B34 and run the code multiple times then the data where in DN 1 not on the right
place.
You can see it on my upload

[moosmahna]

Then, when i delete the all the data in DN 1 and run the macro again I got the error.

[AB33]

The reason you keep getting an error is the code could not find the next empty Row (LR) in sheet Dn1. I have now changed it. I have also included a separate code which uses fixed next empty cell, which would not give you any error if the code could not find next empty row (LR).

[moosmahna]

Thanks a lot again!!! Works great!!!!!

[AB33]

Moosmahna,

You are welcome!

Could you please now close (Mark) this thread as solved? Go to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

[moosmahna]

One last question :-)
Is it possible to write a macro that Looks for colours and then gets the data?
for example:
"Tabelle 1" Cells B10 to E10 are green. The first green cell ("B10") is "B1A" and the last green cell ("E10") is
"B1E"

[AB33]

This is new request which does not relate to the original thread.
Please close this thread and start a new thread.