I am trying to code a macro that will take an ID number that is somewhere in the file name and then find the matching subject number in Column A of a master sheet, where it will then offset and add to that row. There are two files, one called InterviewData10**.xlsx and SCIDdata10**.xslx with the asteriks representing two numbers. I had success with InterviewData using this code:
I tried this with the SCIDdata, however it is a lot more conditional than just copying and pasting. It has 1's that when in certain cells need to have a certain word put into the master file. This is the code I am trying to use:
When running the code is stopped when I do SubjectID.Offset.
In my efforts to debug, I have found the following:
The file is certainly opening, and data can be copied and pasted.
The mid function is grabbing the correct subject ID number from the name of the file.
It is not "nothing" because if I do simple functions (that do not involve SubjectID) after the first if statement, there is no error and the code will run properly.
When doing debug.Print, for some reason there is nothing in the immediate window. It does not recognize the SubjectID as a cell value, whereas when I do debug.Print on the SubID from InterviewData, it comes up with the ID number for the chosen file. Because of this, it is telling me that I am missing an object (error 91).
I am aware of the problem, but I have no idea how to fix it.
Bookmarks