VBA, index to search in a range of numerous data then copy and paste to other sheet

[dinokovac93]

Hi guys,

I'm working on a extremely tedious project and my hands are starting to fall off . I'm not too sure if this VBA is possible but if anyone has suggestions please feel free to post it.

To demonstrate what I am doing manually check out this GIF:

Typically I could make a macro to fill in all these cells but the issue is that the information in sheet 5 is always in different cells. Which is why I believe a index would be useful, but then again the different cells would mess up the index.

I'm no expert but I think I need a VBA macro that loops through each range of data from sheet 5. In the loops it will search using the column names and row dates of sheet 6. While it loops and searches for the data in sheet 5, it will copy and paste the results into sheet 6 to fill out the information.

(In the GIF I only did a few columns to give you an idea of what I'm doing but here are a lot more.)

This is what I imagine to be the appropriate solution but I do not know how to code this up, any help will be extremely helpful.

The macro needs to:
1. Loop through the ranges of data in sheet 5

2. It will copy and paste each of the dates in column A from sheet 5 and paste it into sheet 6.

3. Sheet 6 now has the dates and column names where it can match with sheet 5.

4. It will find the data from sheet 5 according the the date and column names on sheet 6.

5. After matching the appropriate data with the dates and column names it will copy it and paste it into sheet 6.

Note:
1. Dates on sheet 5 do not have headers stating that its a date.
2. There are more columns in sheet 5 then in sheet 6. Only the columns in sheet 6 are needed.
3. Subject ID and Name are not needed in the macro but needs to be in the sheet.

If you have any questions please ask. If anyone has any suggestions please let me know, it's really appreciated.


Here are the column names





[TABLE="width: 4299"]
<tbody>[TR]
[TD]Subject ID[/TD]
[TD]name[/TD]
[TD]visit_date[/TD]
[TD]intraocular pressure - right[/TD]
[TD]intraocular pressure - left[/TD]
[TD]VA - Right Distance SC[/TD]
[TD]VA - Right Distance SC +-[/TD]
[TD]VA - Right Distance CC[/TD]
[TD]VA - Right Distance CC +-[/TD]
[TD]VA - Right Distance PH SC[/TD]
[TD]VA - Right Distance PH CC[/TD]
[TD]VA - Left Distance SC[/TD]
[TD]VA - Left Distance SC +-[/TD]
[TD]VA - Left Distance CC[/TD]
[TD]VA - Left Distance CC +-[/TD]
[TD]VA - Left Distance PH SC[/TD]
[TD]VA - Left Distance PH CC[/TD]
[TD]VA Type[/TD]
[TD]Wearing Rx - Right Sphere[/TD]
[TD]Wearing Rx - Right Cylinder[/TD]
[TD]Wearing Rx - Right Axis[/TD]
[TD]Wearing Rx - Right Add[/TD]
[TD]Wearing Rx - Left Sphere[/TD]
[TD]Wearing Rx - Left Cylinder[/TD]
[TD]Wearing Rx - Left Axis[/TD]
[TD]Wearing Rx - Left Add[/TD]
[TD]Wearing Rx - Current Age[/TD]
[TD]Manifest Refraction - Right Sphere[/TD]
[TD]Manifest Refraction - Right Cylinder[/TD]
[TD]Manifest Refraction - Right Axis[/TD]
[TD]Manifest Refraction - Right Add[/TD]
[TD]Manifest Refraction - Right Dist VA[/TD]
[TD]Manifest Refraction - Right Near VA[/TD]
[TD]Manifest Refraction - Left Sphere[/TD]
[TD]Manifest Refraction - Left Cylinder[/TD]
[TD]Manifest Refraction - Left Axis[/TD]
[TD]Manifest Refraction - Left Add[/TD]
[TD]Manifest Refraction - Left Dist VA[/TD]
[TD]Refraction Type[/TD]
[TD]Pachymetry - Right Eye[/TD]
[TD]Pachymetry - Left Eye[/TD]
[TD]external exam - right eye[/TD]
[TD]external exam - left eye[/TD]
[TD]Slit Lamp Exam - Right Lids[/TD]
[TD]Slit Lamp Exam - Right Conjunctiva[/TD]
[TD]Slit Lamp Exam - Right Cornea[/TD]
[TD]Slit Lamp Exam - Right AC[/TD]
[TD]Slit Lamp Exam - Right Lens[/TD]
[TD]Slit Lamp Exam - Right Iris[/TD]
[TD]Slit Lamp Exam - Right Vitreous[/TD]
[TD]Slit Lamp Exam - Left Lids[/TD]
[TD]Slit Lamp Exam - Left Conjunctiva[/TD]
[TD]Slit Lamp Exam - Left Cornea[/TD]
[TD]Slit Lamp Exam - Left AC[/TD]
[TD]Slit Lamp Exam - Left Lens[/TD]
[TD]Slit Lamp Exam - Left Iris[/TD]
[TD]Slit Lamp Exam - Left Vitreous[/TD]
[TD]Fundus Exam - Right Disc[/TD]
[TD]Fundus Exam - Right Macula[/TD]
[TD]Fundus Exam - Right Periphery[/TD]
[TD]Fundus Exam - Right Vessels[/TD]
[TD]Fundus Exam - Left Disc[/TD]
[TD]Fundus Exam - Left Macula[/TD]
[TD]Fundus Exam - Left Periphery[/TD]
[TD]Fundus Exam - Left Vessels[/TD]
[TD]Fundus Exam - Right Cup/Disc Ratio[/TD]
[TD]Fundus Exam - Left Cup/Disc Ratio[/TD]
[/TR]
</tbody>[/TABLE]

 

[dinokovac93]

I posted this twice by accident.

Please go to this link. Sorry.

https://www.mrexcel.com/forum/excel-questions/1026769-vba-search-range-data-then-copy-paste.html