Need to adapt some code to a new data set

[MermaidNiki]

Hi everyone,
The monkeys need your help again. I have three files shared here and I will break them down for you.
1. OldMacrosFile contains the code that currently works on my Mac for a different problem. This is a similar scenario but not exactly.
2. FertileFemaleCount contains the data that I need moved over. Only column AJ. Each sheet is for a different monkey group.
3. FertileTest is an example of the sort of file the code will populate information into.

While many of the files that I will use this on are "like" FertileTest, there are some key differences, not in the headers, but in the columns. However, Column J will always be the populated column. J needs to be populated with data from FertileFemaleCount column AJ, based on how the FertileTest columns C (Date) and E (Group) match with FertileFemaleCount A (Date) and each respective sheet (these are based on the same group names of R1, R2, and PB).

Does anyone think they can grab the code from the OldMacrosFile and adapt it accordingly? The only tricky thing about is that for the OldMacrosFile it needed to pull the information based on a day before the date listed. For this one, the actual date is what I need.

Thanks in advance - i hope having the code already helps.

[dflak]

I'm confused. What do you want moved from which file to where in the other file? Column AJ is different things on tabs R1, R2 and PB in the FertileFemaleCount file. Where does this information wind up?

The FertileFemaleCount has one record per day. the Fertile Test file has multiple records per day.

Maybe you can trace several pieces of data from Count to Test as an example.

[MermaidNiki]

I'm so sorry - I missed that entirely. Give me just a bit tonight to make this better with more information!

[MermaidNiki]

Ok! I think I figured it out. In PB and in R2 AJ is the correct column (# females in fertile phase), this is column AS for the R1 tab.
I uploaded a new file that has the first 129 rows filled in. Each of the Excel files that this code will let data flow into will always use J as the landing column.
But the files themselves have varying numbers of rows based on what how R subsetted it for me. So some have 67,000 rows and others only have 35,000.
In all of them, multiple rows will have the same date and the same group.

If it helps, the rows on FertileTest are lines of behavior attached to a particular monkey in that group during a particular focal. So there
will be multiple rows with seemingly similar information, but there are slight differences throughout the rest of the columns. (Which on the real
files extend to like.. AZ.. lol)

I hope this helps, please let me know if you need more and Thank You!
PS - I changed up the code you wrote last time to work for a Mac, all the "/" are replaced with Application.Pathseparator

[dflak]

I notice that the name on the tab on this workbook has a .CSV extension. Does this mean that you opened the CSV file and saved it as an XLSX file?

[dflak]

This should work in both Macs and PCs. Fill in the path name to your source files in Cell B1 . Fill in the filenames in cells B2 and B3.

I found it easier to build from scratch than to reverse engineer the code.

I proceeded with the assumption that the matrix you want filled in is actually a CSV file. If it isn't just change the name in cell B3. It will work with either a CSV file or Excel file as long as the information you want is on the first sheet.

The program works by copying the file in Cell B3 into the table in the Sheet called Fertile. It then opens the count file and gets the last row in each tab and last column in each tab. The last column is assumed to have the count you want.

The program then goes down the list of dates in the table, picks the appropriate sheet and ranges in the source file and looks up the data. It records the number found if the value in column K is "F" or "f" - we don't want to record data for males. Whatever number comes across in column J will remain there. Also Column J is not changed if the date cannot be found in the source table.

I hope this is what you want.

For the purposes of testing I blanked out all the data in column J in the source file. I was able to confirm that rows with males or rows without a matching date were not affected,

[MermaidNiki]

Thank you! Ok some info:
1. The files were all initially saved as .xlsx and converted into CSV for R. So this code will be run on both CSV and Excel and it sounds like that should be ok - phew!
2. Can you change J to show up on every row, whether male or female? It seems counter intuitive, but it is more important on the male rows because it serves as a control for their behavior. And we want it on the female rows, just in case it adjusts their behavior too.
3. You mentioned "missing dates". But hopefully, there should not be any. Every date in the FertileTest file should be represented in the FertileFemale file. Fingers crossed this won't be a problem, but when I test it the first time, I will make sure sort the column and verify. Sometimes my stupid Excel files retain weird date formats and will mess stuff up.

Again - thank you for hopping on to help me with such a complex problem. I really appreciate it.

[dflak]

As for the males: when data is imported into the table it comes in with column J information filled in already. I only update information for females. Existing information for males remains unchanged.

I only blanked out the data in the test data I was importing to make it easier to see that data for the males is unchanged. When you import real data you will have information there.

[MermaidNiki]

Oh! Got it! I will test it out tonight when I get off work and will update you asap.

[MermaidNiki]

Sorry I didn't get back to you last night, I'll pm you the reason. I am messing with it now and I get a runtime error 76, path not found. But I checked the paths and the file names repeatedly. Not sure what to do.

[dflak]

You should not be getting this error message. I check for the existence of the paths to both files: if the path name does not exist, then you should get a dialog box telling you the path name and that it doesn't exist.

Show me which line of code has the error and it may help me narrow down what might be causing it.

[MermaidNiki]

Thank you! Here is a screen shot
Screen Shot 2018-07-02 at 8.51.53 AM.png

[dflak]

Between the Function line and the If Dir() line add the following piece of code temporarily:

Please Login or Register  to view this content.

This bit of code should produce a dialog box with the file name being tested. It should have the colons in it instead of slashes.

If it still has slashes, go to the top of the module and change the code indicated - apparently Application.PathSeparator isn't working and we'll have to hard code it.

Please Login or Register  to view this content.

You can remove the MsgBox after testing.

[MermaidNiki]

OK - here is the updated report. MsgBox showed slashes, I updated the code you suggested and tested again. I still get the same error in the same place and it still gives me slashes and not colons.

[dflak]

Nothing is attached.

[MermaidNiki]

Oh, I'm sorry, I didn't think I needed to attach anything. One second please, I'll screen shot the message box. Oops - I guess it gives ONE colon at the end that my tired eyes missed. Note to self that screen shots always help.
After this, it pops up with the same fie not found run time error, highlighting the same row of code.

[dflak]

The entries that you need to make in Cells B1, B2 and B3 must also have colons in them.

[MermaidNiki]

OH! OK - so they now look like this. (When I added : in front of B2 and B3, it added a double colon to the file name message box, so I removed those.)
I get a new error as well, but the same line of code highlighted. Screen shots attached.

[dflak]

I'm not familiar with the Mac directory structure so I did some research.

I just pulled up information on the Mac File System. According to the article you can also use a forward slash. Set PS = "/" and define the paths and file names using forward slashes. I notice your names don't have spaces so you should not have issues with that.

[jeffreybrown]

@MermaidNiki,

I removed all attached images with offensive language and removed extraneous quotes.

It's possible more of the images were removed than necessary as I could not distinguish which one was the one with the offensive language.

Please be careful what you post in the future!

[MermaidNiki]

Sorry Mod... It's how a frustrated phD labels her file folders. Can't really be helped.

[MermaidNiki]

I think I need to just use the Windows machines here on campus then, because nothing is working. And I went and looked at the old code to see if it could be adapted, but none of those fixes work either. I still get the device not found error. This is so frustrating because I know your code can work on a mac since the last one did with just Application.PathSeparator

[jeffreybrown]

Sorry Mod... It's how a frustrated phD labels her file folders. Can't really be helped.

Frustrated or not, doesn't mean you should post that filth, so it can be helped.

[MermaidNiki]

I'm not familiar with the Mac directory structure so I did some research.

I just pulled up information on the Mac File System. According to the article you can also use a forward slash. Set PS = "/" and define the paths and file names using forward slashes. I notice your names don't have spaces so you should not have issues with that.

Hey friend! I messed around with some code and rewrote some sections to get past the file name problem, only to run into issues with the Table Range code. Ugh. So, I ran to campus to use a Windows machine. This one gives me the following errors.

WindowsError1a.png
WindowsError1b.png

[Marc L]

Hi !

The #13 is an obviously error as per its message : the data expected does not match !
So just compare the type of the variable and the type of the data …

[MermaidNiki]

Hi !

The #13 is an obviously error as per its message : the data expected does not match !
So just compare the type of the variable and the type of the data …

Thank you, but the data DOES match. It worked on the test files I provided initially. This full file has all the exact same information, same headers, same columns, just 67K rows instead of only a few hundred. I did double check, but Date is column C. So I am a bit lost.

[MermaidNiki]

OH WAIT. I may have figured this out. Stay tuned.

[MermaidNiki]

OH WAIT. I may have figured this out. Stay tuned.

Nope

[MermaidNiki]

Oh for Heaven's sake.... I see what it is...

dflakk - I thought that the code would pull all the columns over into the Fertile sheet, not lock them into certain table headers. Not your fault at all, this has been a ridiculous and complex problem. And, I can work with this regardless, I just switched over the columns. Easy.

So now the code runs with no errors, but column J, Fertile is totally blank =(

Could it be this? Because I don't care if the monkey is male or female, the number needs to be put into the cell regardless.

Please Login or Register  to view this content.

YEP - I now have all cells filled in correctly except for the ones with males! If we can fix this, I will just run this macros on all my files on a Windows machine.

[dflak]

You hit in on the head. I am excluding the males. Remove the If / End If and the number will also be recorded for the males.

[MermaidNiki]

You hit in on the head. I am excluding the males. Remove the If / End If and the number will also be recorded for the males.

Please Login or Register  to view this content.

I deleted this section of it and now nothing fills in - what am I doing wrong?

Please Login or Register  to view this content.

[dflak]

This is what the code should look like in this section (note the commented out section) - you took out too much.

Please Login or Register  to view this content.

[MermaidNiki]

This is what the code should look like in this section (note the commented out section) - you took out too much.

Please Login or Register  to view this content.

I am now going to see if I can run this on the other 30 files and when they are successful, will mark this as resolved. Thank you!