VBA: to loop and copy/paste based on 2 criteria (similar to a Hlookup)

swimmer27

New Member
Joined
Jul 10, 2018
Messages
1
I have a data set that has 200 rows with (let's say) 40 entries per row and each entry is divided by year (40 years worth of values). The entries are numbered 1 - 200.

I would like to create a new dataset by entry number and make each yearly entry monthly depending on whatever year it corresponds to back in the original data set (the value for the year is kept consistent throughout the 12 months of that year) on a separate part of the same sheet

I currently have this in excel as an HLookup/match formula referencing the entry number and year but want to automate the process in VBA.

An idea I have is to loop 200 times through and find the entry number and year of the date and to match it with the original table's entry, then copy and paste into a new table.

I'm new to VBA and any help is greatly appreciated. Thanks!!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,213,533
Messages
6,114,179
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