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

dinokovac93

New Member
Joined
Oct 10, 2017
Messages
11
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:

eJn1DTj1VN.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





Subject IDnamevisit_dateintraocular pressure - rightintraocular pressure - leftVA - Right Distance SCVA - Right Distance SC +-VA - Right Distance CCVA - Right Distance CC +-VA - Right Distance PH SCVA - Right Distance PH CCVA - Left Distance SCVA - Left Distance SC +-VA - Left Distance CCVA - Left Distance CC +-VA - Left Distance PH SCVA - Left Distance PH CCVA TypeWearing Rx - Right SphereWearing Rx - Right CylinderWearing Rx - Right AxisWearing Rx - Right AddWearing Rx - Left SphereWearing Rx - Left CylinderWearing Rx - Left AxisWearing Rx - Left AddWearing Rx - Current AgeManifest Refraction - Right SphereManifest Refraction - Right CylinderManifest Refraction - Right AxisManifest Refraction - Right AddManifest Refraction - Right Dist VAManifest Refraction - Right Near VAManifest Refraction - Left SphereManifest Refraction - Left CylinderManifest Refraction - Left AxisManifest Refraction - Left AddManifest Refraction - Left Dist VARefraction TypePachymetry - Right EyePachymetry - Left Eyeexternal exam - right eyeexternal exam - left eyeSlit Lamp Exam - Right LidsSlit Lamp Exam - Right ConjunctivaSlit Lamp Exam - Right CorneaSlit Lamp Exam - Right ACSlit Lamp Exam - Right LensSlit Lamp Exam - Right IrisSlit Lamp Exam - Right VitreousSlit Lamp Exam - Left LidsSlit Lamp Exam - Left ConjunctivaSlit Lamp Exam - Left CorneaSlit Lamp Exam - Left ACSlit Lamp Exam - Left LensSlit Lamp Exam - Left IrisSlit Lamp Exam - Left VitreousFundus Exam - Right DiscFundus Exam - Right MaculaFundus Exam - Right PeripheryFundus Exam - Right VesselsFundus Exam - Left DiscFundus Exam - Left MaculaFundus Exam - Left PeripheryFundus Exam - Left VesselsFundus Exam - Right Cup/Disc RatioFundus Exam - Left Cup/Disc Ratio

<tbody>
</tbody>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top