Hello
Being new and unfamiliar with the possibilities of VB in Excel, I’m hoping someone could help me with a solution to some code I’ve been trying to write.
Basically there are three tables:
First table is an input table that will extend down for several hundred rows eventually but I’ve limited to just a couple for the example,
A reference table
An output table – this used to be an HLOOKUP table but this has become too cumbersome and I’ve run out of arguments in the formula, hence resorting to code instead.
Basically starting with each row of data in the input table, need to reference the value of each successive column to the corresponding date row in the reference table. Then put the resultant date in the output table.
For example refer Input table cell E2 (7,000), refers to the reference table will return this value occurs in Aug 19 which is then returned in the output table.
I need the code to loop through multiple rows of the input table and returning the corresponding date value in the output table.
Many thanks.
Input table 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10th
Row1 1000 3000 5000 7000 9000 11000 13000 15000 17000 19000
Row2 5000 10000 15000 20000 25000 30000 35000 40000 45000 50000
Reference table Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20
Row 1 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 11000 12000 13000 14000 15000
Output table 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10th
Row1 Feb-19 Apr-19 Jun-19 Aug-19 Oct-19 Dec-19 Feb-20 Apr-20 Jun-20 Aug-20
Row2 Jun-19 Nov-19 Apr-20 Sep-20
Being new and unfamiliar with the possibilities of VB in Excel, I’m hoping someone could help me with a solution to some code I’ve been trying to write.
Basically there are three tables:
First table is an input table that will extend down for several hundred rows eventually but I’ve limited to just a couple for the example,
A reference table
An output table – this used to be an HLOOKUP table but this has become too cumbersome and I’ve run out of arguments in the formula, hence resorting to code instead.
Basically starting with each row of data in the input table, need to reference the value of each successive column to the corresponding date row in the reference table. Then put the resultant date in the output table.
For example refer Input table cell E2 (7,000), refers to the reference table will return this value occurs in Aug 19 which is then returned in the output table.
I need the code to loop through multiple rows of the input table and returning the corresponding date value in the output table.
Many thanks.
Input table 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10th
Row1 1000 3000 5000 7000 9000 11000 13000 15000 17000 19000
Row2 5000 10000 15000 20000 25000 30000 35000 40000 45000 50000
Reference table Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20
Row 1 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 11000 12000 13000 14000 15000
Output table 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10th
Row1 Feb-19 Apr-19 Jun-19 Aug-19 Oct-19 Dec-19 Feb-20 Apr-20 Jun-20 Aug-20
Row2 Jun-19 Nov-19 Apr-20 Sep-20