Help needed in adding people from one workbook to another and listing when they're working

[s3a]

Edit #2: The latest macro is in my second post. Running this macro adds the names to "this.xlsm" but not properly. Additionally, it doesn't state who's working or not (by placing a "W" in the time/date and person name intersection).

I attached two sample workbooks stripped of private information (and, the macro is in "this.xlsm").

Here is the (functional but incomplete) macro I currently have in "this.xlsm" listed here for your convenience.:

Please Login or Register  to view this content.

Could someone please show me how to extract a person's name (the data in the K column of "other.xlsm"), place it horizontally to the right of "Loto Quebec" in "this.xlsm" if the column E of "other.xlsm" says "LQ" and if it says anything else to place it to the right of "S&P" (by shifting everything in that row as of the column where the new person is going to be added) and then put a W at every intersection in "this.xlsm" of the date and time the particular person is working at. Put differently, a W should be placed everywhere that is within either the [Reg start, Reg end] (columns L and M of "other.xlsm") or [Inst start, Inst end] (columns T and U of "other.xlsm") interval (or both).

If more information is needed, tell me and I will give it to you.

Any input would be greatly appreciated!

Edit #1: You should run the macro once to notice what it does and understand what this thread is about.

[s3a]

Here is my currently most up-to-date macro.:

Please Login or Register  to view this content.

Running this macro adds the names to "this.xlsm" but not properly. Additionally, it doesn't state who's working or not (by placing a "W" in the time/date and person name intersection).

Also, if there's any way I can simplify my request for help, tell me.

[s3a]

I would really appreciate it if someone could at least point out the part(s) of my macro that is/are flawed so that I can fix them myself.

Here is my latest macro.:

Please Login or Register  to view this content.

[jaslake]

Hi s3a

Took a quick look at your workbooks and ran the code. Gotta be honest, can't tell for certain what you're after.

Do this...mock up a this.xlsm workbook that takes the Data from other.xlsm workbook that demonstrates what you'd like the results to be after running the code. Call the mock workbook "Results"...post it to your Thread.

If I SEE what you want perhaps I can duplicate it.

[s3a]

Thanks for your answer.

I'm attaching what you requested. I also purposely changed the values in other.xlsm to better illustrate what I am trying to get done.

If you need any more information, just ask.

Edit: No names should be transferred on the right of the "Project" column via the macro; that will be done manually without a macro. If more data with a person already on the right of the "Project" column is found, Ws need to be added to that column rather than re-adding that person to the right of "S&P" or "Loto Quebec". The macro needs to not undo what is done manually.

[s3a]

I did some more reading and, I'm now trying to modularize the code to simplify it/make it scalable since it is more complicated than I initially anticipated.

Here is my latest macro.:

Please Login or Register  to view this content.

When I try to run the program, I get the following message.:

Run-time error '424':
Object required

Then, when I click "Debug", I get the following line highlighted.:

Please Login or Register  to view this content.

What am I doing wrong?

[s3a]

I fixed something else in the macro so, here is the latest version.:

Please Login or Register  to view this content.

[jaslake]

Hi s3a

Your project is quite complex (to me). The code in the attached appears to do as you requested in your Results sample file. The code can be cleaned up (variable names could be made Public for example). I've not tried to clean it up, it's presented as written.

I have no idea what the code will do here

If more data with a person already on the right of the "Project" column is found, Ws need to be added to that column rather than re-adding that person to the right of "S&P" or "Loto Quebec". The macro needs to not undo what is done manually.

You're sample file didn't include this type of data.

Run the code from this v2.xlsm ...CTL + x has been assigned as the Keyboard Shortcut.

[s3a]

Hello. Here are the new other.xlsm and Results.xlsm files.

Notice that there was Name5 placed for whatever reason on the right of "Project" before that name was found in other.xlsm therefore instead of going to the right of "S&P", it went (in my pretend-macro run) to the right of "Project".

P.S.
Your code didn't work perfectly on my real Excel files (and my real version doesn't incorporate names on the right of "Project" yet so that's not the problem). It seems that all the names went to the right of "S&P" and to the left of "Loto Quebec" and the first name was just a blank "piece of text". Furthermore, something is really odd with the formatting of the cells in the real "other" file. I noticed that when I scroll down rows 6 and above stay there while the rows 7 and below scroll. Also in that same real "other" file, sometimes I can Ctrl+F data and, other times, I can't (successfully). That is probably a factor in the macro's screw-ups. How do I deal with that? Or, in the worst case, how do I disable that formatting?

[jaslake]

Hi s3a

Post your "Real File(s)" with their original File Structure and Formatting and include the anomaly of a record (or two or three...what ever is "Real") being manually added to the right of "Project". I can't duplicate your issue with the files I'm currently working with.

[s3a]

In words, the "Project" thing is like having a name that is already added. Instead of creating a new column, you just put all the Ws on the column that was made manually prior to the macro's run.

About the "real files", they contain sensitive data so, can I send them to you privately (and, we can then, possibly, put sample files that share that formatting "challenge" in this thread later)?

[jaslake]

Hi s3a

The

formatting "challenge"

is what's lead to the vast majority of the tweaks we've had to make to previously posted code.

I don't need or wish to see your entire "Real" file. Create a subset (without confidential information) that truly represents your "Real" file (same structure, same formatting). Make certain it contains

In words, the "Project" thing is like having a name that is already added. Instead of creating a new column, you just put all the Ws on the column that was made manually prior to the macro's run.

I can't manage this issue without seeing it in order to write Code to deal it.

I wish to see both files (this and other...or whatever you call them..."Real" files..."Real" file Structure, "Real" file formatting)

[s3a]

I had already given you a "Result.xlsm" file with stuff relating to the "Project" column; did you not see it or do you want more information?

I'm attaching an "other.xlsm" file which I think has the "formatting challenge". I'm also attaching files "ActualResults.xlsm" from when I ran the program and "ExpectedResults.xlsm" for what I am trying to achieve.

(These files are in workbooks.zip.)

[jaslake]

Hi s3a

What's the "s" doing in the Date Field (Column A) of REP_TECH_SCHEDULE?

There's a Blank Space at Cell A14 of REP_TECH_SCHEDULE...does this exist in your "Real" file?

[s3a]

I seem to have been more tired than I thought when I made those files, sorry about that.

The latest versions should be correct but, tell me if you think something is wrong so that I can confirm or deny your suspicion(s).

[jaslake]

Hi s3a

How does this happen? When does it happen? What does the Results File LOOK LIKE before ANY code is run assuming this scenario? Show me.

Notice that there was Name5 placed for whatever reason on the right of "Project" before that name was found in other.xlsm therefore instead of going to the right of "S&P", it went (in my pretend-macro run) to the right of "Project".

Don't zip the file...not necessary.

[s3a]

How does this happen?

The macro should see that the name already exists in "this" workbook and instead of adding it to the column stated in the "other" file, it should just keep pilling the data on the column the name is already at.

When does it happen?

I'm not too sure what you mean but, to attempt to answer your question anyway, Name5, in my example(s), is assumed to be right-ward of the "Project" column prior to the macro's run (always).

What does the Results File LOOK LIKE before ANY code is run assuming this scenario? Show me.

Check out PriorToMacroRun.xlsm.

Don't zip the file...not necessary.

I was trying to save space for the site and, I prefer it like that so, I thought you would too. I now have them attached individually.

Also, I am sorry for the very late reply. My computer had problems which I've now resolved.

[jaslake]

Hi s3a

We're not communicating well at all. Look at this picture
Questions.jpg

In your PriorToMacroRun Book:
How does Name5 physically get entered into Cell N1?
How do the W's physically get entered into Cells N16, N17 and N18?

I'm trying to understand THE PROCESS. Only you know it...assume I'm stupid and know nothing (it's true in this instance). Help me out here.

[s3a]

In referrence to your image's comic-book boxes,
Top-left:
It will most likely be added by the macro but, it is also possible that the human being (laboriously) adds it all him or herself. In the example(s) I've sent you, this is intended to be done manually (I cheated by copy-pasting) prior to the macro's run. Basically, if the date has not already been made by the macro, the human being has to do it manually and the macro should not be confused by this.

To-right:
This is manually entered by a human being before the macro's run or it was created by the macro and then a human being moved it to the right of the "Projects" column. In the case of my example(s), it is expect that this be added manually before the macro's run.

Bottom-right:
These "W"s are assumed to be manually entered by a human being or made by the macro and then moved by a human being to the right of the "Projects" column. In the case of my example(s), it is expected that this be added manually before the macro's run.

I hope this time I made sense but, if I didn't, I don't mind re-phrasing it again.

[jaslake]

Hi s3a

The picture is getting a bit clearer.

Regarding the Date, Day and Time

it is also possible that the human being (laboriously) adds it all him or herself...

and this

if the date has not already been made by the macro, the human being has to do it manually and the macro should not be confused by this.

The Code checks to see if the Date already exisis...if it doesn't find it creates it 37 times. On the other hand, if it finds it EVEN ONCE, nothing is done...it leaves it alone. I guess I'd wonder why the User is even messing around in the Date, Day, Time Columns. I'd consider locking the Cells and protecting the Worksheet. You're allowing the User to make mistakes. Your issue not mine.

Regarding manually added Names to the right of Project Column. I'll revise the Code to check the existence of each Name in Any Column to the right of Project Column. If the Code finds it, nothing will be done with the Name. If the Code doesn't find the Name it'll be added in the slot in which it belongs based on the value in Column E of other.

Regarding manually adding W's to the Body of Results, I have to wonder why. If the Name has been previously manually entered the Code will find it and enter the W's. Again, I'd consider locking the Cells and protecting the Worksheet. You're allowing the User to make mistakes. Your issue not mine.

I'll look at this today...get back to you.

[jaslake]

Hi s3a

Attached are two new files this v2.1 and other. Open both files. You'll notice "the User" has entered Name5 in Cel N1 of this v2.1

Click the Buttons (in sequence) on Sheet1 of this v2.1

Let me know of issues.

[s3a]

Hello, jaslake.

When I use my "this" and "other" workbooks that were made for testing, all seems well however, when I use my "real" "other" workbook, there are issues as of button "2". When I click on "1", all seems well and then, when I click on "2", the first column after "S&P" is green but has no text in it (in other words, it's empty). I noticed that I cannot Ctrl+F anything other than "S&P", "Name5" and "Project". At a glance, apart from needing to shift everything after the empty cell horizontally by 1 cell to the left and not being able to Ctrl+F anything, button "2"'s task seems well done. To test button "3", I shifted the columns as mentioned above and then pressed "3", not even one W was added.

To make things worse, I can't think of a way to show this to you perfectly without giving real people's names since, if I modify the cells, the formatting problem of the Ctrl+F is no longer present (as in Ctrl+F works again).

However, I am still attaching a file that I believe will be useful which contains the “Loto Quebec” cell “badly formatted” (such that the Ctrl+F does not work). I want to emphasize that “S&P”, “Project”, “Name5” and the dates are all possible to Ctrl+F but “Loto Quebec” is not. I believe the ones that are Ctrl+F-able are Ctrl+F-able because those cells were ignored and not handled by the macro.

[jaslake]

Hi s3a

What's the purpose of Ctrl+F (Find and Replace) in this scenario?

That aside, I changed two lines of code in the attached that appears to have resolved this issue

pressed "3", not even one W was added

[s3a]

Hi, again.

What's the purpose of Ctrl+F (Find and Replace) in this scenario?

Nothing other than the obvious. That is, Ctrl+F should work so that the user can find a name (for example).

That aside, I changed two lines of code in the attached that appears to have resolved this issue.

Button "1" works perfectly. Button "2" works almost perfectly (where the only flaw is that most cells are not Ctrl+F-able). Button "3" now has a run-time error when run with my real "other" workbook (which does not occur with the test "other" workbook).:

Run-time error '91':
Object variable or With block variable not set

The following is highlighted:

Intersect(myStartRow.EntireRow, myCol.EntireColumn) = "W"

in Find_Time_Worked after clicking "Debug".

[jaslake]

Hi s3a

Alrighty then...I guess it's time to bite the bullet. If you wish to send me your "real" other file, I'll PM my email address.

[jaslake]

The Web Site you provided is Blocked by my Anti-Mal-ware Software. Try Emailing the File.

If that doesn't work...well...